I wish to create a Financial Report with several levels of summation and with detail level financial data. The data must be sorted and summed in a specific order, that will come from a chart of accounts.

I have looked into using FML, but to my understanding, I would have to specifically code every line and subtotal on the report. I would rather use a database that contained the parent/child/grandchild/etc relationships and have FOCUS dynamically produce the report.

I have looked into using FML Heirarchy, but have not been able to figure out how to produce output that would print various sort/summary labels, followed by detail data, with a summary amount line. The examples I have seen from the WebFOCUS manuals, produce Heirarchy reports where the sort/summary label and amount is printed prior to the detail lines. This is not a normal way of producing a report.

Ideally, I would like to code a report using standard WebFOCUS logic like:

TABLE FILE FINANCIAL
SUM DETAIL
BY LEVEL1 SUBTOTAL
BY LEVEL2 SUBTOTAL
BY LEVEL3 SUBTOTAL
END

But the Levels in this report, would be specified in a chart of accounts table, that defines the mapping between the levels.

Does anyone have any ideal how to approach this problem?

Hi John

Couldnt you just store the order in a table and then look up the order to use and stick the values in ampers? So pseudo code would be something like...


-*ASSUME YOU HAVE A TABLE THAT LOOKS LIKE THIS

-*LEVEL LEVELORDER

-*LEVELX 1
-*LEVELY 2
-*LEVELZ 3

-*MAKE SURE LEVEL IS ALPHA FORMAT. MIGHT NEED TO CONVERT IT FIRST.
TABLE FILE XXX
PRINT LEVEL
BY LEVELORDER NORPINT
ON TABLE HOLD AS YYY
END
-SET &NUMRECS = &RECORDS;

-*NOW READ THE LEVELS INTO AMPER VARIABLES...MAKE SURE ALPHA SPECIFIED IS CORRECT (A10 NOW)
-RUN
-SET &I = 0;
-STARTLOOP
-SET &I = &I + 1;
-READ YYY &LEVEL.&I.A10.
-IF &I LT &NUMRECS GOTO STARTLOOP;

-*NOW USE THE ORDER IN THE OUTPUT
TABLE FILE FINANCIAL
SUM DETAIL
-SET &I = 0;
-STARTLOOP2
-SET &I = &I + 1;
BY &LEVEL.&I SUBTOTAL
-IF &I LT &NUMRECS GOTO STARTLOOP2;
END

Does this help?

Jodye

Hi John-
Without knowing the Chart of Accounts that you are using here is a guess to put detail before sum using hierarchical FML:

FOR ACCOUNT
"Assets" OVER
-* A title
10000 GET CHILD 1 ADD ALL AS CAPTION OVER
-* This will list the next level of accounts
-* below 10000 with the units of account
10000 ADD ALL AS CAPTION
-* this can be the total for everything in 10000
HTH

Jimster06:

Your technique:
FOR ACCOUNT
"Assets" OVER
-* A title
10000 GET CHILD 1 ADD ALL AS CAPTION OVER
-* This will list the next level of accounts
-* below 10000 with the units of account
10000 ADD ALL AS CAPTION
-* this can be the total for everything in 10000
HTH

works okay for a one level report. However I don't think it is possible using this technique to go multiple levels. Even if it is possible, I think it would prove very complex and hard to maintain, having to specifiy all possible combinations of LEVELS in the report fex.

What I want to do is produce a report like this:

LEVEL 1 "Description"
LEVEL 2 "Description"
LEVEL 3a "Description"
Detail 1 Description Amount
Detail 2 Description Amount
LEVEL 3a Subtotal
LEVEL 3b "Description"
Detail 1 Description Amount
Detail 2 Description Amount
LEVEL 3b Subtotal
LEVEL 2 Subtotal
LEVEL 1 Grand Total

This format resembles a standard FOCUS report with various subtotals, except that the order of the Levels is being driven by a Chart of Accounts, which defines the PARENT,CHILD,GRANDCHILD, etc order in a specified manner. By using a Chart of Accounts, I hope to be able to dynamically create a complex financial report, that would be easy to code and maintain.

JohnK, Is the COA balanced or unbalanced hierarchy? IOW, does evert path have the same number of child levels?

Also, do you have to perform a recursive join to get at all the levels, or is your COA hierarchy in a flat table (maybe post the master for the COA)?

Jimster06:

Your technique:
FOR ACCOUNT
"Assets" OVER
-* A title
10000 GET CHILD 1 ADD ALL AS CAPTION OVER
-* This will list the next level of accounts
-* below 10000 with the units of account
10000 ADD ALL AS CAPTION
-* this can be the total for everything in 10000
HTH

works okay for a one level report. However I don't think it is possible using this technique to go multiple levels. Even if it is possible, I think it would prove very complex and hard to maintain, having to specifiy all possible combinations of LEVELS in the report fex.

What I want to do is produce a report like this:

LEVEL 1 "Description"
LEVEL 2 "Description"
LEVEL 3a "Description"
Detail 1 Description Amount
Detail 2 Description Amount
LEVEL 3a Subtotal
LEVEL 3b "Description"
Detail 1 Description Amount
Detail 2 Description Amount
LEVEL 3b Subtotal
LEVEL 2 Subtotal
LEVEL 1 Grand Total

This format resembles a standard FOCUS report with various subtotals, except that the order of the Levels is being driven by a Chart of Accounts, which defines the PARENT,CHILD,GRANDCHILD, etc order in a specified manner. By using a Chart of Accounts, I hope to be able to dynamically create a complex financial report, that would be easy to code and maintain.

Dhagen:

I am at the design/concept phase of the task of creating a multilevel financial report. For this phase, I am using the demo WebFOCUS database CENTGL, which has the fields GL_ACCOUNT and GL_ACCOUNT_PARENT.

I have gone though the documentation on Creating Financial Reports (Chapter 17 Creating Reports with the WebFOCUS Language), but all the examples provided are too elementary for my report. Using the CENTGL joined to CENTSYSF, I am able to produce a simple One Level Summary with detail report, but I want to go multiple levels. Using the demo CENTGL and CENTSYSF database I would like to produce a report the looks like this

Total Operating Expenses
Selling Expenses
Advertising
TV/RADIO 99999.99
Print Media 99999.99
Internet Advertising 99999.99
--------------
Total Advertising 99999.99

Promotional Expenses
DATA DESCRIPTION 99999.99
DATA DESCRIPTION 99999.99
--------------
Total Promotional Expenses 99999.99

---------------
Total Selling Expenses 999999.99

Administration
IT Contractors 999999.99
Social Security 999999.99
-----------------
Total Administration 9999999.99


Total Operating Expenses 99999999.99


The format of this report, with various Subtotals and detail lines, follows the pattern of regular FOCUS reports. What differs is that the order, and also the various levels of subtotaling, will be controled by a chart of accounts database, like CENTGL.
I have read though the WebFOCUS documention on using FML and Reporting Dynamically form a Heirarchy, but the examples do not reach the desired level of sophistication. I would like to produce a report with "N" number of levels. I have searched though the IBI website, and can't find an example of a Financial Report that has more than one level of detail/summation.

JohnK,

Sorry for the lateness of this reply. I have done this type of report in the past. The report was against an unbalanced hierarchy, and was built to display the details of the lowest point of the hierarchy, and show subtotals at all higher points. The problem is that I can't seem to locate any of the examples. Please be patient, and I'll see what I can find early this week.

Regards