Assigning Column Reference Numbers

In this section:

How 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 command which can assign column numbers only to columns that display in the report output or to all fields referenced in the report request. You can use column notation in COMPUTE and RECAP commands to refer to these columns in your request.


Top of page

x
Syntax: How to Control the Creation of Column Reference Numbers
SET CNOTATION={ALL|PRINTONLY|EXPLICIT}

where:

ALL
Assigns column reference numbers to every column in the internal matrix. ALL is the default value.
PRINTONLY
Assigns column reference numbers only to columns that display in the report output.
EXPLICIT
Assigns column reference numbers to all fields referenced in the request, whether displayed or not.

Top of page

x
Using Column Notation in a Report Request

Reference:

To create a column reference in a request, you can:



Example: Using Column Notation in a Non-FML Request With CNOTATION=ALL

In the following request with CNOTATION=ALL, the product of C1 and C2 does not calculate TRANSTOT times QUANTITY because the reformatting generates additional columns.

SET CNOTATION = ALL
TABLE FILE VIDEOTRK
SUM TRANSTOT/D12.2 QUANTITY/D12.2 
AND COMPUTE
PRODUCT = C1 * C2;
BY TRANSDATE
END

The output is:

TRANSDATE        TRANSTOT        QUANTITY         PRODUCT
---------        --------        --------         -------
 91/06/17           57.03           12.00        3,252.42
 91/06/18           21.25            2.00          451.56
 91/06/19           38.17            5.00        1,456.95
 91/06/20           14.23            3.00          202.49
 91/06/21           44.72            7.00        1,999.88
 91/06/24          126.28           12.00       15,946.63
 91/06/25           47.74            8.00        2,279.11
 91/06/26           40.97            2.00        1,678.54
 91/06/27           60.24            9.00        3,628.85
 91/06/28           31.00            3.00          961.00

BY fields do not get a column reference, so the first column reference is for TRANSTOT with its original format, then the reformatted version. Next is QUANTITY with its original format and then the reformatted version. Last is the calculated value, PRODUCT.



Example: Using Column Notation in a Non-FML Request With CNOTATION=PRINTONLY

Setting CNOTATION=PRINTONLY assigns column references to the output columns only. In this case, the product of C1 and C2 does calculate TRANSTOT times QUANTITY.

SET CNOTATION = PRINTONLY
 
TABLE FILE VIDEOTRK
SUM TRANSTOT/D12.2 QUANTITY/D12.2
AND COMPUTE
PRODUCT = C1 * C2;
BY TRANSDATE
END

The output is:

TRANSDATE        TRANSTOT        QUANTITY         PRODUCT
---------        --------        --------         -------
 91/06/17           57.03           12.00          684.36
 91/06/18           21.25            2.00           42.50
 91/06/19           38.17            5.00          190.85
 91/06/20           14.23            3.00           42.69
 91/06/21           44.72            7.00          313.04
 91/06/24          126.28           12.00        1,515.36
 91/06/25           47.74            8.00          381.92
 91/06/26           40.97            2.00           81.94
 91/06/27           60.24            9.00          542.16
 91/06/28           31.00            3.00           93.00


Example: Using CNOTATION=PRINTONLY With Column Numbers in an FML Request

In the following request, the reformatting of fields generates additional columns in the internal matrix. In the second RECAP expression, note that because of the CNOTATION setting:

SET CNOTATION=PRINTONLY
DEFINE FILE LEDGER
CUR_YR/I5C=AMOUNT;
LAST_YR/I5C=.87*CUR_YR - 142;
END
TABLE FILE LEDGER
SUM CUR_YR/F9.2 AS 'CURRENT,YEAR'
LAST_YR/F9.2 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/F9.2C= R1 + R2 + R3; AS 'TOTAL CASH' OVER
" "                                                OVER
RECAP GROCASH(2)/F9.2C=100*TOTCASH(1)/TOTCASH(2) - 100;
AS 'CASH GROWTH(%)'
END

The output is:

                  CURRENT      LAST
                  YEAR         YEAR
                  -------      ----
CASH ON HAND      8784.00   7214.00
DEMAND DEPOSITS   4494.00   3482.00
TIME DEPOSITS     7961.00   6499.00
                --------- ---------
TOTAL CASH      21,239.00 17,195.00
                                   
CASH GROWTH(%)                23.52


Example: Using CNOTATION=PRINTONLY to RECAP Over Contiguous Columns in an FML Request

In this example, the RECAP calculation for ATOT occurs only for displayed columns 2 and 3, as specified in the request. No calculation is performed for displayed column 1.

SET CNOTATION=PRINTONLY
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/F9.2 CUR_YR/F9.2 LAST_YR/F9.2
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'
END

The output is:

                      NEXT_YR    CUR_YR   LAST_YR
                      -------    ------   -------
CASH                 25991.00  21239.00  17195.00
ACCOUNTS RECEIVABLE  21941.00  18829.00  15954.00
INVENTORY            31522.00  27307.00  23329.00
                    --------- --------- ---------
ASSETS  ACTUAL                   67,375    56,478



Example: Using CNOTATION=PRINTONLY With Relative Column Addressing in an FML Request

This example computes the change in cash (CHGCASH) for displayed columns 1 and 2.

SET CNOTATION=PRINTONLY
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/F9.2 CUR_YR/F9.2 LAST_YR/F9.2
FOR ACCOUNT
10$$ AS 'TOTAL CASH' LABEL TOTCASH           OVER
" "                                          OVER
RECAP CHGCASH(1,2)/I5SC = TOTCASH(*) - TOTCASH(*+1); AS 'CHANGE IN CASH'
END

The output is:

                  NEXT_YR     CUR_YR    LAST_YR
                  -------     ------    -------
TOTAL CASH       25991.00   21239.00   17195.00
                                               
CHANGE IN CASH      4,752      4,044           



Example: Using CNOTATION=PRINTONLY With Cell Notation in an FML Request

In this request, two RECAP expressions derive VARIANCEs (EVAR and WVAR) by subtracting values in four displayed columns (1, 2, 3, 4) in row three (PROFIT); these values are identified using cell notation (r,c).

SET CNOTATION=PRINTONLY
TABLE FILE REGION
SUM E_ACTUAL/F9.2 E_BUDGET/F9.2 W_ACTUAL/F9.2 W_BUDGET/F9.2
FOR ACCOUNT
3000 AS 'SALES'                         OVER
3100 AS 'COST'                          OVER
BAR                                     OVER
RECAP PROFIT/I5C = R1 - R2;             OVER
" "                                     OVER
RECAP EVAR(1)/I5C = E(3,1) - E(3,2);
AS 'EAST  VARIANCE'                     OVER
RECAP WVAR(3)/I5C = E(3,3) - E(3,4);
AS 'WEST  VARIANCE'
END

The output is:

                 E_ACTUAL   E_BUDGET   W_ACTUAL   W_BUDGET
                 --------   --------   --------   --------
SALES             6000.00    4934.00    7222.00    7056.00
COST              4650.00    3760.00    5697.00    5410.00
                ---------  ---------  ---------  ---------
PROFIT              1,350      1,174      1,525      1,646
                                                          
EAST  VARIANCE        176                                 
WEST  VARIANCE                             -121           


Example: Using NOPRINT, Field Reformatting, and COMPUTE With Column Notation

The following request has a field that is not printed, several reformatted fields and three calculated values. With SET CNOTATION=PRINTONLY, the column references result in correct output.

SET CNOTATION = PRINTONLY
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 NOPRINT CUR_YR
COMPUTE AMT2/D6 = AMOUNT *2;
LAST_YR/D5   AMOUNT NEXT_YR
COMPUTE AMT3/D6  = AMOUNT*3;
COMPUTE AMT4/D6  = AMOUNT*4;
FOR ACCOUNT
10$$ AS 'CASH'                                 OVER
1100 AS 'ACCTS. REC.'                          OVER
1200 AS 'INVENTORY'                            OVER
BAR                                            OVER
RECAP ATOT/I8C = R1 + R2 + R3; AS 'TOTAL'      OVER
RECAP DIFF(2,10,2)/D8  = ATOT(*) - ATOT(*-1);
END

The output is:

             CUR_YR     AMT2  LAST_YR  AMOUNT  NEXT_YR     AMT3     AMT4
             ------     ----  -------  ------  -------     ----     ----
CASH         21,239   42,478   17,195  21,239   25,991   63,717   84,956
ACCTS. REC.  18,829   37,658   15,954  18,829   21,941   56,487   75,316
INVENTORY    27,307   54,614   23,329  27,307   31,522   81,921  109,228
             ------  -------   ------  ------   ------  -------  -------
TOTAL        67,375  134,750   56,478  67,375   79,454  202,125  269,500
DIFF                  67,375           10,897           122,671         


Example: Using Column Notation With NOPRINT in a non-FML Request

The following request, sums TRANSTOT, QUANTITY, and TRANSCODE by TRANSDATE. TRANSTOT has the NOPRINT option, so it is not displayed on the report output. The request also calculates the following fields using COMPUTE commands:

SET CNOTATION = ALL
TABLE FILE VIDEOTRK
SUM TRANSTOT/D7.2 NOPRINT QUANTITY/D7.2 TRANSCODE
  COMPUTE TTOT2/D7.2 = C1;
  COMPUTE UNIT_COST1/D7.2 = C1/C2;
  COMPUTE UNIT_COST2/D7.2 = C1/QUANTITY;
BY TRANSDATE
END

With this request, only CNOTATION=EXPLICIT produces the correct output. The following discussion illustrates why the EXPLICIT setting is needed.

With CNOTATION=ALL, all fields in the internal matrix are assigned column numbers. In particular, the request creates the following column references:

UNIT_COST1 is C1/C2. These column numbers have both been assigned to TRANSTOT, so UNIT_COST1 always equals 1. UNIT_COST2 is C1 (TRANSTOT) divided by QUANTITY. The output is:

TRANSDATE  QUANTITY  TRANSCODE     TTOT2  UNIT_COST1  UNIT_COST2
---------  --------  ---------     -----  ----------  ----------
 91/06/17     12.00         10     57.03        1.00        4.75
 91/06/18      2.00          2     21.25        1.00       10.63
 91/06/19      5.00          4     38.17        1.00        7.63
 91/06/20      3.00          3     14.23        1.00        4.74
 91/06/21      7.00          6     44.72        1.00        6.39
 91/06/24     12.00          9    126.28        1.00       10.52
 91/06/25      8.00          7     47.74        1.00        5.97
 91/06/26      2.00          2     40.97        1.00       20.48
 91/06/27      9.00          7     60.24        1.00        6.69
 91/06/28      3.00          3     31.00        1.00       10.33

With CNOTATION = PRINTONLY, the field TRANSTOT, which has the NOPRINT option, is not assigned any column numbers. QUANTITY with its original format is not assigned a column number because it is not displayed on the report output. The reformatted QUANTITY field is displayed and is assigned a column number. Therefore, the request creates the following column references:

UNIT_COST1 is C1/C2, QUANTITY/TRANSCODE. UNIT_COST2 is C1 (QUANTITY) divided by QUANTITY. Therefore, UNIT_COST2 always equals 1. The output is:

TRANSDATE  QUANTITY  TRANSCODE     TTOT2  UNIT_COST1  UNIT_COST2
---------  --------  ---------     -----  ----------  ----------
 91/06/17     12.00         10     12.00        1.20        1.00
 91/06/18      2.00          2      2.00        1.00        1.00
 91/06/19      5.00          4      5.00        1.25        1.00
 91/06/20      3.00          3      3.00        1.00        1.00
 91/06/21      7.00          6      7.00        1.17        1.00
 91/06/24     12.00          9     12.00        1.33        1.00
 91/06/25      8.00          7      8.00        1.14        1.00
 91/06/26      2.00          2      2.00        1.00        1.00
 91/06/27      9.00          7      9.00        1.29        1.00
 91/06/28      3.00          3      3.00        1.00        1.00

With CNOTATION = EXPLICIT, the reformatted TRANSTOT field is explicitly referenced in the request, so it is assigned a column number even though it is not displayed. However, the TRANSTOT field with its original format is not assigned a column number. The QUANTITY field with its original format is not assigned a column number because it is not explicitly referenced in the request. The reformatted QUANTITY field is assigned a column number. Therefore, the request creates the following column references:

UNIT_COST1 is C1/C2, TRANSTOT/QUANTITY. UNIT_COST2 is C1 (TRANSTOT) divided by QUANTITY. Therefore, UNIT_COST2 always equals UNIT_COST1. The output is:

TRANSDATE  QUANTITY  TRANSCODE     TTOT2  UNIT_COST1  UNIT_COST2
---------  --------  ---------     -----  ----------  ----------
 91/06/17     12.00         10     57.03        4.75        4.75
 91/06/18      2.00          2     21.25       10.63       10.63
 91/06/19      5.00          4     38.17        7.63        7.63
 91/06/20      3.00          3     14.23        4.74        4.74
 91/06/21      7.00          6     44.72        6.39        6.39
 91/06/24     12.00          9    126.28       10.52       10.52
 91/06/25      8.00          7     47.74        5.97        5.97
 91/06/26      2.00          2     40.97       20.48       20.48
 91/06/27      9.00          7     60.24        6.69        6.69
 91/06/28      3.00          3     31.00       10.33       10.33


Example: Using Cell Notation in an FML Request

In the following request, CUR_YR has the NOPRINT option. The CHGCASH RECAP expression is supposed to subtract CUR_YR from LAST_YR and NEXT_YR.

SET CNOTATION = ALL
DEFINE FILE LEDGER
CUR_YR/I7C = AMOUNT;
LAST_YR/I5C = .87*CUR_YR - 142;
NEXT_YR/I5C = 1.13*CUR_YR + 222;
END
TABLE FILE LEDGER
SUM CUR_YR/I5C NOPRINT LAST_YR NEXT_YR
FOR ACCOUNT
10$$ AS 'TOTAL CASH ' LABEL TOTCASH OVER
" " OVER
RECAP CHGCASH(1,3)/I5SC=(TOTCASH(*) - TOTCASH(1));
  AS 'CHANGE FROM CURRENT'
END

When CNOTATION = ALL, C1 refers to the CUR_YR field with its original format, C2 refers to the reformatted value, C3 is LAST_YR, and C4 is NEXT_YR. Since there is an extra column and the RECAP only refers to columns 1 and 3, the calculation for NEXT_YR - CUR_YR is not performed. The output is:

                     LAST_YR  NEXT_YR
                     -------  -------
TOTAL CASH            17,195   25,991
 
CHANGE FROM CURRENT   -4,044

When CNOTATION = PRINTONLY, the CUR_YR field is not assigned any column number, so there is no column 3. Therefore, no calculations are performed. The output is:

                     LAST_YR  NEXT_YR
                     -------  -------
TOTAL CASH            17,195   25,991
 
CHANGE FROM CURRENT

When CNOTATION = EXPLICIT, the reformatted version of the CUR_YR field is C1 because it is referenced in the request even though it is not displayed. Both calculations are performed correctly. The output is:

                     LAST_YR  NEXT_YR
                     -------  -------
TOTAL CASH            17,195   25,991
 
CHANGE FROM CURRENT   -4,044    4,752


x
Reference: Usage Notes for Column Numbers

Information Builders