FMLCAP: Retrieving FML Hierarchy Captions

How to:

Available Languages: reporting

The FMLCAP function returns the caption value for each row in an FML hierarchy request. In order to retrieve caption values, the Master File must define an FML hierarchy and the request must use the GET CHILDREN, ADD, or WITH CHILDREN option to retrieve hierarchy data. If the FOR field in the request does not have a caption field defined, FMLCAP returns a blank string.

FMLCAP is supported for COMPUTE but is not recommended for use with DEFINE.

Syntax: How to Retrieve Captions in an FML Request Using the FMLCAP Function

FMLCAP(fieldname|'format')

where:

fieldname

Is the name of the caption field.

'format'

Is the format of the caption field enclosed in single quotation marks.

Example: Retrieving FML Hierarchy Captions Using FMLCAP

The following request retrieves and aggregates the FML hierarchy that starts with the parent value 2000. FMLCAP retrieves the captions, while the actual account numbers appear as the FOR values.

SET FORMULTIPLE = ON
TABLE FILE CENTSTMT
SUM ACTUAL_AMT
COMPUTE CAP1/A30= FMLCAP(GL_ACCOUNT_CAPTION);
FOR GL_ACCOUNT
2000 WITH CHILDREN 2 ADD
END

The output is:

                Actual  CAP1 
                ------  ---- 
2000      313,611,852.  Gross Margin 
  2100    187,087,470.  Sales Revenue 
    2200   98,710,368.  Retail Sales 
    2300   13,798,832.  Mail Order Sales 
    2400   12,215,780.  Internet Sales 
  2500    100,885,159.  Cost Of Goods Sold 
    2600   54,877,250.  Variable Material Costs 
    2700    6,176,900.  Direct Labor 
    2800    3,107,742.  Fixed Costs