Identifying Data

In this section:

How to:

You can identify and format many categories of data in a report, including:

The following illustrates where the DATA and ACROSSVALUE components appear in a report, and which TYPE values you use to identify them. 

TABLE FILE CENTORD
HEADING CENTER
"UNITS SOLD IN 2002 BY PLANT"
SUM QUANTITY AND ROW-TOTAL AS '2002 TOTAL'
ACROSS QUARTER
BY PLANTLNG AS 'PLANT'
WHERE YEAR EQ 2002
ON TABLE COLUMN-TOTAL AS 'TOTAL UNITS'
ON TABLE SET PAGE-NUM OFF
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF,$
END

Note: Since this request simply illustrates where the components appear in a report, it omits a StyleSheet.

Syntax: How to Identify All Data

To identify all report data in a StyleSheet (except totals, grand totals, subtotals, and horizontal sort (ACROSS) values, which need to be identified separately) use this attribute and value: 

TYPE = DATA

Example: Identifying All Data in a Report

The following illustrates how to identify all of the data in a report. The relevant StyleSheet declaration is highlighted in the request. 

TABLE FILE CENTORD
HEADING CENTER
"UNITS SOLD IN 2002 BY PLANT"
SUM QUANTITY AND ROW-TOTAL AS '2002 TOTAL'
ACROSS QUARTER
BY PLANTLNG AS 'PLANT'
WHERE YEAR EQ 2002
ON TABLE COLUMN-TOTAL AS 'TOTAL UNITS'
ON TABLE SET PAGE-NUM OFF
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, $ 
TYPE=DATA, STYLE=BOLD, $ 
ENDSTYLE
END

The output is:

Syntax: How to Identify a Column of Data 

TYPE=DATA, COLUMN=column

where:

column

Specifies one or more columns that you wish to format. For a list of values, see Identify an Entire Column.

Example: Identifying a Column of Data

The following illustrates how to identify a column of data. The relevant StyleSheet declaration is highlighted in the request.

Note that when identifying a column using Nn, NOPRINT columns are counted. Even though the Product Name field is the first column in this report, it is identified with N2 because of the NOPRINT column. 

TABLE FILE CENTORD
PRINT QUANTITY LINEPRICE LINE_COGS
BY ORDER_NUM NOPRINT 
BY PRODNAME
WHERE ORDER_NUM EQ '48045'
ON TABLE SET PAGE-NUM OFF
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF,$ 
TYPE=DATA, COLUMN=N2, STYLE=ITALIC,$ 
ENDSTYLE
END

The output is:

Syntax: How to Identify a Row of Horizontal Sort (ACROSS) Data 

TYPE=ACROSSVALUE, [ACROSS={fieldname|{N|A}n}]

where:

ACROSS

If you have a request with multiple ACROSS fields, you can identify each field using the ACROSS identifier. You only need to include the ACROSS identifier if you have multiple ACROSS fields in your request.

fieldname

Specifies a horizontal sort row by its field name.

n

Specifies a horizontal sort row by its position in the sequence of horizontal sort rows. Cannot be combined with a field name specification in the same StyleSheet.

Example: Identifying a Row of Horizontal Sort (ACROSS) Data

The following illustrates how to identify a row of horizontal sort data. The relevant StyleSheet declaration is highlighted in the request. 

TABLE FILE CENTORD
HEADING
"Units Sold"
SUM QUANTITY
BY PRODNAME
ACROSS PLANT AS 'Manufacturing Plant'
WHERE PRODTYPE EQ 'Digital'
ON TABLE SET PAGE-NUM OFF
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, $
TYPE=HEADING, SIZE=12, $ 
TYPE=ACROSSVALUE, ACROSS=PLANT, STYLE=BOLD, $ 
ENDSTYLE
END

The output is:

Note: To produce the same results you can alternatively use the value N1 for the ACROSS attribute in the StyleSheet declaration. For example, TYPE=ACROSSVALUE, ACROSS=N1, STYLE=BOLD, $.

Example: Identifying Row Totals (ACROSS-TOTAL) for Horizontal Sort Data

The following illustrates how to identify a row total (ACROSS-TOTAL) for horizontal sort (ACROSS) data using the ACROSSVALUE component and a numeric column reference (Nn). The relevant StyleSheet declaration is highlighted in the request. 

TABLE FILE CENTORD
SUM QUANTITY
BY PRODNAME
ACROSS PLANT AS 'Manufacturing Plant'
ACROSS-TOTAL AS 'Plant Totals'
WHERE PRODTYPE EQ 'Digital'
ON TABLE SET PAGE-NUM OFF
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, $ 
TYPE=ACROSSVALUE, COLUMN=N8, STYLE=ITALIC, COLOR='RED', $ 
ENDSTYLE
END

The following image shows the output with the ACROSS-TOTAL value, Plant Totals, styled in red italics.

Identifying Totals and Subtotals

How to:

Within a StyleSheet, you can identify the grand totals, subtotals, subtotal calculations (generated by ON sortfield RECAP or ON sortfield COMPUTE), column totals, and row totals of a report in order to format them. For details on identifying row totals, see Identifying an Entire Report, Column, or Row.

The following example illustrates where these components are in a report, and which TYPE values you use to identify them. 

TABLE FILE EMPLOYEE
SUM DED_AMT AND GROSS
BY DEPARTMENT BY PAY_DATE
ON DEPARTMENT RECAP DEPT_NET/D8.2M = GROSS-DED_AMT;
WHEN PAY_DATE GT 820101
ON DEPARTMENT SUBTOTAL
END

The following figure shows each component:

Note: Since this request simply illustrates how to identify different types of totals and subtotals, it omits a StyleSheet.

Syntax: How to Identify a Grand Total, Subtotal, or Subtotal Calculation 

TYPE=type, [BY=sortfield] [coltype=column]

where:

type

Identifies a subtotal or total. Select from:

GRANDTOTAL which is a grand total (generated by COLUMN-TOTAL, SUBTOTAL, SUB-TOTAL, RECOMPUTE, or SUMMARIZE). See Identifying a Grand Total for an example.

SUBTOTAL which is a subtotal (generated by SUBTOTAL, SUB-TOTAL, RECOMPUTE, or SUMMARIZE). See Identifying Subtotals for an example.

RECAP which is a subtotal calculation (generated by ON sortfield RECAP or ON sortfield COMPUTE). See Identifying a Subtotal Calculation (RECAP/COMPUTE) for an example.

BY

If you have a request with multiple BY fields, and two or more have subtotal commands associated with them, you can identify each field using the BY identifier. This is helpful when you want to format each subtotal differently or when you want to format only one subtotal.

You only need to include the BY identifier if you have multiple BY fields in your request.

sortfield

Specifies the BY field associated with several subtotal commands of a report. Use the fieldname for the value (BY=fieldname).

coltype

Identifies a specific column to apply formatting. When you include the COLUMN or ACROSSCOLUMN identifier in your declaration, only the subtotal values receive the formatting, the labeling text will not. Values can be:

COLUMN which is a display column (generated by PRINT, LIST, SUM, or COUNT) or a computed column (generated by COMPUTE).

ACROSSCOLUMN where every instance of a display or computed column is repeated across a horizontal sort (ACROSS) row.

If there are several columns being totaled or subtotaled by one command, and you do not specify a column in the StyleSheet, the formatting will be applied to the totals or subtotals for all of the columns. It will also be applied to the labeling text for the total and subtotal values.

column

Specifies the column whose totals or subtotals you wish to format. For a list of values, see Identify an Entire Column.

Example: Identifying a Grand Total

The following illustrates how to identify a grand total in a report request. In this example, we only want to format the grand total value for the LINE_COGS field, so the COLUMN attribute is included in the StyleSheet declaration. The grand total in this request is generated by COLUMN-TOTAL. The relevant StyleSheet declaration is highlighted in the request.

Note:

  • To style the entire grand total row, remove the COLUMN attribute from the StyleSheet declaration.
  • To produce the same results you can alternatively use the values N5, P5, or C3 for the COLUMN attribute in the StyleSheet declaration. 
TABLE FILE CENTORD
SUM QUANTITY LINEPRICE LINE_COGS AND COLUMN-TOTAL
BY ORDER_NUM BY PRODNAME
WHERE ORDER_NUM EQ '48053' OR '48798'
ON TABLE SET PAGE-NUM OFF
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF,$ 
TYPE=GRANDTOTAL, COLUMN=LINE_COGS, STYLE=BOLD, SIZE=11,$ 
ENDSTYLE
END

The output is:

Example: Identifying Subtotals

The following illustrates how to identify subtotals in a report request. In this example, only subtotal values in the QUANTITY and LINE_COGS fields are formatted, so the COLUMN attribute is included in the StyleSheet declarations.

Also, since there are two SUBTOTAL commands associated with two of the three BY fields (PLANT and ORDER_NO), the BY attribute is also included in each declaration to ensure the formatting is applied to the correct value. The relevant StyleSheet declarations are highlighted in the request.

Note:

  • To style an entire subtotal row, remove the COLUMN and BY attributes from the StyleSheet declaration.
  • To produce the same results you can, alternatively, use the values COLUMN=N6, COLUMN=P6, or COLUMN=C3 for the COLUMN=LINE_COGS attribute.
  • To produce the same results you can, alternatively, use the values COLUMN=N4, COLUMN=P4, or COLUMN=C1 for the COLUMN=QUANTITY attribute. 
TABLE FILE CENTORD
SUM QUANTITY LINEPRICE LINE_COGS AS 'Line Cost of, Goods Sold'
BY PLANT
BY ORDER_NUM
BY PRODNAME
ON PLANT SUBTOTAL
ON ORDER_NUM SUBTOTAL
WHERE ORDER_NUM EQ '35774' OR '48041'
WHERE PLANT EQ 'BOS'
ON TABLE SET PAGE-NUM OFF
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF,$ 
TYPE=SUBTOTAL, BY=PLANT, COLUMN=LINE_COGS, STYLE=BOLD+ITALIC,
    COLOR=BLUE,$
TYPE=SUBTOTAL, BY=ORDER_NUM, COLUMN=QUANTITY, STYLE=BOLD, SIZE=11,$ 
ENDSTYLE
END

The output is:

Example: Identifying a Subtotal Calculation (RECAP/COMPUTE)

The following illustrates how to identify a subtotal calculation created with a RECAP or COMPUTE phrase. In this example, the subtotal calculation is generated with ON PLANT RECAP QTY/F6=QUANTITY. The relevant StyleSheet declaration is highlighted in the request.

Note: If there is more than one RECAP or COMPUTE field in your request, you can distinguish them by adding BY=fieldname to the StyleSheet declaration. 

TABLE FILE CENTORD
SUM QUANTITY LINEPRICE LINE_COGS AS 'Line Cost of, Goods Sold'
BY PLANT BY ORDER_NUM
ON PLANT RECAP QTY/F6=QUANTITY;
WHERE PLANT EQ 'BOS'
WHERE ORDER_NUM LT '56098'
ON TABLE SET PAGE-NUM OFF
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, $ 
TYPE=RECAP, STYLE=BOLD+ITALIC, $ 
ENDSTYLE
END

The output is: