REPORT

The REPORT command quickly produces output for one or more data expressions. REPORT automatically loops over the dimensions of the data and formats the output. Using this default layout, you can produce an attractive report with a single short command. In addition, you can use REPORT command options to modify the default format and produce a custom report. Output from the REPORT command is sent to the current outfile.

To produce a default report, use this simple form.

REPORT expression . . .

You can also customize the layout that REPORT produces by using various options. REPORT has an underlying format similar to ROW, and all of the options available with the ROW command are available with REPORT.

Syntax

REPORT [NOHEAD] [GRANDTOTALS] [[SUBTOTALS] GROUP dimension] - 

     [[SUBTOTALS] [attributes] DOWN dimension] -

     [[ROWTOTALS] ACROSS dimension [limit-clause]:] -

     [SUBTOTALS] [attributes] expression(s)

Arguments

NOHEAD

Specifies that the report should contain no initial blank line and no headings. NOHEAD must be the first argument to the REPORT command. It overrides any HEADING arguments you specify in the command, as well as suppressing all headings that the REPORT command normally generates automatically. The NOHEAD keyword is useful for creating files that do not contain data or headings.

GRANDTOTALS

Includes a grand total for each numeric column at the end of your report. Unless you include NOHEAD, GRANDTOTALS must be the first argument to the REPORT command. When you include NOHEAD, GRANDTOTALS must be the second argument to the REPORT command.

SUBTOTALS

Includes subtotals for numeric columns. A row of dashes precedes each row of subtotals. You can get subtotals for a specific set of data by specifying the keyword SUBTOTALS before the GROUP or DOWN keyword or before a data expression.

SUBTOTALS GROUP dimension
SUBTOTALS DOWN dimension
SUBTOTALS expression

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 dimension

Produces a separate group, or two-dimensional slice, of the data for each value of dimension. You can use the GROUP keyword 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. Any GROUP phrases must be specified before any DOWN or ACROSS phrases.

In place of dimension, you can specify a text expression in order to provide formatted labels. 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. 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".

DOWN dimension

Produces a column of dimension values labeling the rows down the left side of your report. The default width of the label column is controlled by the LCOLWIDTH option, which has a default value of 14 characters. When the DOWN phrase specifies a composite or a conjoint dimension, Oracle OLAP creates a separate column for each base dimension. The default width of the base dimension columns is controlled by the COLWIDTH option, which has a default value of 10 characters. You can override the default of any label column by using the WIDTH attribute in REPORT (see Table 20-5, "Format Attributes for Data Values"). You can have only one DOWN phrase. Any DOWN phrase must be specified before any ACROSS phrase.

When the DOWN dimension is a composite or a conjoint 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) > . . .

In place of dimension you can specify a text expression in order to provide formatted labels. The expression must be dimensioned only by the desired DOWN dimension, and each value of the expression should be descriptive text that corresponds to its associated dimension value. You can also use two or more text expressions (each dimensioned only by the DOWN dimension) as the dimension argument by placing them in angle brackets.

REPORT DOWN < expression1 expression2 > . . .

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". For information on suppressing a label column, see "Suppressing a Column".

ROWTOTALS

Includes a column headed "TOTAL" at the right side of the report with a total for each numeric row. You must specify ROWTOTALS before the ACROSS keyword. Including a row total in your report does not imply either column subtotals or a grand total. The keyword SUBTOTALS before an ACROSS phrase produces subtotals for any DOWN and GROUP dimensions that are specified.

ACROSS dimension [limit-clause]:

Produces a row of 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 dimension (or composite) in the report, each followed by a colon.

You can nest different ACROSS dimensions for one data expression, as illustrated in the following statement.

ACROSS district: ACROSS product: units

In place of dimension you can specify a text expression in order to provide formatted labels. 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".

When you specify a composite in the ACROSS phrase, you cannot include a limit-clause argument. You must limit the base dimensions of a composite to the desired values before you execute a REPORT command.

However, when you specify a dimension in the ACROSS phrase, limit-clause enables you to change the status of that dimension. The new status will be in effect only for the duration of the REPORT command. The format of limit-clause is as follows.

[ADD|COMPLEMENT|KEEP|REMOVE|INSERT|TO] valuelist [IFNONE label]

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). The following example temporarily limits month to the last six values, no matter what the current status of month is.

ACROSS month LAST 6: units

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, "Format Attributes for Data Values" that specify how to format the data. A group of attributes can apply to one or more data expressions. See "Attributes for Formatting Data".

The purpose of negative attributes (such as NOPAREN) is to override options or attributes that are more globally set. The negative attributes have an asterisk (*) before their names in Table 20-5, "Format Attributes for Data Values" .

expression . . .

The data to be shown in the report. The way the data looks depends on its data type and the attributes you specify. You can specify more than one expression; the expressions do not have to have the same dimensions.

When you specify only one data expression, REPORT produces one data column for each heading for the ACROSS dimension. However, when you want REPORT to show two or more expressions under each heading, enclose the expressions in angle brackets (< >).

ACROSS dimension: <expression1, expression2>

Table 20-5 Format Attributes for Data Values

Attribute	Meaning
HEADING 'text'	Specifies text to use in place of default column headings.
WIDTH n (W n)	Makes the column n spaces wide. The default width for the first column is the value of the LCOLWIDTH option. For other columns, it is the value of the COLWIDTH option. The maximum width is 4000 characters. Columns with a width of 0 (zero) are suppressed.
SPACE n (SP n)	Precedes the column with n spaces. The default for the first column is 0; for other columns, 1.
INDENT n	Indents the value n spaces within its column. The default is 0.
LEFT (L)	Left-justifies the value within its column. This is the default for TEXT data.
RIGHT (R)	Right-justifies the value within its column. This is the default for numeric and Boolean data.
CENTER (C)	Centers the value within its column.
LSET 'text'	Adds text to the left of the value.
*NOLSET	Does not add anything to the left of the value. (Default
RSET 'text'	Adds text to the right of the value.
*NORSET	Does not add anything to the right of the value. (Default)
FILL 'char'	Puts char into unused positions in the column. The default fill character is a space.
DECIMAL n (D n)	Shows n decimal places. Decimal places are separated by the character currently recorded in the DECIMALCHAR option. The default number of decimal places is controlled by the DECIMALS option.
*NODECIMAL	Shows the number of decimal places indicated by the DECIMALS option. (Default)
COMMA	Marks thousands and millions with commas or the character currently recorded in the THOUSANDSCHAR option. The default is controlled by the COMMAS option.
*NOCOMMA	Does not mark thousands and millions.
PAREN	Uses parentheses to indicate negative numbers. The default is controlled by the PARENS option.
*NOPAREN	Uses the minus sign to indicate negative numbers. The default is controlled by the PARENS option.
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.
MNOTATION	Always uses M-notation (divides values by one million and appends "M").
CMNOTATION	Conditionally uses M-notation, when needed to make a value fit in a column.
*NOMNOTATION	Does not use M-notation (uses asterisks for oversize values).
MDECIMAL n	Shows n decimal places in numbers formatted with M-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).
CENOTATION	Conditionally uses E-notation, when needed to make a value fit in a column.
*NOENOTATION	Does not use E-notation (defaults to conditional M-notation).
EDECIMAL n	Shows n decimal places in numbers formatted with E-notation; n can be any number from 0 to 16, or 255.
NASPELL 'text'	Uses text in place of NA values. The default is controlled by the NASPELL option.
*NONASPELL	Spells NA values as indicated by the NASPELL option.
ZSPELL 'text'	Uses text in place of zero numeric values. The default is controlled by the ZSPELL option.
*NOZSPELL	Spells zero values as indicated by the ZSPELL option.
YESSPELL 'text'	Text used for TRUE Boolean values. The default is recorded in the YESSPELL option.
NOSPELL 'text'	Text used for FALSE Boolean values. The default is recorded in the NOSPELL option.
TRUNCATE (TRUNC)	Truncates a character value to the column width when it does not fit in the column.
*NOTRUNCATE (NOTRUNC)	Creates additional lines when the character value does not fit in the column.
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.
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.
VALONLY	Underlines or overlines the value only. (Used with UNDER and OVER.)
NOVALONLY	Underlines or overlines the entire width of the column. (Used with UNDER and OVER.)
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 ''
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 ''

Notes

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

In determining the layout of its output, REPORT follows any layout keywords (GROUP, DOWN, or ACROSS) that you specify in the statement. By default, 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.

When no layout keywords are specified, 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".

The layout keywords, GROUP, DOWN, and ACROSS, allow you to alter the default layout by changing the way in which the data's dimensions are arranged down and across the report.

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. (See "Default Layout".) When you want a different layout, the easiest way to get it is to specify exactly what you want with the GROUP, DOWN, and ACROSS keywords.

When one of the dimensions has just one value in the status, you should specify it in a GROUP phrase for a more pleasing layout.

When you specify a composite in a GROUP, DOWN, or ACROSS phrase, you can override the default format of REPORT and break out the data by its composite. In this case, when a particular combination of base dimension values does not exist in the composite, the report does not include that combination. See Example 20-32, "Reporting Data Dimensioned by Composites".

The dimensions that you specify in GROUP, DOWN, and ACROSS phrases are not required to be relevant to the data they loop over. See "Specifying Extra Dimensions".

You can specify an unnamed composite as the dimension argument by using the syntax that was used to create the unnamed composite.

You can use attributes to change the way the data is formatted. Attributes can apply to one data expression or a group of expressions, depending on where you specify them.

• When you do not specify any attributes, the default format is used for the data values in a report. 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. However, by including format attributes prior to the data expression in your REPORT command, you can change the way in which the values are formatted.

  attributes expression

• When you have several data expressions in your REPORT command, 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.

• You can specify attributes before an ACROSS phrase. Those attributes apply to the data values within the scope of that particular ACROSS phrase.

  attribs1 ACROSS dimen1: [attribs2 ACROSS dimen2:]

• You can format the data in the labels column by specifying attributes before the DOWN phrase.

  attributes DOWN dimension

Table 20-5 shows attributes you can use to format values. Some attributes have a corresponding option (indicated in Table 20-5) that you can use to control the default.

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 the REPORT command.

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.

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.

The ZEROROW option controls whether rows of all-zero data are included in a report.

The REPORT command suppresses decimal places in row and column totals of INTEGER data unless you specify the DECIMAL attribute for the totaled expression.

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.

The maximum width of a line in a report is 4000 characters.

For data columns (ACROSS), the default width is the value of the COLWIDTH option (default is 10). The default width for the label column (DOWN) is the value of the LCOLWIDTH option (default is 14). This default is used when you omit the DOWN phrase or when you specify a simple dimension in the DOWN phrase. When the DOWN phrase specifies a conjoint dimension or a composite, the default label width is the width of the COLWIDTH option (default is 10) and there is a separate column for each base dimension.

You can specify widths for specific columns by using the WIDTH attribute (abbreviated W). For a composite or conjoint dimension, the WIDTH attribute applies to each base dimension column. The total label width is the number of base dimensions multiplied by the width you specify (plus one for the space between the columns).

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.

You can suppress a column by specifying a column width of 0.

When you use the LSET or RSET attribute with an expression that contains NA values, the text you specify with LSET or RSET will not be included to the left or right of any NA 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

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".

You can use the RUNTOTAL function within a REPORT command to create running totals.

When a "decimal overflow" condition occurs while subtotals are being accumulated (that is, an out-of-range value is generated), all subtotals for the affected column are set to NA and processing continues when the DECIMALOVERFLOW option is set to YES. When DECIMALOVERFLOW is set to NO, an error occurs when a decimal overflow condition occurs.

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.

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

Examples

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

--------------PRICE-------------
                --------------MONTH-------------
PRODUCT           Jan96       Feb96      Mar96
--------------- ---------- ---------- ----------
Tents               165.50     165.75     165.13
Canoes              200.25     200.09     200.05
Racquets             55.02      55.03      55.00
Sportswear           50.03      50.02      50.00
Footwear             38.01      38.01      38.01

LIMIT month TO 'Jan96' TO 'Jun96'
LIMIT district TO 'Boston' 'Chicago' 'Dallas'
REPORT SUBTOTALS W 8 DOWN month -
   ACROSS district: W 8 units

PRODUCT: TENTS
         ----------UNITS-----------
         ---------DISTRICT---------
MONTH    Boston   Chicago  Dallas
-------- -------- -------- --------
Jan96         307      189      308
Feb96         209      190      324
Mar96         277      257      436
Apr96         372      318      560
May96         525      433      744
Jun96         576      466      838
-------- -------- -------- --------
TOTAL       2,266    1,853    3,210
   ...

LIMIT product TO 'Sportswear'
LIMIT month TO 'May96' 'Jun96'
LIMIT district TO ALL
REPORT GRANDTOTALS W 12 DOWN district ACROSS month: -
   DECIMAL 0 <sales sales.plan>

PRODUCT: SPORTSWEAR
             -------------------MONTH-------------------
             --------May96-------- --------Jun96--------
DISTRICT       SALES    SALES.PLAN   SALES    SALES.PLAN
------------ ---------- ---------- ---------- ----------
Boston           72,617     69,623     79,630     73,569
Atlanta         161,537    148,823    177,967    157,939
Chicago         101,873     94,545    112,793     97,427
Dallas          170,939    165,449    175,066    164,192
Denver           89,971     91,880     97,237     94,729
Seattle          57,713     55,905     60,323     56,808
             ---------- ---------- ---------- ----------
                654,651    626,224    703,017    644,664
             ========== ========== ========== ==========

LIMIT district TO FIRST 3
LIMIT product TO ALL
LIMIT month TO 'Jan95'
REPORT GROUP month W 10 DOWN product ACROSS district: -
   <W 9 sales W 6 price>

MONTH: Jan95
           -------------------DISTRICT------------------------
           -----Boston-----  ----Atlanta----- -----Chicago----
PRODUCT      SALES    PRICE   SALES    PRICE   SALES    PRICE 
---------- --------- ------  --------- ------ --------- ------
Tents      32,153.52 160.77  40,674.20 160.77 29,098.94 160.77
Canoes     66,013.92 190.24  49,462.88 190.24 45,277.56 190.24
Racquets   52,420.86  52.84  54,798.82  52.84 54,270.39  52.84
Sportswear 53,194.70  48.54 114,446.26  48.54 72,123.47  48.54
Footwear   91,406.82  36.10 100,540.28  36.10 90,287.70  36.10

LIMIT month TO 'Jan96' TO 'Mar96'
LIMIT district TO 'Boston' 'Chicago'
REPORT d.sales

DISTRICT: Boston
               ------------D.SALES-------------
               -------------MONTH--------------
PRODUCT          Jan96      Feb96      Mar96
-------------- ---------- ---------- ----------
Tents                  NA         NA         NA
Canoes             70,489     82,238     97,622
Racquets               NA         NA         NA
Sportswear         57,079     63,122     67,006
Footwear               NA         NA         NA
 
DISTRICT: Chicago
               ------------D.SALES-------------
               -------------MONTH--------------
PRODUCT          Jan96      Feb96      Mar96
-------------- ---------- ---------- ----------
Tents                  NA         NA         NA
Canoes             48,662     54,425     68,816
Racquets               NA         NA         NA
Sportswear         77,490     85,879     85,308
Footwear               NA         NA         NA

REPORT DOWN SPARSE <product district> d.sales

------------D.SALES-------------
                      -------------MONTH--------------
 PRODUCT    DISTRICT    Jan96      Feb96      Mar96
---------- ---------- ---------- ---------- ----------
Canoes     Boston         70,489     82,238     97,622
Sportswear Boston         57,079     63,122     67,006
Canoes     Chicago        48,662     54,425     68,816
Sportswear Chicago        77,490     85,879     85,308

ALLSTAT
REPORT SPARSE <product district>