Reporting Dynamically From a Hierarchy

Hierarchical relationships between fields can be defined in a Master File, and automatically displayed using the Financial Modeling Language (FML). The parent and child fields must share data values, and their relationship should be hierarchical. The formats of the parent and child fields must both be either numeric or alphanumeric.

By examining these fields, it is possible to construct the entire organization chart or chart of accounts structure. However, to print the chart in a traditional FML report, you need to list the employee IDs or account numbers in the request syntax in the order in which they should appear on the report. If an employee or account is added, removed, or transferred, you have to change the report request to reflect this change in organizational structure. For example:

In contrast, with FML hierarchies you can define the hierarchical relationship between two fields in the Master File and load this information into memory. The FML request can then dynamically construct the rows that represent this relationship and display them in the report, starting at any point in the hierarchy. In the example shown, EMP_ID is called the hierarchy field.

Requirements for FML Hierarchies

In the Master File, use the PROPERTY=PARENT_OF and REFERENCE=hierarchyfld attributes to define the hierarchical relationship between two fields. See the Describing Data manual for information.
The hierarchy must be loaded into memory. This loaded hierarchy is called a chart. If the hierarchy is defined in the Master File and referenced by the FML request, it is loaded automatically. If you want to use a hierarchy defined in a Master File that is not either referenced in the FML request or joined to the Master File referenced in the FML request, issue the LOAD CHART command before issuing the FML request.

The number of charts that can be loaded is 16. Charts are automatically unloaded when the session ends.
In the FOR phrase of the FML request. Use the GET/WITH CHILDREN or ADD phrase to retrieve the hierarchical data starting at a specific point in the hierarchy.

To use FML hierarchies, the FOR field must either be:

The hierarchy field.
or
Used as the join field to a unique segment that has the hierarchy field. In this case, the hierarchy field must be the join field. Note that the condition that the join be unique only applies if the hierarchy is defined in the cross-referenced segment.

In other words, the FOR field must be in a parent-child hierarchy, or linked to one. The latter case allows transaction data that contains the hierarchy field to be joined to a separate data source that contains the hierarchy definition.

As with any FML request, a tagged row is displayed even if no data is found in the file for the tag values, with a period (.) representing the missing data. You can override this convention by adding the phrase WHEN EXISTS to the definition of a tagged row. This makes displaying a row dependent upon the existence of data for the tag.

Note: In order for the hierarchical indentations to be retained in HTML output, the setting SHOWBLANKS=ON must be in effect.

Top of page

Example: Defining a Hierarchy in a Master File

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 corporation, 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 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', $

Displaying an FML Hierarchy

How to:

Display an FML Hierarchy

The GET CHILDREN and WITH CHILDREN commands dynamically retrieve and display hierarchical data on the FML report. GET CHILDREN displays only the children, not the parent value referenced in the command. WITH CHILDREN displays the parent and then the children.

Top of page

Syntax: How to Display an FML Hierarchy

TABLE FILE filename 
{PRINT|SUM} ...
FOR hierarchyfld 
parentvalue {GET|WITH} CHILD[REN] [n|ALL] [AS CAPTION|'text'] [LABEL label]
.
.
.
END

where:

filename: Is the name of the file to be used in the FML request. If the hierarchy for this request cannot be loaded automatically, it must have been loaded previously by issuing the LOAD CHART command.
hierarchyfld: Is the hierarchy field name. If the request references a joined structure, the name must be the field name from the host file. The alias name is not supported.
parentvalue: Is the parent value for which the children are to be retrieved.
GET CHILDREN: Displays the hierarchy starting from the first child of the specified parentvalue. It does not include the parent in the display. (This corresponds to the FML syntax CHILD1 OVER CHILD2 OVER ...)
WITH CHILDREN: Displays the hierarchy starting from the specified parentvalue. It includes the parent in the display. (This corresponds to the FML syntax parentvalue OVER CHILD1 OVER CHILD2 OVER ...).
n|ALL: Is a positive integer from 1 to 99, specifying the number of levels of the hierarchy to display. If a number greater than 99 is specified, a warning message is displayed and n is set to 99. The default value is 1. Therefore, if n is omitted, only direct children are displayed. GET or WITH CHILDREN 2 displays direct children and grandchildren. GET or WITH CHILDREN 99 displays children to 99 levels. ALL is a synonym for 99. Each child instance is printed over the one that follows. Successive levels of the hierarchy field are indented two spaces from the previous level.
CAPTION: Indicates that the caption values to display should be taken from the field defined as the CAPTION in the Master File.
Note that the AS CAPTION phrase is supported for tagged rows, including those that do not use the GET/WITH CHILDREN or ADD syntax. However, the hierarchy must be defined (by specifying the PARENT_OF attribute) in order to load and display the caption values. If the hierarchy is not defined, the AS CAPTION phrase is ignored.
'text': Is a text string to use as the row title for the hierarchy field values. The CAPTION field defined in the Master File is not used as the caption on the report output.
label: Is an explicit row label. Each generated row is labeled with the specified label text.

Note: The hierarchy is displayed sorted by the parent field and, within parent, sorted by the hierarchy field.

See the Using Functions manual for information about the FMLFOR, FMLLIST, FMLCAP, and FMLINFO functions that return the tag values and captions used in an FML request.

Top of page

Example: Displaying an FML Hierarchy

The following request displays two levels of account numbers, starting from account 3000:

SET BLANKINDENT=ON 
TABLE FILE CENTGL
PRINT GL_ACCOUNT_PARENT
FOR GL_ACCOUNT  
3000 WITH CHILDREN 2 
END

The output is shown as follows.

             Parent
             ------
3000         1000
  3100       3000
    3110     3100
    3120     3100
    3130     3100
    3140     3100
    3200     3000
    3300     3200
    3400     3200
    3500     3200
    3600     3200
    3700     3200
    3800     3200
    3900     3200

Note that if the request specifies GET CHILDREN instead of WITH CHILDREN, the line for the parent value (3000) does not display on the report output.

Top of page

Example: Displaying an FML Hierarchy With Captions

The following request displays two levels of a charts of accounts hierarchy, starting with account 1000 (the top of the hierarchy), and displays the caption field values instead of the account numbers:

 
TABLE FILE CENTGL
PRINT GL_ACCOUNT_PARENT
FOR GL_ACCOUNT  
1000 WITH CHILDREN 2 AS CAPTION 
END

The output is shown as follows.

                                    Parent
                                    ------
Profit Before Tax                         
  Gross Margin                      1000  
    Sales Revenue                   2000  
    Cost Of Goods Sold              2000  
  Total Operating Expenses          1000  
    Selling Expenses                3000  
    General + Admin Expenses        3000  
  Total R+D Costs                   1000  
    Salaries                        5000  
    Misc. Equipment                 5000

Note that if the request specifies GET CHILDREN instead of WITH CHILDREN, the line for the parent value (1000, Profit Before Tax) does not display on the report output.

Consolidating an FML Hierarchy

How to:

The ADD command consolidates multiple levels of the hierarchy on one line of the FML report output. You can use ADD alone or in conjunction with GET CHILDREN or WITH CHILDREN. Note that ADD is designed to work with requests that use the SUM command. It is also designed to be used with detail-level data, not data that is consolidated.

When used alone, ADD aggregates the parent and children on one line of the report output, summing the numeric data values included on the line. This corresponds to the FML syntax parentvalue or CHILD1 OR CHILD2 OR ...

When used in conjunction with GET CHILDREN, ADD displays one line for each child of the specified parent value. Each line is a summation of that child and all of its children. You can specify the number of levels of children to display (which determines the number of lines generated on the report output) and the depth of summation under each child. By default, only direct children have a line in the report output, and the summary for each child includes all of its children.

When used in conjunction with WITH CHILDREN, ADD first displays a line in the report output that consists of the summation of the parent value and all of its children. Then it displays additional lines identical to those displayed by GET CHILDREN ADD.

In order to use a data record in more than one line of an FML report (for example, to display both detail and summary lines or to consolidate detail data at multiple levels), the following setting is required:

SET FORMULTIPLE=ON

Top of page

Syntax: How to Create One Summary Row for an FML Hierarchy

TABLE FILE filename  
SUM ...
FOR hierarchyfld 
parentvalue ADD [n|ALL] [AS CAPTION|'text'] [LABEL label]
.
.
.
END

where:

filename

Is the name of the file to be used in the FML request. If the hierarchy for this request cannot be loaded automatically, it must have been loaded previously by issuing the LOAD CHART command.

hierarchyfld

Is the hierarchy field name. If the request references a joined structure, the name must be the field name from the host file. The alias name is not supported.

parentvalue

Is the parent value that determines the starting point in the hierarchy for the aggregation.

ADD

Displays the parent and n levels of its children on one row, summing the numeric data values displayed on the row. This corresponds to the FML syntax parentvalue or CHILD1 OR CHILD2 OR ...

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:

FOR ...  
parentvalue                  OVER 
parentvalue ADD 1            OVER
RECAP CHILDSUM = R2-R1;

n|ALL

Is a positive integer from 1 to 99, specifying the number of levels of the hierarchy to aggregate. ALL is the default value. Therefore, if n is omitted, all children are included in the sum. If n is 1, only direct children are included. If n is 2, direct children and grandchildren are included. ADD 99 includes up to 99 levels of children. ALL is a synonym for 99.

CAPTION

Indicates that the caption of the parent value displays for the total row.

Note that the AS CAPTION phrase is supported for tagged rows, including those that do not use the GET CHILDREN or ADD syntax. However, the hierarchy must be defined (by specifying the PARENT_OF attribute) in order to load and display the caption values. If the hierarchy is not defined, the AS CAPTION phrase is ignored.

'text'

Is a text string to use as the row title for the aggregate row. The CAPTION field defined in the Master File is not used as the caption on the report output.

label

Is an explicit row label. Each generated row is labeled with the specified label text.

Top of page

Example: Displaying One Summary Line for an FML Hierarchy

The CENTSYSF data source contains detail-level financial data. To use the account hierarchy in the CENTGL data source with this financial data, the two data sources are joined. The data in CENTSYSF is stored with natural signs, which means, in financial terms, that revenues and liabilities are stored as negative numbers. The portion of the hierarchy used in this request contains only positive data.

Note that the join is not required to be unique, because the hierarchy is defined in the host segment.

First the WITH CHILDREN command displays the lines of the hierarchy starting with account Selling Expenses (3100). Note that only accounts with no children are populated in this detail-level data source. The ADD command then creates one line that is the sum of account 3100 and all of its children:

 
SET FORMULTIPLE=ON
JOIN SYS_ACCOUNT IN CENTGL TO ALL SYS_ACCOUNT IN CENTSYSF
TABLE FILE CENTGL
SUM NAT_AMOUNT/D10.0 NAT_YTDAMT/D10.0
FOR GL_ACCOUNT  
3100 WITH CHILDREN ALL AS CAPTION OVER 
BAR                               OVER  
3100 ADD AS CAPTION 
IF PERIOD EQ '2002/03'
END

The output is shown as follows.

                                          Month         YTD
                                          Actual        Actual
                                          ------        ------
Selling Expenses                               .             .
  Advertising                                  .             .
    TV/Radio                          1,049,146.    2,954,342.
    Print Media                         244,589.      721,448.
    Internet Advertising                  9,542.       29,578.
  Promotional Expenses                   53,719.      151,732.
  Joint Marketing                        97,135.      289,799.
  Bonuses/Commissions                   100,188.      304,199.
                                    ------------  ------------
Selling Expenses                      1,554,319.    4,451,098.

Top of page

Syntax: How to Consolidate FML Hierarchy Data to Any Level and Depth

TABLE FILE filename 
SUM ...
FOR hierarchyfld 
parentvalue {GET|WITH} CHILD[REN] [n|ALL] ADD [m|ALL]
 [AS CAPTION|'text'] [LABEL label]
.
.
.
END

where:

filename: Is the name of the file used in the FML request. If the hierarchy for this request cannot load automatically, it previously loaded by issuing the LOAD CHART command.
hierarchyfld: Is the hierarchy field name. If the request references a joined structure, the name must be the field name from the host file. The alias name is not supported.
parentvalue: Is the parent value that determines the starting point in the hierarchy for the aggregation.
GET|WITH: GET specifies that the first line generated on the report is the consolidated line for the first child of the parent value. WITH specifies that the first line generated on the report is the consolidated line for the parent value, followed by the consolidated lines for each of its children, to the level specified by n.
n|ALL: Is a positive integer from 1 to 99, specifying the number of levels of children to display. The line of output for each child has the sum of that child and its children to the depth specified for the ADD option. The default value is 1. Therefore, if n is omitted, each direct child has a line on the report. If n is 2, direct children and grandchildren each have a line on the report output. ALL is a synonym for 99.
ADD: Sums the hierarchy to the depth specified by m for each line generated by the GET or WITH CHILDREN command.
m|ALL: Is a positive integer from 1 to 99, specifying the number of levels of children to consolidate on each line of the report output. If a number greater than 99 is specified, a warning message is displayed and m is set to 99. The default value is ALL. Therefore, if m is omitted, the consolidated line sums all children. If m is 2, only direct children and grandchildren are consolidated for each line on the report output. ADD 99 aggregates children to 99 levels. ALL is a synonym for 99.
CAPTION: Indicates that the caption of the parent value displays for the total row.
Note that the AS CAPTION phrase is supported for tagged rows, including those that do not use the GET CHILDREN or ADD syntax. However, the hierarchy must be defined (by specifying the PARENT_OF attribute) in order to load and display the caption values. If the hierarchy is not defined, the AS CAPTION phrase is ignored.
'text': Is a text string to use as the row title for the aggregate row. The CAPTION field defined in the Master File is not used as the caption on the report output.
label: Is an explicit row label. Each generated row is labeled with the specified label text.

Top of page

Example: Consolidating FML Hierarchy Data

In the following request, the first WITH CHILD command displays the detail data for the hierarchy starting with account 3100. The next WITH CHILD command creates a consolidated line for the parent account (3100) and each direct child:

 
SET FORMULTIPLE=ON
JOIN SYS_ACCOUNT IN CENTGL TO ALL SYS_ACCOUNT IN CENTSYSF
TABLE FILE CENTGL
SUM NAT_AMOUNT/D10.0 NAT_YTDAMT/D10.0
FOR GL_ACCOUNT  
3100 WITH CHILDREN ALL AS CAPTION      OVER 
" "                                    OVER
BAR AS =                               OVER
" "                                    OVER  
3100 WITH CHILDREN ADD AS CAPTION 
IF PERIOD EQ '2002/03'
END

Note that the join is not required to be unique, because the hierarchy is defined in the host segment.

In the following 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 Selling Expenses (3100): Advertising, Promotional Expenses, Joint Marketing, and Bonuses/Commissions.
The line for Advertising is the sum of itself plus all of its children. If it has multiple levels of children, they are all added into the sum. The other direct children of 3100 do not themselves have children, so the sum on each of those lines consists of only the parent value.

                                          Month         YTD   
                                          Actual        Actual
                                          ------        ------
Selling Expenses                               .             .
  Advertising                                  .             .
    TV/Radio                          1,049,146.    2,954,342.
    Print Media                         244,589.      721,448.
    Internet Advertising                  9,542.       29,578.
  Promotional Expenses                   53,719.      151,732.
  Joint Marketing                        97,135.      289,799.
  Bonuses/Commissions                   100,188.      304,199.
                                                              
                                    ============  ============
Selling Expenses                      1,554,319.    4,451,098.
  Advertising                         1,303,277.    3,705,368.
  Promotional Expenses                   53,719.      151,732.
  Joint Marketing                        97,135.      289,799.
  Bonuses/Commissions                   100,188.      304,199.

Using GET CHILDREN instead of WITH CHILDREN eliminates the top line from each portion of the output. The remaining lines are the same:

                                        Month         YTD
                                        Actual        Actual
                                        ------        ------
Advertising                                  .             .
  TV/Radio                          1,049,146.    2,954,342.
  Print Media                         244,589.      721,448.
  Internet Advertising                  9,542.       29,578.
Promotional Expenses                   53,719.      151,732.
Joint Marketing                        97,135.      289,799.
Bonuses/Commissions                    100,188.      304,199.
                                  ============  ============
Advertising                         1,303,277.    3,705,368.
Promotional Expenses                   53,719.      151,732.
Joint Marketing                        97,135.      289,799.
Bonuses/Commissions                   100,188.      304,199.

The following request displays a consolidated line for account 2000 and each of its direct children and grandchildren.

SET FORMULTIPLE=ON
JOIN SYS_ACCOUNT IN CENTGL TO ALL SYS_ACCOUNT IN CENTSYSF
TABLE FILE CENTGL
SUM NAT_AMOUNT/D10.0 NAT_YTDAMT/D10.0
FOR GL_ACCOUNT  
2000 WITH CHILDREN 2 ADD AS CAPTION 
IF PERIOD EQ '2002/03'
END

The output is shown as follows.

                                          Month         YTD
                                          Actual        Actual
                                          ------        ------
Gross Margin                         -4,513,659.  -13,080,549.
  Sales Revenue                     -10,398,305.  -30,877,546.
    Retail Sales                     -8,237,253.  -24,539,197.
    Mail Order Sales                 -1,138,414.   -3,403,387.
    Internet Sales                   -1,022,638.   -2,934,962.
  Cost Of Goods Sold                  5,884,646.   17,796,997.
    Variable Material Costs           4,415,560.   13,410,629.
    Direct Labor                        961,143.    2,920,449.
    Fixed Costs                         507,943.    1,465,919.

Loading a Hierarchy Manually

How to:

Load a Hierarchy From One Master File for Use With a Separate Master File

Reference:

Usage Notes for FML Hierarchies

In most cases, a hierarchy loads automatically as a result of the request syntax. However, if you need to use a hierarchy defined in one Master File against a data source that is not joined to the hierarchy file (but that contains the same hierarchy field), you can manually load the hierarchy data using the LOAD CHART command.

The number of charts that can load is limited by available memory. Charts automatically unload when the session ends.

The chart loads by running a TABLE request that produces a list of parent values and their associated children:

TABLE FILE chartfile 
BY parentfield BY hierarchyfield 
[SUM captionfield]
END

The resulting chart contains the following information. It may also contain the associated captions, depending on whether the AS CAPTION phrase was used in the request:

  
parentfield     hierarchyfield 
-----------     --------------  
parentvalue1    child1 
parentvalue1    child2 
parentvalue1    child3 
  .
  .
  .

Top of page

Syntax: How to Load a Hierarchy From One Master File for Use With a Separate Master File

You can manually load the hierarchy data if you need to use a hierarchy defined in one Master File against a data source that is not joined to the hierarchy file but that contains the same hierarchy field.

Available memory dictates the number of charts that can load. Charts automatically unload when FOCUS terminates.

LOAD CHART chartfile[.sega].hierarchyfld 
   [FOR requestfile[[.segb].fieldb]]

where:

chartfile

Is the name of the Master File that contains the hierarchy information.

sega

Is the name of the segment that contains the hierarchy field. The segment name is only required if a field in another segment in the structure has the same field name as the hierarchy field.

hierarchyfld

Is the hierarchy field. It is required because a Master File can define multiple hierarchies.

FOR

Loads a hierarchy defined in a Master File that is not used in the FML report request. For example, if Master File B contains the hierarchy information but Master File A is used in the request (without a join between Master Files A and B), issue the following LOAD CHART command prior to the FML request:

LOAD CHART B.FLDB FOR A.FLDA
TABLE FILE A ...

requestfile

Is the name of the Master File used in the FML request.

segb

Is the name of the segment that contains the hierarchy field values in the Master File used in the FML request. It is not required if it has the same name as sega.

fieldb

Is the field in the Master File specified in the FML request that contains the values of the hierarchy field. It is not required if it has the same name as the hierarchy field.

Note:

If you issue the LOAD CHART command multiple times for the same hierarchy, the new hierarchy overlays the previous version in memory.
If you issue the LOAD CHART command for a data source that is dynamically joined to the hierarchy file, you must issue the JOIN command prior to issuing the LOAD CHART command.

Top of page

Reference: Usage Notes for FML Hierarchies

PROPERTY and REFERENCE are propagated to HOLD Master Files when HOLDATTR is set to ON.
The following setting is required in order to use a data record in more than one row of an FML request (for example, both a detail and summary row):
```
SET FORMULTIPLE=ON
```
When reporting against a rolled-up data source such as ESSBASE, the data values stored for the parent instance are an aggregate of all of its children. Do not use the ADD feature on consolidated data.
When reporting against a data source with shared members (such as ESSBASE), in which the same data can be defined multiple times with different hierarchy field values, data shared by two different parents is counted twice in an aggregation operation. To avoid this double aggregation, use the FST. operator in the SUM command for the shared fields.
In order for the hierarchical indentations to be retained in HTML output, the setting SHOWBLANKS=ON must be in effect.