An FML report can refer to explicit columns, as well as explicit rows. You can refer to columns using:
You can perform a calculation for one column or for a specific set of columns. To identify the columns, place the column number in parentheses after the label name.
DEFINE FILE LEDGER
CUR_YR/I5C=AMOUNT;
LAST_YR/I5C=.87*CUR_YR - 142;
END
TABLE FILE LEDGER
SUM CUR_YR AS 'CURRENT,YEAR'
LAST_YR AS 'LAST,YEAR'
FOR ACCOUNT
1010 AS 'CASH ON HAND' OVER
1020 AS 'DEMAND DEPOSITS' OVER
1030 AS 'TIME DEPOSITS' OVER
BAR OVER
RECAP TOTCASH/I5C = R1 + R2 + R3; AS 'TOTAL CASH' OVER
" " OVER
RECAP GROCASH(2)/F5.2 = 100*TOTCASH(1)/TOTCASH(2) - 100;
AS 'CASH GROWTH(%)'
ENDIn the second RECAP expression, note that:
The RECAP value is only calculated for the column specified.
The output is shown in the following image.
CURRENT LAST
YEAR YEAR
------- ----
CASH ON HAND 8,784 7,214
DEMAND DEPOSITS 4,494 3,482
TIME DEPOSITS 7,961 6,499
------ ------
TOTAL CASH 21,239 17,195
CASH GROWTH(%) 23.52After data retrieval is completed, a single column is calculated all at once, and multiple columns one by one.
When a set of contiguous columns is needed within a RECAP, you can separate the first and last column numbers with commas. For example, DIFFERENCE (2,5) indicates that you want to compute the results for columns 2 through 5.
In this example, the RECAP calculation for ATOT occurs only for columns 2 and 3, as specified in the request. No calculation is performed for column 1.
DEFINE FILE LEDGER
CUR_YR/I5C=AMOUNT;
LAST_YR/I5C=.87*CUR_YR - 142;
NEXT_YR/I5C=1.13*CUR_YR + 222;
END
TABLE FILE LEDGER
SUM NEXT_YR CUR_YR LAST_YR
FOR ACCOUNT
10$$ AS 'CASH' OVER
1100 AS 'ACCOUNTS RECEIVABLE' OVER
1200 AS 'INVENTORY' OVER
BAR OVER
RECAP ATOT(2,3)/I5C = R1 + R2 + R3;
AS 'ASSETS--ACTUAL'
ENDThe output is shown in the following image.
NEXT_YR CUR_YR LAST_YR
------- ------ -------
CASH 25,991 21,239 17,195
ACCOUNTS RECEIVABLE 21,941 18,829 15,954
INVENTORY 31,522 27,307 23,329
------- ------ -------
ASSETS--ACTUAL 67,375 56,478
How to: |
When you need a calculation for every other or every third column instead of every column, you can supply a factor, or column address, to do this. Column addressing is useful when several data fields are displayed within each value of a column sort.
The left-hand side of the expression has the form:
value(s,e,i)[/format]=
where:
In the following statement, there are two columns for each month:
SUM ACTUAL AND FORECAST ACROSS MONTH
If you want to perform a calculation only for the ACTUAL data, control the placement of the results with a RECAP in the form:
RECAP calcname(1,*,2)=expression;
The asterisk means to continue the RECAP for all odd-numbered columns (beginning in column 1, with an increment of 2, for all columns).
A calculation can use a specific column as a base, and refer to all other columns by their displacement from that column. The column to the left of the base column has a displacement of -1 relative to the base column. The column to the right has a displacement of +1. For example,
COMP=FIX(*)-FIX(*-1);
can refer to the change in fixed assets from one period to the next. The reference to COMP=FIX(*) is equivalent to COMP=FIX.
When referring to a prior column, the column must already have been retrieved, or its value is zero.
This example computes the change in cash (CHGCASH) for columns 1 and 2.
DEFINE FILE LEDGER
CUR_YR/I5C=AMOUNT;
LAST_YR/I5C=.87*CUR_YR - 142;
NEXT_YR/I5C=1.13*CUR_YR + 222;
END
TABLE FILE LEDGER
SUM NEXT_YR CUR_YR LAST_YR
FOR ACCOUNT
10$$ AS 'TOTAL CASH' LABEL TOTCASH OVER
" " OVER
RECAP CHGCASH(1,2)/I5SC = TOTCASH(*) - TOTCASH(*+1); AS 'CHANGE IN CASH'
ENDThe output is shown in the following image.
NEXT_YR CUR_YR LAST_YR
------- ------ -------
TOTAL CASH 25,991 21,239 17,195
CHANGE IN CASH 4,752 4,044How to: |
Column notation assigns a sequential column number to each column in the internal matrix created for a report request. If you want to control the creation of column reference numbers for the columns that are used in your report, use the CNOTATION column notation command.
Because column numbers refer to columns in the internal matrix, they are assigned after retrieval and aggregation of data are completed. Columns created and displayed in a report are stored in the internal matrix, and columns that are not displayed in a report may also be generated and stored in the internal matrix. Columns stored in the internal matrix include calculated values, reformatted field values, BY fields, fields with the NOPRINT option, and certain RECAP calculations such as FORECAST and REGRESS. Every other column in the internal matrix is assigned a column number by default which means you have to account for all internally generated columns if you want to refer to the appropriate column value in your request.
You can change the default assignment of column reference numbers by using the SET CNOTATION=PRINTONLY command which assigns column numbers only to columns that display in the report output. You can use column notation in COMPUTE and RECAP commands to refer to these columns in your request.
SET CNOTATION={ALL|PRINTONLY|EXPLICIT}where:
Note: CNOTATION is not supported in an ON TABLE phrase.
When a report is sorted using the ACROSS phrase, all of the retrieved values are aligned under their appropriate columns. Each column has a title consisting of one value of the ACROSS field. The entire column of data can be addressed by this value in a RECAP calculation.
The following request uses a factor that depends on the value of the ACROSS field (YEAR) to calculate the inventory cost for each year. It then calculates the profit by summing the assets and subtracting the inventory cost for each year.
TABLE FILE LEDGER
SUM AMOUNT ACROSS YEAR
FOR ACCOUNT
10$$ AS 'CASH' LABEL CASH OVER
1100 AS 'ACCOUNTS RECEIVABLE' LABEL RECEIVE OVER
BAR OVER
1200 AS 'INVENTORY VALUE' LABEL INVENT OVER
RECAP INVENTORY_FACTOR/F5.2 = IF YEAR LT '1986'
THEN 1.1 ELSE 1.25; AS 'INVENTORY COST FACTOR' OVER
RECAP INVENTORY_COST = INVENTORY_FACTOR * INVENT;
AS 'INVENTORY COST' OVER
BAR OVER
RECAP PROFIT = CASH + RECEIVE - INVENTORY_COST;
ENDThe output is shown in the following image.
YEAR
1985 1986 1987
-----------------------------------------------
CASH 5,663 7,001 8,575
ACCOUNTS RECEIVABLE 5,295 6,250 7,284
------ ------ ------
INVENTORY VALUE 7,754 9,076 10,477
INVENTORY COST FACTOR 1.10 1.25 1.25
INVENTORY COST 8,529 11,345 13,096
------ ------ ------
PROFIT 2,429 1,906 2,763
| Information Builders |