Does anyone use FML that references an unbalanced account hierarchy? The sample hierarchy table, CENTGL, is balanced.

Bethany
Server Environment:
Win2K3 Server
WebFOCUS 7.13
Apache Tomcat standalone application server

What do you mean by "unbalanced"?

In the CENTGL sample, all of the accounts roll up to a level 2. But our Lawson Financials hierarchy has some accounts that roll up to a level 6 and others that roll up to level 2.

Balanced Hierarchy

 top       level1   level2   account
A1001      D1001   E1001    1234
                            1235
                   E1002    1224
                            1235
           D1002   E2001    2334
                            2335
B1001      D1003   E3001    3455
                            1243 
etc



Unbalanced


top       level1   level2   account
A1001      D1001   E1001    1234
                            1235
           D1002   1224
                   1235
           E2001   2334
                   2335
B1001      3455
           1243 
etc

 

Bethany

We use Walker Financials instead of Lawson, but our account structure is unbalanced as you describe. Some "bottom level" accounts exist at level2, some at level 3, etc. Our FML reports that reference the hierarchy are pretty complex (and in fact, we are dumping Walker - for various reasons) and in the process are rebuilding our account structure.

Is there some specific problem your are having or was the question just a general "Can it be done?"

Bethany

as posted before in privat.

We use CODA and do the same as Darin, reload the hierarchies into an focus database like the CENTGL.

We have created one fex that does the whole reporting.
We have created 4 or 5 hierarchies since several business units have different ways to look at the financial reporting.
It's now possible for the Financial Department to select an hierarchy and to decide if they need a compressed report on the highest level in PDF or a report to the lowest level in Excel or HTML and all kind of options in between.
From each field in the report you can drill down to more detailed reports till the original bookings/journal lines.

If somebody needs a complete new structure, the only thing he needs to do is create the hierarchy in the CODA application and ready, go.

The unbalanced hierarchy can however be used for a FML report.

Darin,

If I remember correctly, we had Walker before converting to Lawson in 2002. I'm just getting started with FML and want to make sure that FML will work with our account structure to create the report output we want. This is an example of how some of our report output should look and what levels the detail are at. Would you be able to send me some sample syntax on how this would work? Also, do you use the Report Painter to create your output or do you just write the code yourself?

 
LEVEL 3: OPERATING REVENUE
             LEVEL 4: Tuition and Fees
                      Gifts
                      Other Income
         OPERATING REVENUE SUBTOTAL

LEVEL 3: OPERATING EXPENSES
             LEVEL 4: SALARY EXPENSE
                       LEVEL 5: Staff Salaries
                                Student Salaries
             SALARY EXPENSE SUBTOTAL
                       LEVEL 5: Fringe Benefits
             LEVEL 4: NON-SALARY DIRECT EXPENSES
                       LEVEL 6: Supplies
                               Travel
                               Entertainment
                               Misc. Expense
             NON-SALARY DIRECT EXPENSES SUBTOTAL
         OPERATING EXPENSE SUBTOTAL
         GRAND TOTAL 

Hi Bethany,

Have you made any progress with this. I am also interested in creating financial reports from our Lawson system using FML.

Are there discussions going on about this that do not show up here in the public forum?

What resources are there for learning more about the FML feature?

Please advise.

Thanks
Jim

Sorry I didn't get back to you sooner - I forgot about it after a couple of days until Jim's post put it back at the top.

Sample syntax is a hard thing to do for FML because it is so flexible that what I produce in sample may be of zero use to you in your hierarchy structure. Here's a snippet from one of my reports:

FOR ACCNTS_ID
' 99060' GET CHILDREN 1 ADD AS CAPTION LABEL R1 OVER
' 99060' ADD ALL AS CAPTION LABEL R3 OVER
' 99040' GET CHILDREN 1 ADD AS CAPTION LABEL R5 OVER
' 99040' ADD ALL AS CAPTION LABEL R7 OVER
' 99030' ADD ALL AS CAPTION LABEL R9 OVER
BAR AS '='

The GET CHILDREN, ADD ALL, ADD 1, etc. statements are what is going to control the level of rollup that is going to be produced and as you can see, some accounts may have multiple levels of detail and others may be al summed up in one level.

I did use the GUI tools to produce these until I was familiar with the statements and syntax. Then I dropped into the code where I can work fastest. Using the GUI is an easy way to visually determine how the rollups are going to appear.

I believe there is an exercise somewhere in the docs that was very helpful to me in learning which statements do what. I'll post a reference if I can find it.

My actual code is rife with DATA POST and DATA PICKUP and Dialogue Manager, so it's pretty complicated stuff. Overall, I'm very confident that you can use FML to create the report you need with the account structure you have from Lawson. It's just that darn learning curve...

P.S I also have to admit that I was an IBIer doing some consultant work for my (now current) employer and they hired me for a few days to get them going on this stuff.

Darin, Thanks for in the info and sample code.

I've got our account hierarchy set up in the same format as the CENTGL sample table and I'm starting to work on contructing a report.

Here's another scenario that I hope someone with FML experience can help with.

Our structure works with account units and accounts. There are some account units that are tagged as being auxillary. On our Statement of Activities which summarizes data based on the account hierarchy, we want to separate out the account units that are tagged as auxillary and put them into a separate account summary line on the Statement of Activites even though they have the same accounts as other account units.

For example, if the data has:

account unit   account   tag  amount
100000         1000             100.00
100100         1000      AUX    200.00
100000         2000              50.00
100000         3000              50.00
100100         2000      AUX     50.00
  

The report should look like:

 
Account     Amount
1000        100.00
2000         50.00
3000         50.00
AUX         250.00
 

All suggestions are appreciated....
Bethany

FML only allows for sorting/summarizing/etc. on a single field, but you are trying to sort and summarize based on both account and account unit. You could concatentate the two into one and then do what you need. One of your best options, which I referred to earlier is the data POST and DATA PICKUP where you would calculate certain data values and/or records (based on your specific account units criteria), save them using POST and then use PICKUP to pick them up again later for inclusion in your final report. This is the method I frequently use for situations like yours. The more you learn, the more there is to learn. I would definitely read up on this functionality.

There also may be some way using DEFINE fields and regular BY for sorting, or using NOPRINTs, RECAPs.