ROW command

The ROW command produces a single line of data in cells, one after another in a single row. A series of ROW commands that produce corresponding cells are often used to build up columns of data. For this reason, we normally speak of the ROW command as producing a line of columns. Output from the ROW command is sent to the current outfile.

The ROW command is typically used with other commands, functions, and options that you can think of collectively as report-writing statements

The ROW command itself consists of a series of column descriptions that specify the data to be produced and, optionally, the output format of the data.

In addition, ROW has a versatile capability for doing row and column arithmetic. It can perform calculations and include the calculation results in the output. It can use any kind of calculated expression in the column descriptions; and it can take advantage of row and column totaling functions (see Table 10-6, "Row and Column Arithmetic").

ROW is primarily used in report programs to produce the lines of the report. The maximum width of any row in a report is 4,000 characters.

Tip:

When you know ahead of time that you do not need the subtotaling capability of the ROW command, using a HEADING statement instead of ROW to produce the lines of your report can provide a time savings, because, in this case, Oracle OLAP does not keep track of subtotals.

See also::

ROW function

Syntax

ROW [attributes] [ACROSS dimension [limit-clause]:] {exp1|SKIP } -

[[attributes] [ACROSS dimension [limit-clause]:] {expn|SKIP }]

Parameters

ROW with no arguments produces a blank line.

attributes

One or more attributes for a column. Attributes are format specifications that determine how the data value is formatted within the column. There is no limit to the number of attributes that you can use to describe a column format. (See Table 10-5, "Column Attributes for ROW" for an explanation of each of the available attributes.) The default for some format attributes is determined by the current setting of Oracle OLAP options (see Table 10-7, "Report-Related Options" for a list of these options).

ACROSS

An ACROSS phrase lets you include multiple values of a dimensioned expression in a single row by looping over one dimension (or composite) of the expression. Typically, ROW shows only the value that corresponds to the first dimension value within the current limits. With an ACROSS phrase, ROW produces one data column for each dimension value currently in the status.

You can apply a single ACROSS phrase to multiple data expressions, or you can use separate ACROSS phrases for different data expressions. See "Multiple Expressions" and "Separate ACROSS Phrases".

dimension

The name of a dimension or composite over which the statement loops.

When you show data for a variable dimensioned by a composite and you do not include an ACROSS phrase, ROW shows output for all data cells that correspond to the base dimension values of the composite. When a particular combination of base dimension values does not exist in the composite, ROW shows NA for the corresponding data cell. Likewise, when you specify one composite's base dimension in an ACROSS phrase, ROW shows NA for a data cell for which the composite contains no value. However, when you specify a composite in the ACROSS phrase, ROW shows output only for data cells for which combinations of base dimension values exist in the composite which provides a more concise report that better reflects your data.

When the dimension specified in an ACROSS phrase has null status, ROW does not produce any data columns for that ACROSS phrase.

limit-clause

When you specify a dimension in the ACROSS phrase, a clause that enables you to temporarily change the status of that dimension during the execution of the ROW statement.

The syntax of limit-clause is the same syntax as any of the limit-clause arguments in the various forms of the LIMIT command (that is, the syntax of the LIMIT command after the limit-type argument such as "TO"). For the syntax of these arguments, see LIMIT (using values) command, LIMIT using LEVELREL command, LIMIT (using parent relation), LIMIT (using related dimension) command, LIMIT NOCONVERT command, and LIMIT command (using POSLIST).

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 empty status for the dimension, an error occurs. 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.

Note:

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 using a LIMIT command before you execute a ROW statement

SKIP

Used instead of an expression to indicate that the column is to be left blank.

Table 10-5 Column Attributes for ROW

Attribute	Meaning
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 4,000 characters. Columns with a width of `0` 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. (The default for TEXT data.)
RIGHT (R)	Right-justifies the value within its column. (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. When used with an expression that contains `NA` values, Oracle OLAP does not include the text you specify at the left of any `NA` values.
NOLSET	Does not add anything to the left of the value.
RSET 'text'	Adds text to the right of the value. When used with an expression that contains `NA` values, Oracle OLAP does not include the text you specify at the right of any `NA` values
NORSET	Does not add anything to the right of the value.
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 specified by 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.
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`. (Note that when you set the DECIMAL attribute to `0` and you use the NOLEADINGZERO keyword, Oracle OLAP does not show any decimal values between `-1` and `1` that are rounded to 0.)
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 instead 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 instead 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 or column 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 or column 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 ''`

Use the functions that are listed in Table 10-6, "Row and Column Arithmetic" to perform calculations on the values generated so far in a report.

Table 10-6 Row and Column Arithmetic

Function Data Type Value Returned

Function	Data Type	Value Returned
COLVAL(n)	DECIMAL	Value in the nth column of the current row. When n > `0`, an absolute column number (from the left margin, moving to the right). When n < `0`, a relative column number (from the current column, moving left).
RUNTOTAL(n) where: n = 1,2, ...32	DECIMAL	Total of all numbers generated in the current column since the last SUBTOTAL or ZEROTOTAL for n. Does not reset total for n to `0`.
SUBTOTAL(n) where: n = 1,2, ...32	DECIMAL	Total of all numbers generated in the current column since the last SUBTOTAL or ZEROTOTAL for n. Resets total for n to `0`.

COLVAL(n)

DECIMAL

Value in the nth column of the current row. When n > 0, an absolute column number (from the left margin, moving to the right). When n < 0, a relative column number (from the current column, moving left).

RUNTOTAL(n)

where:

n = 1,2, ...32

DECIMAL

Total of all numbers generated in the current column since the last SUBTOTAL or ZEROTOTAL for n. Does not reset total for n to 0.

SUBTOTAL(n)

where:

n = 1,2, ...32

DECIMAL

Total of all numbers generated in the current column since the last SUBTOTAL or ZEROTOTAL for n. Resets total for n to 0.

The options that are listed in Table 10-7, "Report-Related Options" affect the default format for a ROW command.

Table 10-7 Report-Related Options

Option	Meaning
COLWIDTH	Column width for all but the first column when the WIDTH attribute is not used. The default is `10`.
COMMAS	Specifies whether a thousands group separator is used when neither the COMMA attribute nor the NOCOMMA attribute is used. The default is `YES` (uses a separator).
DECIMALS	Number of decimal places when the DECIMAL attribute is not used. The default is `2`.
LCOLWIDTH	Column width for the first column when the WIDTH attribute is not used. The default is `14`.
LSIZE	Defines the line size within which the STDHDR program centers the standard header. The default is `80` characters.
NASPELL	Text used for `NA` values when the NASPELL attribute is not used. The default text is `NA`.
NLS_LANGUAGE	Specifies the text used for `TRUE` and `FALSE` Boolean values. These values are reflected in the YESSPELL and NOSPELL options.
NLS_TERRITORY	Specifies the character used for the decimal marker and the thousands group separator. These values are reflected in the DECIMALCHAR and THOUSANDSCHAR options.
PARENS	Parentheses usage for negative numbers when neither the PAREN attribute nor the NOPAREN attribute is used. The default is `NO` (does not use parentheses; uses a minus sign).
ZEROROW	Controls generation or suppression of rows in which all numeric values are zero. The default is `NO` (generates zero rows).
ZSPELL	Text used for zero values when the ZSPELL attribute is not used. The default text is `OFF`, which shows a zero (`0`).

Use the statements that are listed in Table 10-8, "OLAP DML Statements That Are Compatible with the ROW Command" with the ROW command.

Table 10-8 OLAP DML Statements That Are Compatible with the ROW Command

Command	Action
BLANK n	Produces n blank lines. The default is one line.
HEADING column- description(s)	Produces titles and column headings for a report. Numeric values in headings are not added to column totals.
PAGE	Forces a page break in output when PAGING is set to `YES`.
ZEROTOTAL	Resets all 32 totals to `0` for all columns.
ZEROTOTAL ALL col(s)	Resets all 32 totals to `0` for the specified columns, or for all columns when there are no column arguments.
ZEROTOTAL n col(s)	Resets the indicated total (n) to `0` for the specified columns, or for all columns when there are no column arguments.

Usage Notes

Report-Writing Commands

The ROW command and its associated options and commands are referred to collectively as report-writing statements. Table 10-6, "Row and Column Arithmetic" lists functions you can use for performing row and column arithmetic in reports. Table 10-7, "Report-Related Options" lists report-related options that determine the default format for ROW output. Table 10-8, "OLAP DML Statements That Are Compatible with the ROW Command" lists additional statements that are used in combination with ROW to create reports.You can use the PAGING option and associated paging-related options to produce your report program in a page-oriented format.

Labels for Composites and Conjoint Dimensions

When you produce a report of data that has a composite or a conjoint dimension in its dimension list, you can produce a label column for each base dimension by using the KEY function. You can also provide a separate WIDTH attribute for each label column. For example, when proddist is a composite with the base dimensions product and district, you can use statements similar to the following ones.

FOR proddist
   ROW W 12 KEY(proddist district) W 8 KEY(proddist product) ...

Multiple Expressions

When you want the same format attribute or ACROSS phrase to apply to multiple data expressions, you can enclose the expressions in angle brackets (< >) and place the common attributes or ACROSS phrase immediately before the bracketed expressions.

attributes <expression1, expression2, ...>

ACROSS dimension: <expression1, expression2, ...>

When you have attributes that apply to only one expression within the brackets, place the specific attributes immediately before the expression.

attributes1 <expression1, attributes2 expression2>

When an attribute inside angle brackets (specific to a column) conflicts with an attribute outside the brackets (common to several columns), the specific attribute overrides the common attribute.

You can nest brackets to any depth, if you have an equal number of right and left brackets.

Separate ACROSS Phrases

For data generated with an ACROSS phrase, you can produce all the columns for one expression and then all the columns for additional expressions by using separate ACROSS phrases.

ACROSS dim: expression1, ACROSS dim: expression2

You also can nest ACROSS phrases to show data columns for two or more dimensions of an expression across a row.

ACROSS dim1: ACROSS dim2: expression

Row and Column Arithmetic

See Table 10-6, "Row and Column Arithmetic" for a list of the functions available for row and column arithmetic. You can use these functions to perform calculations on the values already generated in a report. Oracle OLAP maintains 32 running totals for each column, so you can include up to 32 levels of subtotals in a report. Note that when a numeric value is too large to fit into a data cell, ROW 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, ROW replaces the value with asterisks.

Examples

Example 10-109 Labeling Data Values

In this example, ROW produces a line of output that contains a value of sales, along with the corresponding dimension values for district, month, and product that identify it.

ROW W 8 district month product sales

The preceding statement produces the following row of output.

Boston        Jan95      Tents  32,153.52

Example 10-110 Reporting Two Variables

The line of output produced by this ROW statement contains the current dimension value of district, followed by the values of sales and sales.plan for Sportswear for each of the first two months of 1996.

LIMIT month TO 'Jan96' 'Feb96'
LIMIT product TO 'Sportswear'
ROW W 8 district ACROSS month: <sales sales.plan>

These statements produce the following row of output.

Boston    57,079.10  61,434.20  63,121.50  64,006.91

Example 10-111 Formatting and Labeling the Output

In this ROW statement, you want to see the actual and planned sales of tents for June 1996. You want to limit the status of month only for this one ROW statement, so you include the value Jun96 in the ACROSS phrase. You format the values as whole dollar amounts, and you also add a dollar sign to the values, along with individual labels that identify the actual and planned figures.

LIMIT product TO 'Tents'
ROW WIDTH 15 name.product ACROSS month 'Jun96': -
   DECIMAL 0 LSET '$' W 18 -
   <RSET ' (actual)' sales -
   RSET ' (plan)' sales.plan>

These statements produce the following row of output.

3-Person Tents    $95,121 (actual)     $80,138 (plan)

Example 10-112 Reporting on a Variable Dimensioned by a Composite

In this example, D.SALES is a variable whose dimension list includes the dimension month and the unnamed composite SPARSE <product district>. By specifying the composite in an ACROSS phrase of a ROW statement, you can produce a report that includes only the data cells for which the composite contains values.

LIMIT product TO ALL
LIMIT district TO 'Atlanta'
LIMIT month TO 'Jan96'
ROW ACROSS SPARSE <product district>: d.sales