Oracle9i OLAP Developer's Guide to the OLAP DML
Release 2 (9.2)

Part Number A95298-01
 Home Book List Contents Index Master Index Feedback

Working with Expressions, 4 of 11

## Specifying a Single Value for the Dimension of an Expression

A qualified data reference (QDR) is a way of limiting one or more dimensions of an expression to a single value. QDRs are useful when you want to specify a single value without changing the current status. Using a QDR, you can qualify a dimension (which allows you to specify one dimension value in an expression) or one or more dimensions of a variable or relation.

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`.
• 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 command, 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 command below 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, if 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 command fetches a single value.

You can use a qualified data reference with the target expression of the `=` command. 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. If 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.

#### Example 4-1 Replacing a Dimension in a Variable

Suppose you have two variables, `sales` and `quota`, which are dimensioned by `month`, `product`, and `district`. A third variable, `division`.`mgr`, is dimensioned by `month` and `division`. You also have a relation between `division` and `product`, called `division`.`product`. These objects have the following definitions.

```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

```

The command below produces the report following it.

```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

```

Suppose you want to obtain a report that shows the fraction by which sales have exceeded quota; and you want to include the appropriate division manager for each product. You can show the division manager for each product by using the relation `division`.`product`, which is related to `division` and dimensioned by `product`, as the qualifier. The QDR replaces the `division` dimension with `product`, so that it has the same dimensions as the other expression in the report "`sales` / `quota`." The command below produces the report following it.

```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`. If 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 command below displays the value `WEST`.

```SHOW region.district(district 'SEATTLE')
```

### Qualifying a Dimension

You can use a QDR to qualify the dimension itself, which allows 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 command or function.When you use an ampersand with a QDR, you must enclose the whole expression in parentheses if 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 the `REPORT` command.

```REPORT &(myvar(reptype 'ACTUAL'))

```

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

### Using the QUAL Function to Specify a QDR

Sometimes you will find that 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).

#### Example 4-2 Using the QUAL Function

The following example first shows how you might view your data by limiting its dimensions, and then how you might view it by using `QUAL`.

These commands produce the report shown below them.

```LIMIT month TO 'JAN96' TO 'JUN96'
LIMIT line TO 'COGS'
LIMIT division TO 'SPORTING'
REPORT DOWN month W 11 MAX(actual,budget) W 11 actual W 11 budget

DIVISION: SPORTING
---------------LINE----------------
---------------COGS----------------
MAX(ACTUAL,
MONTH            BUDGET)     ACTUAL      BUDGET
-------------- ----------- ----------- -----------
JAN96           287,557.87  287,557.87  279,773.01
FEB96           323,981.56  315,298.82  323,981.56
MAR96           326,184.87  326,184.87  302,177.88
APR96           394,544.27  394,544.27  386,100.82
MAY96           449,862.25  449,862.25  433,997.89
JUN96           457,347.55  457,347.55  448,042.45

```

Now consider how you might view the same figures for `MAX(actual,budget)` without changing the status of `line` or `division`.

```ALLSTAT
LIMIT month TO 'JAN96' TO 'JUN96'
REPORT HEADING 'For Cogs in Sporting Division' DOWN month -
QUAL(MAX(actual,budget), line 'COGS', division 'SPORTING')

For Cogs in
Sporting       MAX(ACTUAL,
Division         BUDGET)
-------------- -----------
JAN96           287,557.87
FEB96           323,981.56
MAR96           326,184.87
APR96           394,544.27
MAY96           449,862.25
JUN96           457,347.55

```

If you attempt to produce the same report with standard QDR syntax, then an error is signalled.

```REPORT HEADING 'For Cogs in Sporting Division' DOWN month -
MAX(actual,budget) (line cogs, division sporting)

```

The following error message is produced.

```ERROR: A right parenthesis or an operator is expected after LINE.
```