|Oracle9i OLAP Services Developer's Guide to the OLAP DML
Release 1 (9.0.1)
Part Number A86720-01
Writing Reports, 5 of 12
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
produces the following output.
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.
The following commands create a row of output that shows the value of UNITS for each month in status.
These commands produce the following output.
You can apply the ACROSS keyword to more than one data expression by placing angle brackets around the expressions.
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.
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.
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.
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.
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.
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.
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.