Skip Headers
Oracle® OLAP DML Reference
11g Release 2 (11.2)

E17122-08
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
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

STATRANK

The STATRANK function returns the position of a dimension or dimension surrogate value in the current status list or in a valueset.

Return Value

INTEGER

Syntax

STATRANK(dimension [value])

Parameters

dimension

A text expression whose value is the name of a dimension, dimension surrogate, or valueset.

value

The value you want to check, which is an appropriate data type for dimension. For example, value can be a text expression for an ID or TEXT dimension, an INTEGER for an INTEGER dimension, a date for a time dimension, or a combination of values enclosed by angle brackets for conjoint or concat dimensions. The value of a text expression must have the same capitalization as the actual dimension value. When you use a text expression, it must be a single-line value.

When you specify the value of a conjoint dimension, be sure to enclose the value in angle brackets, and separate the base dimension values with a comma and space. When you specify the value of a concat dimension, be sure to enclose the value in angle brackets, and separate the base dimension name from the value with a colon and space.

When you do not specify value, STATRANK returns the position of the current value. When you specify the name of a valid dimension value that is not in the current status list or in the valueset, STATRANK returns NA.

Examples

Example 8-116 Using STATRANK to Identify Position Numbers

Suppose you want to produce a report of the top five months by total sales, displayed in order as a numbered list. You can use STATRANK to number each month. Assume that you have written a report program with the following definition and contents.

DEFINE sales.rpt PROGRAM
PROGRAM
LIMIT month TO TOP 5 BASEDON TOTAL(sales, month)
SHOW 'Top five months by total sales:'
for month
    ROW WIDTH 4 JOINCHARS(STATRANK(month) '.') WIDTH 5 month
END

The report program produces the following output.

Top five months by total sales:
1.   Jul96
2.   Jun96
3.   Jul95
4.   Aug96
5.   Jun95

After executing the sales.rpt program, you can use a SHOW statement with the STATRANK function to learn the position of a particular month within the top five months by total sales.

The following statement

SHOW STATRANK(month Jun96)

produces this output.

2

Example 8-117 Using STATRANK When the Dimension Is a Conjoint Dimension

When the dimension that you specify is a conjoint dimension, then the entire value must be enclosed in single quotes.

For example, suppose the analytic workspace has a region dimension and a product dimension. The region dimension values include East, Central, and West. The product dimension values include Tents, Canoes, and Racquets.

The following statements define a conjoint dimension, and add values to it.

DEFINE reg.prod DIMENSION <region product>
MAINTAIN reg.prod ADD <'East', 'Tents'> <'West', 'Canoes'>

To specify base positions, use a statement such as the following. Because the position of East in the region dimension is 1 and the position of Tents in the product dimension is 1, the following statement returns the position of the corresponding reg.prod value.

SHOW STATRANK(reg.prod '<1, 1>')

1

To specify base text values, use a statement such as the following.

SHOW STATRANK(reg.prod '<\'East\', \'Tents\'>')

1

Example 8-118 Using STATRANK When the Dimension Is a Concat Dimension

When the dimension that you specify is a concat dimension, then the entire value must be enclosed in single quotes. The following statement defines a concat dimension named reg.prod.ccdim that has as its base dimensions region and product.

DEFINE reg.prod.ccdim DIMENSION CONCAT(region product)

A report of reg.prod.ccdim returns the following.

REG.PROD.CCDIM
----------------------
<Region: East>
<Region: Central>
<Region: West>
<Product: Tents>
<Product: Canoes>
<Product: Racquets>

To specify a base dimension position, use a statement such as the following. Because the position of racquets in the product dimension is 3, the statement returns the position in reg.prod.ccdim of the <product: Racquets> value.

SHOW STATRANK(reg.prod.ccdim '<product: 3>')

6

To specify base dimension text values, use a statement such as the following.

SHOW STATRANK(reg.prod.ccdim '<product: Tents>')

4