Skip Headers
Oracle® OLAP DML Reference
10g Release 2 (10.2)

Part Number B14346-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

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] [attributesexpression(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.

[ADD|COMPLEMENT|KEEP|REMOVE|INSERT|TOvaluelist [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).

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 <expression1expression2>

  • 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 expression1expression2>

    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:

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

These statements produce the following output.

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

Example 20-29 Including Column Totals

This example looks at unit sales for three districts for the first half of 1996, with district across the report and a subtotal for each column. (By default, months would be arranged across the report, since month is the fastest-varying dimension of units.) To make the report more compact, specify a smaller column width of characters.

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

These statements produce the following output.

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

REPORT produces a similar slice for each product.

Example 20-30 Comparing Two Variables

This example compares actual sportswear sales with the projected sales plan, looking only at whole-dollar figures. It reports the actual and planned values side-by-side for May and June, 1996, and provides a grand total of sales and planned sales for all districts.

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>

These statements produce the following output.

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
             ========== ========== ========== ==========

Example 20-31 Repeating Price Data

This example compares sales across three districts, and it includes the unit price beside each sales figure for close comparison within each district. The REPORT statement specifies two expressions, sales and price. Since sales has three dimensions, month, product, and district, the report shows these three dimensions. However, price is not dimensioned by district. Therefore, the report repeats the values of price for each district. The report for January 1995 shown.

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>

These statements produce the following output.

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

Example 20-32 Reporting Data Dimensioned by Composites

In this example, d.sales is a variable whose dimension list includes the dimension month and the unnamed composite SPARSE <product district>. The unnamed composite contains no values for the base dimension combinations for the Boston and Chicago districts and the Tents, Racquets, And Footwear products. When you use the default form of the REPORT command to produce a report of d.sales data, REPORT breaks out the report by month and by the base dimensions of the unnamed composite (product and district). For the combinations of base dimension values that do not exist in the composite, the report shows NA for the corresponding data cells.

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

These statements produce the following output.

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

By specifying the composite in an ACROSS, DOWN, or GROUP phrase, you can override the default format of REPORT and break out the d.sales data by its composite. In this case, the report only includes the data cells for which the composite contains values.

REPORT DOWN SPARSE <product district> d.sales

This statement produces the following report.

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