WebFOCUS Newsletter February 2005 - Data Consolidation in FML

If a reporting database can be defined as an FML Hierarchy, i.e., displayed as a chart of accounts in the FML Painter, you have many options in displaying children or consolidated data in your report.

For this article we will use a consolidated financial database from the Century Corp. demo: CENTSTMT.

First, we will create our basic underlying report in Developer Studio.

GL_ACCOUNT is the Ledger Account ACTUAL_AMT is the Actual ACTUAL_YTD is the YTD Actual

Let's make GL_ACCOUNT our FOR field and click on the Matrix tab at the bottom of the Report Painter to access the FML Painter.

Notice that because CENTSTMT is an FML Hierarchy, a chart of accounts appears on the right panel of the FML Painter. Let's expand Account 1000 (Profit Before Tax) to see its sub-accounts.

Account 1000 actually has four levels of sub-accounts or parent-child accounts (Screen 1). This will give us plenty of examples to explore data consolidation.

Screen 1

Note: Given this is 2005, I would imagine that the description for Account 2230, Video Player, would now say DVD Player.

In FML, a FOR field value is inserted into the FML Painter as a tag row type. Let's collapse the account hierarchy and bring Account 1000 into R1 (Screen 2).

Screen 2

The title of the row comes from the database, and if this document were printed in color, Account 1000 in the Chart of Account pane would appear in red, indicating it has been used in the FML Painter.

Let's access the TAG row properties dialog. Place the cursor anywhere on R1 and right-click. Select Row Properties from the drop-down list box.

The Children area is where we want to concentrate our attention. Currently it says Show selected item, and all other objects are grayed out.

Let's run the report and see what we get:

                                      YTD
                       Actual      Actual

Profit Before Tax  3,739,153.  15,328,251.

Show selected item displays the value of that account alone. In an unconsolidated database, a parent account such as 1000 would have a value of zero. If we click on the down-arrow button in the Children area we can view other options for displaying children or sub-accounts:

Show selected item (default)
Show selected item consolidated
Show only children
Show only children to level…
Show with all children

Let's go through each option. Currently, Show selected item is the active option and the default. Let's access the row properties of R1 and choose Show selected item consolidated, then click OK.

Again, if this document were in color, Account 1000 in the right-hand pane would be green to indicate the account is being used multiple times in the FML Painter. Also notice above the Painter, in the FML Report Properties area, the Use Multiple Values box is now checked, confirming the green color of Account 1000 in the Chart of Accounts area.

At first it may seem strange that Account 1000 is being used multiple times, yet our FML report consists only of one row. Look closely at the GL_ACCOUNT in R1. Notice there is now a symbol appearing next to 1000. This symbol is two children holding hands, indicating that the value in the report reflects a consolidated value. Let's run the report:

                                           YTD
                         Actual         Actual

Profit Before Tax  402,037,775.  1,395,856,993.

The value of account 1000, Profit Before Tax is larger than our original unconsolidated report. Why? Selecting Show selected item consolidated adds up the values of all sub-accounts regardless of the account type or the mathematical sign. It's something you normally don't want to do. Let's change the Children option to Show only children and click OK.

First, notice in the Row Properties area the syntax of the code that is generated behind the scenes: 1000 GET CHILDREN ALL. Second, the symbol next to 1000 changes from children holding hands to a plus box. This plus box works the same as it does on a tree display: clicking it expands the information underneath.

The FML Painter displays the next level of sub-accounts for account 1000. All the rows have the same row number. This does not change the report output, which is determined by the Children value selected in the TAG properties dialog box, but is strictly for display purposes in the FML painter. The chart of accounts display in the right pane is not affected either. Clicking on the minus box returns the painter to a single R1 row.

Screen 3 shows the report output.

Screen 3

All four sub-levels associated with account 1000, but not the parent, are shown with this one Children selection. If you wanted to see the parent account, 1000, as well as all the children, you would select Show with all children in the TAG properties dialog box.

The last two Children options, Show only children to level… and Show with children to level…, allow you to pare back the detail in the report to a specific level of sub-accounts.