Usage codes

Usage codes define how column data should be used to produce reports and charts. If, for example, you do not want a column to be included at all, you use the OMIT usage code. If you select to show or print a column as it is, the usage code is blank.

Some usage codes let you decide how a column should be formatted. Others let you perform calculations. They are called aggregation usage codes.

You can, for example:

Add or perform another aggregate function on data in a column using the SUM or another aggregation usage code.
Provide summary data across the report using aggregation, GROUP, and ACROSS usage codes.

These usage codes are supported in the IBM Z® Decision Support built-in report generator:

ACROSS

Use the ACROSS usage code to display data horizontally for one of the columns in your report. When you use ACROSS for one column, you must use GROUP for one or more of the other columns. For the remaining columns, you can use the OMIT usage code. In that case, the summary line for each group value can contain several sets of results from the columns that use aggregations. There is one set for each group of values in the column that uses ACROSS.

A report can have only one column with the ACROSS usage code. If you specify more than one ACROSS column, you get an error message.

Example:

Sample report 1 uses the ACROSS usage code for the Department_name column. (See Figure 1 and Figure 2.)

MVS52, MVS Number of Jobs with Tape Mounts, Daily Trend, is another example of a report with an ACROSS usage code.

BREAKn

Use break usage codes to divide the information in your report into smaller sections, to make the report easier to read and understand. There are six levels of break codes (n=1-6) that provide columns for different levels of breaks. Any change in the value of the column causes a break; subtotals are displayed for columns whose usage is one of the aggregation usages.

You can use each break level only once in a report, and the break levels must come in sequence (that is, you can specify BREAK1 and BREAK2, but you cannot specify BREAK1 and BREAK3).

To be able to show breaks in a meaningful way in a report, the SQL query must contain an ORDER BY clause for the column or columns for which you specify BREAKn.

Example:

Sample Report 2 uses BREAK usage codes for the Month_start_date and Department_name columns. (See Figure 3.) MVS53, MVS Jobs Statistics by Period and User Group, Daily, is another example of a report with BREAK usage codes.

GROUP

Use the GROUP usage code to display one line of summary data for each set of values in the column. The summary line can display only values that are the same for each member of the group, such as the value in a control column or the results of columns whose usage is an aggregation code.

Note that blank usage codes cannot be used with GROUP usage codes.

To be able to present reports with GROUP usage codes in a meaningful way, the SQL query must use an ORDER BY clause for the column or columns for which you specify GROUP.

Example:

Sample report 1 uses the GROUP usage code for the Time column. (See Figure 1 and Figure 2.) MVS52, MVS Number of Jobs with Tape Mounts, Daily Trend, is another example of a report with a GROUP usage code.

blank

If no usage code is given, the column is displayed without any aggregation or summary. This usage code cannot be used with the GROUP usage code.

The following usage codes are aggregation usage codes:

SUM

The sum of the values in the column. The data in the column must be numeric.

AVERAGE

The average of the values in the column. The data in the column must be numeric.

MAXIMUM

The greatest value in the column. The data in the column must be numeric.

MINIMUM

The smallest value in the column. The data in the column must be numeric.

FIRST

The first value in the column. The data in the column can be numeric or character (edit code C) data.

LAST

The last value in the column. The data in the column can be numeric or character (edit code C) data.

COUNT

The number of values in the column. The data in the column can be numeric or character (edit code C) data.

TPCT

The percentage each value is of the column total. This aggregation usage code replaces the data value with a calculation. The data in the column must be numeric.

Example:

Sample Report 2 (Figure 3) is an example of a report that uses aggregation usage codes.