Reporting Dynamically From a Hierarchy

In this section:

How to:

Hierarchical relationships between fields can be defined in a Master File and automatically displayed using the Financial Report Painter. The parent and child fields must share data values and their relationship should be hierarchical. For example, suppose that:

By examining these fields, it is possible to construct the entire organization chart or charts of accounts structure. However, to display the chart in a traditional row-based financial report, you would have to list the employee IDs or account numbers in the request in the order in which they should appear on the report. If an employee or account is added, removed, or transferred, you would have to change the report request to reflect this change in organizational structure.

With FML hierarchies, you can define the hierarchical relationship between two fields in the Master File and load this information into memory. The report request can then dynamically construct the rows that represent this relationship and display them in the report, starting at any point in the hierarchy.

Tip: You can construct the Master File using a text editor or the Synonym Editor. For related information, see Requirements for FML Hierarchies and Describing Data for an FML Hierarchy.

In the Financial Report Painter, the hierarchy defined in the Master File is reflected in the FOR field values panel to the right of the Design matrix, as shown in the following illustration. If the hierarchy fields are defined with captions in the Master File, the resulting report indents the captions proportionate to their levels in the hierarchy.

For details about how the parent/child hierarchy is represented in the Master File, see Describing Data for an FML Hierarchy.

Note: The ability to define a hierarchy in a Master File is particularly useful when you are working with a cube data structure, such as ESSBASE. However, it can be employed with other data source types.


Top of page

x
Procedure: How to Specify Tags for Data Values in a Hierarchy

In the Design matrix:

  1. Select the row in which you want to place tag values that have been organized in a parent/child hierarchy. The hierarchy is reflected in the FOR field values panel to the right of the matrix.

    Note: To be able to use the same value in more than one row of the matrix, click the Use Multiple Values check box in the FML Report Properties area above the matrix.

  2. Click the Tag icon on the Financial Report Painter toolbar.

    or

    Right-click anywhere in the row (except on the label) and select Change Type to, and then Tag from the context menu.

    The TAG dialog box opens at the Options tab. (Note that when a hierarchy has been defined in the Master File against which you are reporting, a group of specialized options, described in steps 6-8, are included in the TAG dialog box.)

  3. In the Value box, enter a value from the hierarchy tree, or click the Browse button and select one from the list.
  4. Click the Add button to add the selected value to the Tags box.
  5. Select the value in the Tags box to activate the hierarchy fields.
  6. In the Children box, indicate what level of data you want to retrieve or consolidate in the current row of the matrix:
    Show selected item consolidated

    Displays a single summary row for the numeric data values of all children.

    Show only children

    Displays the hierarchy starting with the first child of the specified parent, up to 99 levels deep. Each child instance appears over the next child instance. The parent level is not included in the output.

    Successive levels of the hierarchy field are indented two spaces from the previous level. You can change the spacing of these indents. For more information, see How to Adjust Indents for Captions in a Hierarchy.

    Show only children to level... Level: n

    Displays children up to the level you indicate. The default value is 1. Therefore, if n is omitted, direct children appear. Level 2 retrieves direct children and grandchildren. The parent level is not included in the output.

    Show with all children

    Displays the hierarchy starting with the specified parent. (Level 99 is equivalent to ALL.) The parent level is included in the output.

    Show with children to level... Level: n

    Displays the values for the specified parent tag and its children, up to 99 levels deep. The default value is 1. Therefore, if n is omitted, direct children appear. The parent level is including in the output.

  7. By default, the Display children's caption box is checked. This enables you to display descriptive text, defined in the Master File, in place of the hierarchy field FOR values. If you do not want the caption to appear, uncheck the Display children's captions box.
    • If you add a parent to the matrix and specify Show selected item, the caption is applied. This value appears in the Title input line, where you can edit it.
    • If you add a parent and specify an option that also retrieves its children, captions for the children appear in the Title column. However, the cells are gray, indicating that you cannot change these titles. (This happens whether Display children's caption is on or off.)
    • If no caption is specified in the Master File, the tag value appears as the title. However, you can provide a descriptive title for the row directly in the Title column on the matrix or in the Title input box on the General tab.
  8. If you chose an option that retrieves children in step 6, you can check the Consolidate box on the Options tabs to display a summary row for children up to the level indicated, immediately following the detail rows for the children being summed.
  9. Click OK or select the General tab to continue.
  10. Row label (R1, and so on) will automatically appear in the Label box.

    To supply an explicit row label to replace the default label (R1, and so on) in the matrix, type it into the Label input box. (The default label is retained internally.)

    The same label applies to the parent level and all child levels.

  11. Optionally, you can select a formatting check box from the General tab: The formatting check box options are:
    • Invisible
    • When Exists
    • Post to

    For details, see Suppressing the Display of Rows and Saving and Retrieving Intermediate Report Results.

  12. Click OK.

The matrix shows TAG in the Row Type column for the parent values. Each parent value for children that has been retrieved is preceded by a plus sign (+) that you can click to display the children. The plus sign (+) becomes a minus sign (-) that you can click to roll up the child values.

If you entered an explicit label in the Label input box, this element will appear in the matrix.

If you selected the Use Multiple Values check box in the FML Report Properties area above the matrix, the used value remains available in the FOR field values panel. It appears in red to indicate that it has already been added to the matrix.

Tip: You can also drag (or double-click) a tag from the FOR field values panel to a desired location on the matrix, then right-click in the TAG row and select Row Properties to open the TAG dialog box.


Top of page

x
Procedure: How to Adjust Indents for Captions in a Hierarchy

To clarify relationships within a hierarchy, the captions (titles) of values are indented at each level. To adjust the indents:

  1. Choose Indent from the Edit menu. The Indent dialog box opens.

    If a hierarchy has not been defined in the Master File for the data you are reporting against, the Indent option does not appear.

  2. Choose one of the following option buttons:
    • Use default value of indent. Specifies how many spaces to indent child entries.
    • Turn indent off. This option left-justifies titles for values at all levels of the hierarchy.
    • Select a value of indent, then enter an explicit measurement to represent the number of spaces to be indented based on the number of blank spaces preceding the caption text in the Master File and the unit of measurement defined in the Report Options Output tab (inches, centimeters, or points). For example, suppose that the caption text is preceded by two blanks and the base measurement is inches. If you enter .2, each level of values in the hierarchy will be indented .2 inches for each leading space (.2" x 2) from the previous level.


Top of page

x
Displaying an FML Hierarchy

When reporting from an FML hierarchy, you can dynamically retrieve and display hierarchical data using two variations:

In either case, you can show all children for the specified parent, or children to a specified level in the hierarchy, up to 99 levels deep. The default hierarchy depth is one level. To highlight these relationships visually, successive levels of the hierarchy field are indented two spaces from the previous level. (You can change the indentations. See How to Adjust Indents for Captions in a Hierarchy.)

The hierarchy appears sorted by the parent field and, within parent, sorted by the hierarchy field.

When displaying a hierarchy, you can show either detail or summary data for fields other than the For field in the request. You can also replace the For field value with a descriptive caption.



Example: Displaying an FML Hierarchy With Captions

The following example displays two levels of a charts of accounts hierarchy and shows descriptive captions defined in the Master File, instead of the account numbers, in the report.

  1. Create a report procedure in Developer Studio and name the file fmlhiercaps.
  2. When prompted, choose Report Painter as your reporting tool (it is the default) and choose CENTGL as your data source. To examine this data, see Sample Data for FML Hierarchy Examples.

    Report Painter opens with CENTGL fields listed in the Object Inspector panel.

  3. Drag the field GL_ACCOUNT_PARENT into the Report Painter window. The column title associated with this field is Parent. Parent is a Detail field by default.
  4. Drag GL_ACCOUNT into the Report Painter window. The column title associated with GL_ACCOUNT is Ledger Account. Select Ledger Account and click the For button. (This will become the controlling field in your financial report.)

  5. Click the Matrix tab (located toward the bottom of the Report Painter window) to open the Design matrix. Note that GL_ACCOUNT (the For field) is the title of the second column and its values appear in the For field values panel at the right of the matrix. You will be populating the matrix with these values.
  6. Begin by dragging the tag 1000 from the FOR field values panel into row R1 of the matrix. The 1000 account tag appears in the GL_ACCOUNT column.
  7. In row R1, right-click 1000 and select Row Properties from the menu. The TAG dialog box opens, with 1000 listed in the Tags box.
  8. In the Children box, choose Show with children to level from the drop-down list, then select 2 in the Level box to display two levels of the hierarchy, with account 1000 as the starting point (or parent level). The Display children’s caption check box is selected by default. This will show the descriptive titles for the children, rather than their tag values (1000, 2000), on the report.

    The dialog box appears as shown in the following image.

    Tag Dialog box

  9. Click the plus (+) sign next to 1000 to expand the hierarchy one level.

    The matrix appears, as shown in the following image.

    If you want to expand the hierarchy another level, repeat the process.

  10. To add some quick styling, click Report Options from the toolbar above the matrix. The Report Options dialog box opens.
    • On the Style tab, click the Style File Selection button. The StyleSheet Selection dialog box opens.
    • Click the Add new item button and select deflt1.sty from the StyleSheet File Selection dialog box.
    • Click OK.

      The selected StyleSheet is added to the Include StyleSheet File section.

    • Click Finish to close the StyleSheet Selection dialog box and click OK to close the Report Options dialog box.

      The StyleSheet is applied to the report.

  11. Click the Run icon on the toolbar to see the report, which lists the account numbers associated with the levels of the parent/child hierarchy. The indents for the hierarchy levels are set by default.

    parent/child hierarchy

Tip: If you want to see the children in the hierarchy without the parent, choose Show only children to level 2 in step 8, rather than Show with children to level 2. Without the parent line, the report would look like the following image.

show only children to level 2


Top of page

x
Consolidating an FML Hierarchy

How to:

Reference:

The Consolidate option consolidates multiple levels of the hierarchy on one line of the report output. Consolidate can be used alone or in conjunction with the Show only children or Show with children options. Consolidation is designed to work with requests that use the Sum option for fields other than the FOR field. It is also designed to be used with detail level financial data, not data that is already consolidated.

In order to use a data record in more than one line of a financial report (for example, to display both detail and summary lines or to consolidate detail data at multiple levels), select the option Use Multiple Values before you begin to populate the matrix.



Example: Displaying One Summary Line for an FML Hierarchy

Data Detour: For this example, you will use two data sources: CENTGL and CENTSYSF. CENTSYSF contains detail level financial data. CENTGL defines the account hierarchy. To use the financial data with the account hierarchy, you must join the two data sources.

Before you complete this example, examine the data you will be using. While you can follow the flow of this example without looking at the data, doing so will help you understand the process and the output more fully. See Sample Data for FML Hierarchy Examples.

  1. In Developer Studio, create a procedure and name the file fmlhiersumrow.
  2. When prompted, choose Report Painter as your reporting tool (it is the default) and choose CENTGL as your host data source. The component connector window opens briefly, followed by the Report Painter, where CENTGL fields are listed in the Object Inspector panel.
  3. Click the Join button from the Setup toolbar. The Join tool opens, listing the fields from CENTGL.MAS in a resizable window. Click the Add File (+) icon on the toolbar. When prompted, specify CENTSYSF as the cross-reference file. The two data sources are automatically joined at their respective SYS_ACCOUNT fields.
  4. Run the Join and close the tool. The fields from CENTGL and CENTSYSF are listed in the Object Inspector.
  5. Drag GL_ACCOUNT (Ledger Account), NAT_AMOUNT (Month Actual), and NAT_YTDAMT (YTD Actual) into the Report Painter window.
  6. Select Month Actual and click the Sum button. YTD Actual automatically becomes a Sum field. Select Ledger Account and click the For button. (The For field will become the controlling field in your financial report.)

  7. Click the Matrix tab below Report Painter to open the Design matrix. Note that GL_ACCOUNT (the FOR field) is the title of the second column and its values appear in the For field values panel at the right of the matrix. You will be populating the matrix with these values.
  8. First, click the Use Multiple Values check box above the matrix. This will enable you to use the same For field value more than once, to present both the detail data and the consolidation.
  9. Drag 3100 onto the matrix in row R1. Right-click on 3100 and select Row Properties.

    The TAG dialog box opens, with 3100 in the Tags list.

  10. In the Children box, select Show with all children and accept the default to Display children's caption.

    This will generate detail data for the parent value (3100) and all of its children, represented on the report by their descriptive captions rather than their account numbers.

    Tag Dialog box

  11. To add an underline below the detailed numeric data, right-click in row R2, select Change Row type to, and click the option button for Bar. The BAR dialog box opens. Accept the default underline character.
  12. Drag 3100 into the matrix again. Right-click and choose Row Properties. The TAG dialog box opens. This time, in the Children box, select Show selected item consolidated to generate one summary line for Selling Expenses and all of its children.

    Tag Dialog box

    The matrix appears as shown in the following image.

    matrix

  13. Click the plus (+) sign next to 3100 in row R1 to expand the hierarchy.

    expand hierarchy

    Notice that Advertising has its own children, while the other children of Selling Expenses do not. Notice also that the consolidated row, R3, has no plus sign (+) or hierarchy to expand.

  14. To add some quick styling, select Styling from the Report menu. The Report Options dialog box opens at the Style tab.
    • Click the Style File Selection button. The StyleSheet Selection dialog box opens.
    • Click the Add new item button and select deflt1.sty from the StyleSheet File Selection dialog box.
    • Click Ok.

      The selected StyleSheet is added to the Include StyleSheet File section.

    • Click Finish to close the StyleSheet Selection dialog box and click OK to close the Report Options dialog box.

      The StyleSheet is applied to the report.

  15. Click the Run icon in the toolbar above the matrix to generate the following output.

    Run

Note that only accounts with no children are populated in the detail level data source (CENTSYF), therefore, no values appear for Selling Expenses and Advertising. The consolidation row accurately sums the detailed data.

Tip: To display the sum of just the children, you must display the parent row, display the summary row, and use a RECAP to subtract the parent row from the sum. For example:

RECAP CHILDSUM = R2-R1;



x
Procedure: How to Consolidate FML Hierarchy Data

The following procedure is an example of how to consolidate FML hierarchy data.

  1. Create a procedure called fmlconsolidate in Developer Studio, following steps 1-8 in the example, Displaying One Summary Line for an FML Hierarchy. Note that this example uses the data described in Sample Data for FML Hierarchy Examples.

    Tip: Rather than starting from scratch, you can open the matrix created in Displaying One Summary Line for an FML Hierarchy, delete the content of the matrix, and then complete the steps that follow.

  2. You should be looking at an empty matrix, with Use Multiple Value checked.

    Drag 3100 onto the matrix in row R1. Right-click on 3100 and select Row Properties. The TAG dialog box opens, with 3100 in the Tags list.

  3. In the Children box, select Show with all children and accept the default to Display children's captions. This will generate detail data for the hierarchy, starting with account 3100. Descriptive captions, rather than account numbers, will display in the output.
  4. To separate the detailed output in the top section of the report from the consolidated output in the bottom section, you will add a blank row. Right-click in row R2 and select Change Row Type to Text. The TEXT dialog box opens. Leave the input box blank to specify a row with no content and click OK.
  5. To underline the numeric columns, right-click in row R3, and select Change Row Type to Bar. Accept the default underline character and click OK.
  6. Repeat step 4 to make row R4 a blank line.

    The matrix appears, as shown in the following image.

    matrix

  7. To create a consolidated line for the parent account (3100) and each direct child, drag account 3100 into row R5, right-click 3100, and select Row Properties. In the TAG dialog box, select Show with all children and click the Consolidate check box.

    Tag Dialog box

    The matrix appears, as shown in the following image.

    matrix

  8. Before you run the report, limit the data to be retrieved to the period from 2002-2003. To do this, click the Report menu at the top of the screen and choose the Where/If tab. The Expression Builder opens. Using any method you choose, create the following expression:
    PERIOD EQ '2002/03'
  9. Click OK.
  10. Click the Run icon.

    The output looks like the following image.

    output

    The top portion shows the detail level data.

    The bottom portion shows the consolidated data. In the consolidated portion of the report:

    • There is one line for the parent that is the sum of itself, plus all of its children to all levels.
    • There is one line for each direct child of account 3100 (Selling Expenses): Advertising, Promotional Expenses, Joint Marketing, and Bonuses/Commissions.
    • The line for Advertising is the sum of itself plus all of its children. If it had multiple levels of children, they would all have been added into the sum. The other direct children of 3100 did not themselves have children, so the sum on each of those lines consists of only the parent value.

Tip: If you wanted to see the children in the hierarchy without the parent, you could choose Show only children, rather than Show with children. Without the parent line, the report would output as shown in the following image.

show only children

What makes this reporting dynamic?

Suppose that you run this report weekly. If you ran the report today, and tomorrow another account were added as a child of 3100, the report for next week would automatically reflect the change in the hierarchy, as well as changes to the data, based on the versatile parent/child declaration in the Master File. No adjustment would be needed to keep either the Master File or the report request up to date.



x
Reference: Sample Data for FML Hierarchy Examples

The CENTGL Master File contains a charts of accounts hierarchy. The field GL_ACCOUNT_PARENT is the parent field in the hierarchy. The field GL_ACCOUNT is the hierarchy field. The field GL_ACCOUNT_CAPTION can be used as the descriptive caption for the hierarchy field.

FILE=CENTGL       ,SUFFIX=FOC
SEGNAME=ACCOUNTS   ,SEGTYPE=S01
FIELDNAME=GL_ACCOUNT,           ALIAS=GLACCT,  FORMAT=A7,
            TITLE='Ledger,Account', FIELDTYPE=I, $
FIELDNAME=GL_ACCOUNT_PARENT,    ALIAS=GLPAR,   FORMAT=A7,
            TITLE=Parent,
            PROPERTY=PARENT_OF, REFERENCE=GL_ACCOUNT, $
FIELDNAME=GL_ACCOUNT_TYPE,      ALIAS=GLTYPE,  FORMAT=A1,
            TITLE=Type,$
FIELDNAME=GL_ROLLUP_OP,         ALIAS=GLROLL,  FORMAT=A1,
            TITLE=Op, $
FIELDNAME=GL_ACCOUNT_LEVEL,     ALIAS=GLLEVEL, FORMAT=I3,
            TITLE=Lev, $
FIELDNAME=GL_ACCOUNT_CAPTION,   ALIAS=GLCAP,  FORMAT=A30,
            TITLE=Caption,
            PROPERTY=CAPTION, REFERENCE=GL_ACCOUNT, $
FIELDNAME=SYS_ACCOUNT,          ALIAS=ALINE,   FORMAT=A6,
            TITLE='System,Account,Line', MISSING=ON, $

The CENTSYSF data source contains detail-level financial data. This is unconsolidated financial data for a fictional company, CenturyCorp. It is designed to be separate from the CENTGL database as if it came from an external accounting system. It uses a different account line system (SYS_ACCOUNT) which can be joined to the SYS_ACCOUNT field in CENTGL. Data uses natural signs (expenses are positive, revenue is negative).

FILE=CENTSYSF     ,SUFFIX=FOC
SEGNAME=RAWDATA   ,SEGTYPE=S2
FIELDNAME = SYS_ACCOUNT   ,  ,A6       , FIELDTYPE=I,
            TITLE='System,Account,Line', $
FIELDNAME = PERIOD        ,  ,YYM      , FIELDTYPE=I,$
FIELDNAME = NAT_AMOUNT    ,  ,D10.0    , TITLE='Month,Actual', $
FIELDNAME = NAT_BUDGET    ,  ,D10.0    , TITLE='Month,Budget', $
FIELDNAME = NAT_YTDAMT    ,  ,D12.0    , TITLE='YTD,Actual', $
FIELDNAME = NAT_YTDBUD    ,  ,D12.0    , TITLE='YTD,Budget', $

You can create an FML hierarchy in a Master File using a text editor or the Synonym Editor. For details, see Describing Data for an FML Hierarchy.


WebFOCUS