February 28, 2008, 11:27 AM
BethanyFML Heirarchy
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
Server Environment: Win2K3 Server WebFOCUS 7.13 Apache Tomcat standalone application server
February 28, 2008, 12:15 PM
Darin LeeWhat do you mean by "unbalanced"?
Regards,
Darin
In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
February 28, 2008, 12:38 PM
BethanyIn 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
Server Environment: Win2K3 Server WebFOCUS 7.13 Apache Tomcat standalone application server
February 28, 2008, 01:39 PM
Darin LeeWe 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?"
Regards,
Darin
In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
February 28, 2008, 04:44 PM
FrankDutchBethany
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.
|
Frank |
| prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7 |
February 29, 2008, 11:01 AM
BethanyDarin,
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
Server Environment: Win2K3 Server WebFOCUS 7.13 Apache Tomcat standalone application server
March 05, 2008, 09:55 AM
JimRiceHi 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
WF DevStu 5.2.6/WF Srv 5.2.4/Win NT 5.2
March 05, 2008, 02:32 PM
Darin LeeSorry 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.
Regards,
Darin
In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
March 06, 2008, 01:18 PM
BethanyDarin, 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
Server Environment: Win2K3 Server WebFOCUS 7.13 Apache Tomcat standalone application server
March 10, 2008, 11:52 AM
Darin LeeFML 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.
Regards,
Darin
In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat