Saving and Retrieving Intermediate Report Results

In this section:

Many reports require results developed in prior reports. This can be accomplished only if a place is provided for storing intermediate values. An example is the need to compute net profit in an Income Statement prior to calculating equity in a Balance Sheet. FML can save selected rows from one or more models by posting them to a work file. The posted rows can then be picked up from the work file and reused.

The default work file is FOCPOST. This is a comma-delimited file from which you can report directly if a FOCPOST Master File is available. In order to use the work file in a request, you must assign a physical name to the FOCPOST ddname before running the report that posts the data, and again before running the report that picks up the data.

You can assign the physical name to the file by issuing a FILEDEF command on Windows and UNIX, or a TSO ALLOCATE or DYNAM ALLOCATE command on z/OS, before the request is run. You may create a FILEDEF command by using the Allocation Wizard.

While you cannot prepare an FML report entirely from data that you supply directly in your request, you can prepare a report entirely from data that is stored in a comma-delimited work file.

Posting Data

How to:

You can save any TAG, RECAP, or DATA row by posting the output to a file. You can use these rows as though they were provided in a DATA row.

The row is processed in the usual manner in the report, depending on its other options, and then posted. The label of the row is written first, followed by the numeric values of the columns, each comma-separated, and ending with the terminator character ($). For more information, see Posting Rows to a Work File.

Note: Only fields that are actually displayed on the report output are posted. Fields that are not printed (for example, fields specified with the NOPRINT option, extra fields that are created when you reformat fields in the request, or fields implied by use in a calculation) are not posted.

Syntax: How to Post Data to a File

The syntax for saving any TAG, RECAP, or DATA row is: 

POST [TO ddname]

where:

ddname

Is the logical name you assign to the work file in which you are posting data.

Add this syntax to any row you wish to post to the work file.

Example: Posting Rows to a Work File

The following request creates an FML report, and posts two tag rows to the LEDGEOUT work file. 

FILEDEF LEDGEOUT DISK [APP]\LEDGEOUT.DAT
DEFINE FILE LEDGER
CUR_YR/I5C=AMOUNT;
LAST_YR/I5C=.87*CUR_YR - 142;
END
TABLE FILE LEDGER
SUM CUR_YR LAST_YR
FOR ACCOUNT 
1100 LABEL AR POST TO LEDGEOUT OVER 
1200 LABEL INV POST TO LEDGEOUT 
END

The output is shown in the following image.

Syntax: How to Pick Up Data From a Work File

You can retrieve posted rows from any work file and use them as if they were provided in a DATA row by adding the following phrase to an FML request. 

DATA PICKUP [FROM ddname] id1 [OR id2 ...] [LABEL label] [AS 'text']

where:

ddname

Is the logical name of the work file from which you are retrieving data.

id

Is the label that was assigned in the work file to the posted row of data that is now being picked up.

label

Is the label you wish to assign to the data you are picking up.

The label you assign to the picked data can, but is not required to, match the label (id) of the posted data.

You can include LABEL and AS phrases, but WHEN EXISTS is not supported.

Note: The retrieved fields are mapped to all fields (printed or not) in the memory repository (internal matrix) of the report. If the matrix contains columns that do not correspond to the fields in the posted file, the retrieved values may be misaligned. For example, if you reformat a field in the PICKUP request, that field will be represented by two columns in the internal matrix. However, the posted file will have only one value representing that field, and the retrieved values will not be mapped properly to the associated columns in the matrix.

Example: Picking Up Data From a Work File

In the following example, the data in the LEDGER data source and in the LEDGEOUT work file are used in the RECAP calculation. To see how this file was created, refer to Posting Rows to a Work File.

Tip: You must assign a logical name to the file by issuing a FILEDEF command on Windows and UNIX, or a DYNAM ALLOCATE command on z/OS, before the request is run. You may create a FILEDEF command by using the Allocation Wizard. 

DEFINE FILE LEDGER
CUR_YR/I5C=AMOUNT;
LAST_YR/I5C=.87*CUR_YR - 142;
END
TABLE FILE LEDGER
SUM CUR_YR LAST_YR
FOR ACCOUNT
1010 TO 1030 AS 'CASH' LABEL CASH   OVER 
DATA PICKUP FROM LEDGEOUT AR  AS 'ACCOUNTS RECEIVABLE' LABEL AR   OVER 
DATA PICKUP FROM LEDGEOUT INV AS 'INVENTORY' LABEL INV            OVER
BAR                                 OVER
RECAP CUR_ASSET/I5C = CASH + AR + INV;
END

The output is shown in the following image.

The following line can be used to pick up the sum of the two accounts from LEDGEOUT. 

DATA PICKUP FROM LEDGEOUT AR OR INV
AS 'ACCTS REC AND INVENTORY'

Note: Since the rows in a PICKUP file are stored in standard comma-delimited format, they can be provided either from a prior posting, or directly by a user.