Retrieving Data for Rows

No automatic looping in the ROW command

Unlike the REPORT command and many other OLAP DML commands, the ROW command does not automatically loop over the values of a dimension.

For example, if you show the DISTRICT dimension with a ROW command, then only the first district in status is shown. The command

row district

produces the following output.

BOSTON

Showing multiple values of a single expression in a row

You can use the ACROSS keyword with the ROW command to show multiple values of a single expression across a row. By specifying ACROSS followed by the name of one of the dimensions or composites of the expression, you can show a data value for each dimension value in the current status. The name of the dimension or composite must be followed by a colon.

Tip: You can specify an unnamed composite in an ACROSS phrase by using the syntax that was used to create it.

Example: Looping across the values of UNITS

The following commands create a row of output that shows the value of UNITS for each month in status.

limit month to 'JAN95' to 'MAR95'
row district, across month: units

These commands produce the following output.

BOSTON                200        203        269

Example: Looping across the values of two expressions

You can apply the ACROSS keyword to more than one data expression by placing angle brackets around the expressions.

limit month to 'JAN95' 'FEB95'
row district, across month: <units sales>

The above commands create a row of output that shows a group containing a UNITS value and a SALES value for each month in status.

BOSTON                200  32,153.52        203  32,536.30

Setting temporary dimension status within the row command

Instead of using a LIMIT command to set the status of a dimension before you execute a ROW command, you can temporarily set the status of the dimension within the ACROSS phrase of the ROW command. You specify a temporary status for the dimension by specifying any of the LIMIT keywords along with an appropriate value list or related-dimension list.

When you set the status for a dimension within an ACROSS phrase, the status remains in effect only for the duration of the ROW command.

For example, the following command temporarily limits the MONTH dimension to January 1995 and February 1995.

row district, across month to 'JAN95' 'FEB95': -
   <units, sales>

Handling a null status condition

You can use the IFNONE keyword in an ACROSS phrase to branch to a label if an attempt to set status would result in no values. Your report program might contain the following lines.

row district, across month keep units gt 500 -
   ifnone novals: units
   .
   .
   .
return
novals:
   .
   .
   .

Looping across composites

A variable that uses a composite includes either the SPARSE keyword or a named composite in its dimension list.

If you report data for a variable that uses a composite, and you do not include an ACROSS phrase in the ROW command, then ROW shows output for all data cells that correspond to the base dimensions of the composite. If a particular combination of base dimension values does not exist in the composite, then ROW shows NA for the corresponding data cell.

If you specify a composite in an ACROSS phrase, then ROW shows output only for data cells for which combinations of base dimension values exist in the composite. This gives you a more concise report that better reflects your data.

If you specify one of the composite's base dimensions in an ACROSS phrase, then ROW shows NA for a data cell for which the composite contains no value.

If you specify a composite in the ACROSS phrase of a ROW command, then you cannot specify LIMIT arguments. You must limit the base dimensions of a composite to the desired values before you execute a ROW command.

Creating a separate row for each dimension value

You can produce a separate row of output for each dimension value in the current status by executing a ROW command in a FOR loop for the dimension. The FOR command can only be executed in a program.

By using nested FOR loops in your program, you can loop over more than one dimension. For example, you might want to show sales data for each product within each district.

Example: Creating a separate row for each district

To show the data for each district in a separate row, you might include lines such as the following ones in your program.

limit month to 'JAN95' to 'MAR95'
limit product to 'TENTS'
for district
   row district, across month: units

The above commands loop over the DISTRICT dimension to create the following output.

BOSTON                200        203        269
ATLANTA               253        276        320
CHICAGO               181        181        247
DALLAS                297        313        419
DENVER                227        210        283
SEATTLE               271        257        322

Each row shows data for the TENTS product for each of the three months in the current status.

Example: Creating a separate row for each product within each district

You can loop over products within a loop over districts to create groupings of output rows like the ones shown below.

BOSTON
------
   TENTS              200        203        269
   CANOES             347        400        482
   RACQUETS           992      1,076      1,114
   SPORTSWEAR       1,096      1,214      1,294
   FOOTWEAR         2,532      2,405      2,775
ATLANTA
-------
   TENTS              253        276        320
   CANOES             260        285        356
   RACQUETS         1,037      1,196      1,158
   SPORTSWEAR       2,358      2,538      2,856
   FOOTWEAR         2,785      3,064      3,217

You can create the output shown above by using nested FOR loops in a program, as shown below.

limit month to 'JAN95' to 'MAR95'
limit district to 'BOSTON' 'ATLANTA'
limit product to all
for district
   do
   row under '-' valonly district
   for product
      row indent 3 product, across month: units
   blank
   doend

The first (or outer) FOR command in the above code loops over the values of DISTRICT and executes all the commands between the DO and DOEND commands. The second (or inner) FOR command loops over the values of PRODUCT to create the rows of data. Therefore, for each value of DISTRICT, rows of unit sales data are produced for each product. The inner FOR loop does not require the DO and DOEND commands because only one command is executed in the loop.

Tip: The ROW commands in this example use the format attributes UNDER, VALONLY, and INDENT. ROW command attributes are explained in the topic "Modifying the Layout of Columns".