REPORT

The REPORT command produces a formatted report for one or more data expressions. REPORT automatically loops over the dimensions of the expression. REPORT sends the output to the current outfile.

Syntax

REPORT [NOHEAD] [GRANDTOTALS] [[SUBTOTALS] {GROUP group-dimension}...] -

[[SUBTOTALS] [attributes] DOWN down-dimension] -

[[ROWTOTALS] { ACROSS across- dimension [limit-clause]: }... ] -

[SUBTOTALS] [attributes] expression(s)

Arguments

When you specify only the expression argument, REPORT produces a report with the layout described in "Default Layout". When you specify some but not all of the dimensions of an expression in GROUP, DOWN, or ACROSS phrases, REPORT follows the default layout as closely as possible with the unspecified dimensions.

NOHEAD

Specifies that the report should contain no initial blank line and no headings. NOHEAD overrides any HEADING arguments you specify for the attributes argument, as well as suppressing all headings that the REPORT command normally generates automatically.

GRANDTOTALS

Includes a grand total for each numeric column at the end of your report.

SUBTOTALS

Includes subtotals for numeric columns. A row of dashes precedes each row of subtotals. When you specify SUBTOTALS for an expression or DOWN phrase, you get subtotals for each GROUP dimension (or composite). When you specify SUBTOTALS for a GROUP phrase, you get subtotals for the specified dimension and for any slower-varying GROUP dimensions. The subtotals for a group appear at the bottom of the last slice in the group.

GROUP

Produces a separate group, or two-dimensional slice, of the data for each value of group-dimension. You can use the GROUP phrase more than once to specify more than one GROUP dimension (or composite). In this case, you produce a separate slice for each combination of the values of the GROUP dimensions.

group-dimension

The name of a dimension or composite from which to retrieve the values to use as group labels, or one or more TEXT expressions that are the actual values you want to use as group labels:

When group-dimension is the name of a composite or a conjoint dimension, Oracle OLAP creates a separate group for each base dimension.
When group-dimension is a TEXT expression, the expression must be dimensioned only by the desired GROUP dimension, and each value of the expression should be descriptive text that corresponds to its associated dimension value.

The dimensions that you specify in a GROUP phrase are not required to be relevant to the data they loop over. See "Specifying Extra Dimensions".

DOWN

Specifies that the report includes row labels (that is, one or more columns on the left side of your report that label the other values). You can have only one DOWN phrase.

down-dimension

The name of a dimension or composite from which to retrieve the values to use as row labels, or one or more TEXT expressions that are the actual values you want to use as row labels.

When down-dimension is the name of a non-conjoint dimension, REPORT produces only one column of row labels.
When down-dimension is the name of a composite or a conjoint dimension, Oracle OLAP creates a separate column for each base dimension.
When down-dimension is a TEXT expression, the expression must be dimensioned only by the desired DOWN dimension, and each value of the expression must be descriptive text that corresponds to its associated dimension value.

The dimensions that you specify in a DOWN phrase are not required to be relevant to the data they loop over. See "Specifying Extra Dimensions".

ROWTOTALS

Includes a column headed "TOTAL" at the right side of the report with a total for each numeric row. Including a row total in your report does not imply either column subtotals or a grand total.

ACROSS

Produces a row of column headings across the top of your report, one for each value in dimension. Under each heading, REPORT produces a column of data for the data expression you specify. You can have more than one ACROSS phrase (or composite) in the report.

across-dimension

The name of a dimension or composite from which to retrieve the values to use as column headings, or one or more TEXT expressions that are the actual values you want to use as column headings.

When across-dimension is the name of a composite or a conjoint dimension, Oracle OLAP creates a separate heading column for each base dimension.
When across-dimension is a TEXT expression, the expression must be dimensioned only by the desired across-dimension, and each value of the expression should be descriptive text that corresponds to its associated dimension value. For information on providing formatted labels for a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR, see "Formatting DAY, WEEK, MONTH, QUARTER, and YEAR Dimension Values".

The dimensions that you specify in an ACROSS phrase are not required to be relevant to the data they loop over. See "Specifying Extra Dimensions".

limit-clause

When you specify a dimension in the ACROSS phrase, the temporary status of that dimension during the execution of the execution of the REPORT statement. (You cannot include a limit-clause argument when you specify a composite in the ACROSS phrase.)

The format of limit-clause is as follows.

To specify the temporary status, insert any of the LIMIT keywords (the default is TO) along with an appropriate value list or related-dimension list. You can use any valid LIMIT clause in valuelist (see the entry for the LIMIT command for further information).

When the limits you specify result in an empty status for the dimension, an error occurs (regardless of the setting of the OKNULLSTATUS option). However, when you include the phrase IFNONE label, the error is suppressed and execution of your program branches to the specified label, where you can handle the error.

attributes

One or more format attributes from Table 20-5, "Formal Attributes for Data Values in a Report" that specify how to format the data. For information on providing formatted labels for a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR, see "Formatting DAY, WEEK, MONTH, QUARTER, and YEAR Dimension Values".

Tip:

When a variable has a formatting property attached to its definition, you can use the OBJ function to obtain the value of that property and use it as the value of an attribute in a REPORT statement.

When you do not specify any attributes, Oracle OLAP formats the data values using the default format described in "Default Layout". In this case, Oracle OLAP automatically determines the width of the columns, the number of decimal places, whether commas are used to mark thousands in numeric values, and so on.

Table 20-5 Formal Attributes for Data Values in a Report

Attribute	Meaning
HEADING 'text'	Specifies text to use in place of default column headings. When you use the HEADING attribute, the position of the heading you specify will vary depending on how many expressions it must span in your report. This means that your heading may or may not replace a default heading. Also, when you use the HEADING attribute to specify a column title that is wider than the column width, the text of the title will wrap within the width of its column.
WIDTH n	(Abbreviated: W.) Makes the column n spaces wide. The maximum width is 4000 characters. Columns with a width of 0 (zero) are suppressed.(See also: "Default Values for the Width Attribute".)
TRUNCATE	(Abbreviated: TRUNC.) Truncates a character value to the column width when it does not fit in the column.
NOTRUNCATE	(Abbreviated: NOTRUNC) Creates additional lines when the character value does not fit in the column. (Default.)
CENTER	(Abbreviated: C.) Centers the value within its column.
LEFT	(Abbreviated: L.) Left-justifies the value within its column. (Default for text data.)
RIGHT	(Abbreviated: R.) Right-justifies the value within its column. (Default for numeric and Boolean data.)
SPACE n	(Abbreviated: SP.) Precedes the column with the specified number of spaces. (By default, REPORT precedes the first column by 0 spaces, and other columns by 1 space.)
FILL 'char'	Puts char into unused positions in the column. (The default fill character is a space.)
COMMA	Marks thousands and millions with commas or the character currently recorded in the THOUSANDSCHAR option. Overrides the setting of the COMMAS option.
NOCOMMA	Does not marks thousands and millions. Overrides the setting of the COMMAS option.
PAREN	Uses parentheses to indicate negative numbers. Overrides the setting of the PARENS option
NOPAREN	Uses the minus sign to indicate negative numbers. Overrides the setting of the PARENS option.
DECIMAL n	(Abbreviated: D.) Shows n decimal places. Decimal places are separated using the character currently recorded in the DECIMALCHAR option. Overrides the value of the DECIMALS option. Note: REPORT suppresses decimal places in row and column totals of integer data unless you specify the DECIMAL attribute for the totaled expression. Additionally, when you set the DECIMAL attribute to `0` and you use the NOLEADINGZERO keyword, any decimal values between `-1` and `1` that are rounded to `0` will not be shown.
MDECIMAL n	Shows n decimal places in numbers formatted with M-notation; n can be any number from 0 to 16, or 255.
NODECIMAL	Shows the number of decimal places specified by the setting of the DECIMALS option. (Default.)
EDECIMAL n	Shows n decimal places in numbers formatted with E-notation; n can be any number from 0 to 16, or 255.
ENOTATION	Always uses scientific notation, also called exponential notation or E-notation (appends "E", and includes a sign before the exponent, for example, `.230E+2` or `.230E-2`).
NOENOTATION	Does not use E-notation; instead, uses to conditional M-notation.
CENOTATION	Conditionally uses E-notation, when needed to make a value fit in a column.
MNOTATION	Always uses M-notation (divides values by one million and appends "M").
NOMNOTATION	Does not use M-notation; instead, uses asterisks for oversize values.
CMNOTATION	Conditionally uses M-notation, when needed to make a value fit in a column. (Default.)
FOLDDOWN	For a multiline character value, places the first line on the row with the other values, and places additional lines below the rest of the row; also strips any leading or trailing spaces. (Default.)
FOLDUP	For a multiline character value, places all but the last line above the rest of the row, and the last line on the row with the other values; also strips any leading or trailing spaces.
INDENT n	Indents the value n spaces within its column. The default is 0.
LEADINGZERO	Puts a leading zero before decimal numbers between -1 and 1.
NOLEADINGZERO	Suppresses leading zeros before decimal numbers between -1 and 1.
CNLEADINGZERO	Puts a leading zero before decimal numbers between -1 and 1 when it does not cut off any significant digits. (Default.)
LSET 'text'	Adds text to the left of the value. When you use LSET with an expression that contains `NA` values, the text you specify with LSET is not included to left of any `NA` value
NOLSET	Does not add anything to the left of the value. (Default.)
RSET 'text'	Adds text to the right of the value. When you use RSET with an expression that contains `NA` values, the text you specify with RSET is not included to right of any `NA` value.
NORSET	Does not add anything to the right of the value. (Default.)
NASPELL 'text'	Uses text in place of `NA` values. Overrides the setting of the NASPELL option.
NONASPELL	Spells `NA` values as indicated by the NASPELL option. (Default.)
YESSPELL 'text'	Uses text for `TRUE` Boolean values. The default is recorded in the YESSPELL option.
NOSPELL 'text'	Uses text for `FALSE` Boolean values. Overrides the setting of the NOSPELL option.
ZSPELL 'text'	Uses text in place of zero numeric values. Overrides the setting of the ZSPELL option.
NOZSPELL	Spells zero numeric values as specified by the ZSPELL option. (Default.)
OVER textexp	Overlines the value with the value of a character expression (textexp). When textexp is a literal value, it must be enclosed in single quotes. Useful literal values include: `'-'` to overline value or column, `'='` to double overline value or column, and `''` to indicate that a value or column is does not have an overline. To overline only when a condition is met, for textexp use: `IF` `boolean-expression` `THEN '-' ELSE ''`
UNDER textexp	Underlines the value with the value of a character expression (textexp). When textexp is a literal value, it must be enclosed in single quotes. Useful literal values include: `'-'` to underline value or column, `'='` to double underline value or column, and `''` to indicate that a value or column is not underlined. To underline only when a condition is met, for textexp use: `IF` `boolean-expression` `THEN '-' ELSE ''`
VALONLY	Used with the UNDER and OVER attributes, underline or overline the value only.
NOVALONLY	Used with the UNDER and OVER attributes, underlines or overline the entire width of the column. (Default.)

expression . . .

The data to be shown in the report. The way the data looks depends on its data type and the attributes you specify.

Note:

The REPORT command is not equipped to deal with NTEXT values. Do not include them in any part of a report.

You can specify more than one expression; the expressions do not have to have the same dimensions:

When you have several data expressions in your REPORT statement, you can specify different format attributes before each. When you want attributes to apply to two or more data expressions, enclose the expressions in angle brackets (< >).

attributes <expression1, expression2>
Attributes outside the brackets apply to all the expressions within the brackets. However, you can also specify attributes for only one of the expressions (even an attribute that contradicts one that applies to the group) within the brackets by including them immediately before the expression.

attributes0 <attributes1 expression1, expression2>

In this case, attributes0 applies to both expression1 and expression2; while attributes1 only applies to expression1.

Tip:

To create running totals, use the RUNTOTAL function.

Notes

Report Options

A number of options effect reports created using the OLAP DML. These options are listed in Table A-14, "Report Options".

Default Layout

When you do not specify any of the layout phrases (GROUP, DOWN, or ACROSS), REPORT tries to format its output compactly. Normally, this means a two-dimensional report of the data with one of the dimensions down the side and the other across the top, much like a spreadsheet. Any additional dimensions of the data form "slices" or separate two-dimensional segments, like a series of spreadsheets. By default, REPORT uses the following rules to determine the layout:

The fastest-varying dimension in an expression (the one that appears first in the definition of that expression) goes across, the next fastest goes down, and any remaining dimensions become GROUP slices.
The order of dimensions in a list of two or more expressions is a simple combination of the dimensions that appear in the definitions of the component expressions. The original order is preserved as far as possible, subject to the rule that repeated mentions of the same dimension are dropped. For example, the dimensions of the combined variables price and industry.sales, where price has the dimensions <month product> and industry.sales has the dimensions <quarter product region>, are <month product quarter region>.

When you produce a report of data for a variable dimensioned by a composite, REPORT automatically breaks out the data by the base dimensions of the composite that is used in the definition of the variable. When a particular combination of base dimension values does not exist in the composite, the report shows NA for the corresponding data cell. See Example 20-32, "Reporting Data Dimensioned by Composites".

Default Values for the Width Attribute

When you omit the WIDTH attribute for an ACROSS phrase, the default width is the value of the COLWIDTH option (default is 10).

When you omit the WIDTH attribute for a DOWN phrase or when you specify a simple dimension in the DOWN phrase, the default width is the value of the LCOLWIDTH option (default is 14).

When you omit the WIDTH attribute for a DOWN phrase that specifies a conjoint dimension or a composite, the default label width is the width of the COLWIDTH option and there is a separate column for each base dimension. You can provide a different width for each base dimension column by using the KEY function. You can produce a label column for each base dimension with the KEY function and use a separate WIDTH attribute for each column. For example, assume that proddist is a composite with the base dimensions product and district. In this case, you can use a statement similar to the following one.

REPORT DOWN < W 8 KEY(proddist, product) -
   W 12 KEY(proddist, district) > . . .

When you use the default line width of 80 characters (determined by the LSIZE option) and the default column width settings (with a single label column of 14 characters) a line of output can accommodate the labels column and six data columns.

The combined width of all the columns of a report cannot be greater than 4000 characters.

When a numeric value is too large to fit into a data column, REPORT rounds it off to the nearest million with the symbol M at the right side of the cell. When a value is still too large, REPORT replaces the value with a series of asterisks.

Formatting DAY, WEEK, MONTH, QUARTER, and YEAR Dimension Values

When you use a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR as the dimension in an ACROSS, DOWN, or GROUP phrase, you can use the CONVERT function to override the dimension's VNF (or the default VNF) and provide your own format for the dimension value names. To override the VNF, use the CONVERT function with a vnf argument in place of the dimension argument to the ACROSS, DOWN, or GROUP keyword. For example, in a report of units data, you can format the labels for the month dimension by using the following statement.

REPORT HEADING 'Month' DOWN -
   CONVERT(month TEXT '<mtextl> <yyyy>') units

Specifying Extra Dimensions

The REPORT command uses whatever dimensions you specify in laying out the report, regardless of whether the expressions to be shown are dimensioned by these dimensions. When an expression is not dimensioned by one or more of the dimensions specified, the values of that expression are repeated for each value of the extra dimension. This fact is sometimes useful for comparisons. SeeExample 20-31, "Repeating Price Data".

Performance Tip for Reporting Variables Dimensioned by Composites

By default, when REPORT explicitly loops over a composite, it sorts the composite values according to the current order of the values in the composite's base dimensions. The task of sorting requires some processing time, so when variables are large, performance can be affected. When your variable is very large, and you are more concerned about performance than about the order in which REPORT output is produced, you can set the SORTCOMPOSITE option to NO.

Examples

Example 20-28 Creating a Default Report

This example shows how to look at product prices for the first three months of 1996. You can use REPORT in its simplest form, without changing the default layout

LIMIT month TO 'Jan96' TO 'Mar96'
REPORT price