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 in conjunction 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 21-3, "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 4000 characters.

Syntax

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

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

Arguments

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 21-2, "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 21-4, "Report-Related Options" for a list of these options).

ACROSS

An ACROSS phrase lets you include more than one value of a dimensioned expression in a single row by looping over one of the dimensions (or composites) of the expression. Normally ROW just shows 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 of the composite's base dimensions 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. This 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 format of limit-clause is the same as that for a LIMIT command, excluding the word "LIMIT". You can use any valid LIMIT clause (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 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 in place of an expression to indicate that the column is to be left blank.

Table 21-2 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 4000 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. 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. 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 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 theZSPELL 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 21-3, "Row and Column Arithmetic" to perform calculations on the values generated so far in a report.

Table 21-3 Row and Column Arithmetic

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.

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

Table 21-4 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 theZSPELL attribute is not used. The default text is OFF, which shows a zero (0).

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

Table 21-5 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.

Notes

Report-Writing Commands

The ROW command and its associated options and commands are referred to collectively as report-writing statements. Table 21-3, "Row and Column Arithmetic" lists functions you can use for performing row and column arithmetic in reports. Table 21-4, "Report-Related Options" lists report-related options that determine the default format for ROW output. Table 21-5, "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 more than one data expression, you can enclose the expressions in angle brackets (< >) and place the common attributes or ACROSS phrase immediately before the bracketed expressions.

attributes <expression1, expression2, ...> 

or

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

When you have attributes that apply to only one of the expressions 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, as long as 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 21-3, "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.

Performance Tip for Using ROW with Variables Dimensioned by Composites

By default, when ROW explicitly loops over a composite, or when ROW is executed in a FOR loop that explicitly loops over a composite, Oracle OLAP 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 ROW output is produced, you can set the SORTCOMPOSITE option to NO.

Examples

ROW W 8 district month product sales

Boston        Jan95      Tents  32,153.52

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

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

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

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

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