Limiting Dimension Values Directly in an Expression

Form of a Qualified Data Reference

A qualified data reference takes the following form.

     expression(dimname1 dimexp1 [, dimname2 dimexp2. . .])

The dimname argument is the name of one of the dimensions, or a dimension surrogate of the dimension, of the expression and the dimexp argument is one of the following:

• A value of dimname.

  Note:

  The setting of the LIMITSTRICT option determines how Oracle OLAP behaves when a a QDR specifies a nonexistent value. By default, when you specify a nonexistent value, Oracle OLAP treats the nonexistent value as an invalid value and issues an error. If, instead, you want Oracle OLAP to treat a nonexistent value as an NA value, set the value of LIMITSTRICT to NO

• A text expression whose result is a value of dimname.

• A numeric expression whose result is the logical position of a value of dimname.

• A relation of dimname.

  Note:

  To qualify a complex expression, use the QUAL function.

Qualifying a Variable

You can qualify any or all of a dimensions of a variable using either of the following techniques:

• The QDR can temporarily limit a dimension of the variable by selecting one specified value of the dimension. This value can be outside the current status.

• The QDR can replace a dimension of the variable with a less aggregate related dimension when you supply the name of an appropriate relation as the qualifier. The dimension is temporarily replaced by the dimension(s) of the relation.

For example, the variable sales has three dimensions, month, product, and district. You might want to compare total sales in Boston to the total sales in all cities. In a single statement, you want district to be limited to two different values:

• For the numerator of the expression, you want the status of district to be Boston.

• For the denominator of the expression, you want the status of district to be ALL.

The following statement lets you calculate this result by using a QDR.

SHOW sales(district 'Boston')/TOTAL(sales)

You can qualify more than one of the dimensions of a variable. For example, when you qualify all the dimensions of the sales variable by specifying one dimension value of each dimension, then you narrow sales down to a single–cell value.

To fetch sales for Jun02, Tents, and Seattle, use the following QDR.

SHOW sales(month 'Jun02', product 'Tents', district 'Seattle')

This statement fetches a single value.

You can use a qualified data reference with the target expression of an assignment (SET) statement. This lets you assign a value to a specific cell in a data object.

The following example assigns the value 10200 to the data cell of the sales composite that is specified in the qualified data reference. When the composite named sales does not already have a value for the combination Boston and Tents, then this value combination is added to the composite, thus adding the data cell.

sales(market 'Boston' product 'Tents' month 'Jan99')= 10200

Replacing a Dimension in a Variable

When you use a relation as the qualifier in the QDR, you replace a dimension of the variable with the dimension or dimensions of the relation. The relation must be related to the dimension that you are qualifying, and it must be dimensioned by the replacement dimension.

DEFINE sales VARIABLE DECIMAL <month product district>
LD Sales Revenue
DEFINE quota VARIABLE DECIMAL <month product district>
DEFINE division.mgr VARIABLE TEXT <month division>
DEFINE division.product RELATION division <product>
LD Division for each product 

REPORT division.mgr

-------------------DIVISION.MGR----------------------
          ----------------------MONTH--------------------------
DIVISION  JAn02    Feb02    Mar02    Apr02    May02    Jun02   
--------  -------- -------- -------- -------- -------- --------
Camping   Hawley   Hawley   Jones    Jones    Jones    Jones   
Sporting  Carey    Carey    Carey    Carey    Carey    Musgrave
Clothing  Musgrave Musgrave Musgrave Musgrave Musgrave Wong    

REPORT DOWN month sales W 6 sales/quota W 8 HEADING -
   'MANAGER' division.mgr(division division.product)

DISTRICT: BOSTON
        -----------------------------PRODUCT------------------------------------
        ----TEnts---- ---canoes---- --racquets---  --sportswear-- ---footwear---
        Sales/        Sales/        Sales/         Sales/         Sales/
Month   Quota Manager Quota Manager Quota Manager  Quota Manager  Quota  Manager
------  ----- ------- ----- ------- ----- -------  ----- -------- ----- --------
Jan02   1.00  Hawley  0.82  Hawley  1.02  Carey    0.91  Musgrave 0.92  Musgrave
Feb02   0.84  Hawley  0.96  Hawley  1.00  Carey    0.80  Musgrave 1.07  Musgrave
Mar02   0.87  Jones   0.95  Jones   0.87  Carey    0.88  Musgrave 0.91  Musgrave
Apr02   0.91  Jones   0.93  Jones   0.99  Carey    0.94  Musgrave 0.95  Musgrave
...

Qualifying a Relation

You can also use a QDR to qualify a relation (which is really a special kind of variable).

Suppose the region.district relation is dimensioned by district. When you qualify district with the value Seattle, then the value of the expression is the value of the relation for Seattle. Because the QDR specifies one value of district, the expression has a single–cell result.

The definition of region.district is as follows.

DEFINE region.district RELATION region <district>
LD The region for each district

The following statement displays the value WEST.

SHOW region.district(district 'Seattle')

Qualifying a Dimension

You can use a QDR to qualify the dimension itself, which enables you to specify one dimension value in an expression. The following expression specifies one value of district, the one contained in the single-cell variable mydistrict.

district(district mydistrict)

For a concat dimension, you can use a QDR to qualify the dimension by specifying a value from one of the base dimensions of the concat dimension. The following expression specifies one value of reg.dist.ccdim, a concat dimension that has region and district as its base dimensions. The costs variable is dimensioned by the division and reg.dist.ccdim dimensions.

SHOW reg.dist.ccdim(district 'Boston')

The preceding expression produces the following result.

<DISTRICT: Boston>

Using Ampersand Substitution with QDRs

An ampersand character (&) at the beginning of an expression substitutes the value of the expression for the expression itself in a statement.When you use an ampersand with a QDR, you must enclose the whole expression in parentheses when you want the variable to be qualified before the substitution is made.

Suppose you have a text variable named myvar that is dimensioned by reptype and that contains the names of variables. Remember that it is myvar that is dimensioned by reptype, not the variables named by myvar. Therefore, you must use parentheses so that myvar is qualified and the resulting value is used in a REPORT statement.

REPORT &(myvar(reptype 'actual'))

When you do not use parentheses and the variable that is specified in myvar is sales, then you get an error message that sales is not dimensioned by reptype.

Using the QUAL Function to Specify a QDR

Sometimes you the syntax of a QDR is ambiguous and could either be misinterpreted or cause a syntax error. In this case, you can use the QUAL function to explicitly specify a qualified data reference (QDR).