FMLINFO: Returning FOR Values

How to:

Available Languages: reporting

The FMLINFO function returns the FOR value associated with each row in an FML report. With FMLINFO, you can use the appropriate FOR value in a COMPUTE command to do drill-downs and sign changes for each row in the report, even when the row is a summary row created using an OR list or a Financial Modeling Language (FML) Hierarchy ADD command.

Note: You can use the SET parameter FORMULTIPLE=ON to enable an incoming record to be used on more than one line in an FML report.

Syntax: How to Retain FOR Values in an FML Request 

FMLINFO('FORVALUE', output)

where:

'FORVALUE'

Alphanumeric

Returns the FOR value associated with each row in an FML report. If the FML row was generated as a sum of data records using the OR phrase, FMLINFO returns the first FOR value specified in the list of values. If the OR phrase was generated by an FML Hierarchy ADD command, FMLINFO returns the FOR value associated with the parent specified in the ADD command.

output

Alphanumeric

Is the name of the field that contains the result, or the format of the output value enclosed in single quotation marks.

Example: Retrieving FOR Values for FML Hierarchy Rows

The following request creates a field called PRINT_AMT that is the negative of the NAT_AMOUNT field for account numbers less than 2500 in the CENTSYSF data source. The CENTGL data source contains the hierarchy information for CENTSYSF. Therefore, CENTGL is joined to CENTSYSF for the request: 

SET FORMULTIPLE = ON
JOIN SYS_ACCOUNT IN CENTGL TO ALL SYS_ACCOUNT IN CENTSYSF
TABLE FILE CENTGL
SUM NAT_AMOUNT/D10 IN 30
COMPUTE PRINT_AMT/D10 = IF FMLINFO('FORVALUE','A7') LT '2500'
     THEN 0-NAT_AMOUNT ELSE NAT_AMOUNT;
COMPUTE FORV/A4 = FMLINFO('FORVALUE', 'A4');
COMPUTE ACTION/A9  = IF FORV LT '2500'
      THEN 'CHANGED' ELSE 'UNCHANGED';
FOR GL_ACCOUNT
2000 WITH CHILDREN 2 ADD AS CAPTION
END
Note: The parent value specified in the WITH CHILDREN ADD command (2000) is returned for the first row on the report. Each subsequent row is also a consolidated subsection of the hierarchy with a parent value that is returned by FMLINFO:
                                   Month 
                                  Actual     PRINT_AMT  FORV  ACTION
                                  ------     ---------  ----  ------ 
Gross Margin                 -25,639,223    25,639,223  2000  CHANGED
  Sales Revenue              -62,362,490    62,362,490  2100  CHANGED
    Retail Sales             -49,355,184    49,355,184  2200  CHANGED
    Mail Order Sales          -6,899,416     6,899,416  2300  CHANGED
    Internet Sales            -6,107,890     6,107,890  2400  CHANGED
  Cost Of Goods Sold          36,723,267    36,723,267  2500  UNCHANGED
    Variable Material Costs   27,438,625    27,438,625  2600  UNCHANGED
    Direct Labor               6,176,900     6,176,900  2700  UNCHANGED
    Fixed Costs                3,107,742     3,107,742  2800  UNCHANGED

Example: Using FMLINFO With an OR Phrase

The FOR value printed for the summary line is 1010, but FMLINFO returns the first value specified in the OR list, 1030: 

SET FORMULTIPLE = ON
TABLE FILE LEDGER 
SUM AMOUNT
COMPUTE RETURNEDFOR/A8 = FMLINFO('FORVALUE','A8');
FOR ACCOUNT
1010                  OVER 
1020                  OVER
1030                  OVER
BAR                   OVER
1030 OR 1020 OR 1010
END

The output is:

  
AMOUNT
 
RETURNEDFOR
 
1010
1020
1030
 
 8,784
 4,494
 7,961
 
1010 
1020 
1030
  
------
 
---------
 
1010
 
21,239
 
1030