Technical data

ManualsBrandsSimplicity ManualsDo-It-Yourself tools4564

161

162

163

164

165

166

167

168

169

170

Using Built-in Functio ns in Analytic Models Chapter 11

• You can associate members in one dimension with members in another dimension, and then look up an

associated value for each member in the first dimension. For example, you can associate each employee with

a job, and then look up the job salary for each employee.

Looking Up a Va

lue at a Positi on

To look up a value at a particular position, use the position number for the member argument. You can also

use an expression that returns the position number.

For e xample, to look up the value of SALES for the first member in the PRODUCTS dimension, use

the following formula:

AT(PRODUCTS, 1, SALES)

To look up the v

alue of SALES for the last member in the PRODUCTS d imension, use the following formula:

AT(PRODUCTS, NUMMEMBERS(PRODUCTS), SALES)

This works bec

ause the NUMMEMBERS function returns the number of members in the Products dimension,

which is the po

sition of the last member.

See C

hapter 11, “Using Built-in Functions in Analytic Models,” NUMMEMBERS , page 177.

Looking Up a Value for a Member by Name

To look up a va

lue for a particular member by name, u se a member reference for the membe r argument.

For example, the following formula returns UNIT_COST divided by UNIT_PRICE for the Monitors product:

AT(PRODUCTS, [PRODUCTS:Monitors], UNIT_COST / UNIT_PRICE)

You can achieve t he same result using member references after the data cube names, as follows:

UNIT_COST [PRODUCTS:Monitors] / UNIT_PRICE [PRODUCTS:Monitors]

To evaluate a complex expression for a single member, the AT function is more concise because you a re not

required to repeat the member reference for every data cube.

Looking Up an

Associated Value

You can associate members in one dimension with members in another dimension, and then look up an

associated value for each member in the first dimension. For example, suppose that you would like to associate

each employee with a job, and then look up the job salary for each employee. To do this, perform the following:

1. Create a dimension called JOBS.

2. CreateadatacubecalledEMPLOYEE_JOB.FormatthisdatacubeasamemberoftheJOBSdimension.

3. Create a dimension called EMPLOYEE. Attach this dimension to the EMPLOYEE_JOB data cube.

4. CreateadatacubecalledSALARY_BY_JOB, which contains the salary for each job.

5. Create a data cube called EMPLOYEE_SALARY. Create the following formula for this data cube.

You can look up the salary for each employee by using the name of the as sociation data cube a s

the member argument:

AT(JOBS, EMPLOYEE_JOB, SALARY_BY_JOB)

For each empl

oyee, the formula looks up the number in SALARY_BY_JOB that is at the member

indicated b

y EMPLOYEE_JOB.