Limiting Based on a Relationship Within a Hierarchy

You can use a family tree to place dimension values in status. You can limit a dimension as follows:

You can limit a dimension to the parents, children, ancestors, or descendants of each value in a list of specified values or for each value in status.

You can also find the descendants based on a particular parent relationship. This is useful with hierarchical dimensions that contain both a detail level and levels that are aggregations of lower levels. To use the LIMIT command in this way, you must ensure that the analytic workspace contains a relation that holds the parent for each value of the dimension.

The simplified syntax for limiting a dimension based on a relationship within a hierarchy is shown below.

The PARENTS keyword finds the parent of each value in valuelist or, when there is no valuelist, it finds the parent for each value in status. It uses the parent-rel to look up the parent.

The CHILDREN keyword finds the children of each value in valuelist or, when there is no valuelist, finds the children for each value in status. It uses the parent-rel to look up the children.

The ANCESTORS keyword finds the ancestors (that is, parents, grandparents, and so on) of each value in valuelist or, when there is no valuelist, finds the ancestors of each value in status.

The DESCENDANTS keyword finds the descendants (that is, children, grandchildren, and so on) of each value in valuelist or, when there is no valuelist, finds descendants for each value in status.

The HIERARCHY keyword is similar to DESCENDANTS and finds the descendants (that is, children, grandchildren, and so on) based on the value of the parent-rel argument.

The parent-rel argument is the name of a relation between the dimension and itself. For each dimension value, the relation holds another value of the dimension that is its parent dimension value (the one immediately above it in a given hierarchy). This parent-relation can have more than one dimension.

Differences Between HIERARCHY and DESCENDANTS Keywords

Both the HIERARCHY and DESCENDANTS keywords of the LIMIT command allow you to set the status of a dimension based on its family tree; however, the different keywords give you different results.

One difference is the order of the values:

DESCENDANTS groups the values by level (all children, and then all grandchildren).

HIERARCHY places each group of children next to its parent.

Additionally, if you use the HIERARCHY keyword, then you can include the additional arguments described in the following table that let you further manipulate the contents of the current status list.

IF you want to . . .

THEN use the . . .

list children before their parents,

INVERTED keyword.

skip n generations for each value in valuelist, or, when there is no valuelist skip n generations for each value in status,

SKIPn phrase.

include n generations down from each value of valuelist or, when there is no valuelist, include n generations for each value in status,

DEPTHn phrase.

run a command, represented as a text expression, every time it constructs a group of children,

RUNtextexp phrase.

exclude the original values from the current status list,

NOORIGIN keyword.

Example 6-5 Skipping Generations

Suppose your application issues the following command.

LIMIT market TO HIERARCHY DEPTH 2 SKIP 1 USING market.market 'TOTUS'

In processing this command, the parent relation is searched (market.market) to find the children and the grandchildren (DEPTH 2) of TOTUS and discards the first generation (SKIP 1).

The resulting status follows.

TOTUS
BOSTON
ATLANTA
CHICAGO
DALLAS
DENVER
SEATTLE

Note that TOTUS is included in status. With HIERARCHY, the original values are included in status.

Example 6-6 Sorting a Group of Children

When you are using the HIERARCHY keyword with the LIMIT command, you can use the RUN keyword to execute a command, specified as a text expression, every time a group of children is constructed. This lets you further manipulate the values that are being placed in status.

The following command not only limits the values of the market dimension to descendants using the market.market self-relation but also, every time a group of children is constructed, sorts the values in the market dimension in increasing order based on unit sales.

LIMIT market TO HIERARCHY RUN 'SORT market A unit.m' USING market.market

Note:

In this command, if you use KEEP or REMOVE instead of TO in the LIMIT command, then the SORT command has no effect.

Example 6-7 Drilling Down on a Hierarchy Using a Relation

Suppose you want to drill down on districts from the region level of the market dimension. This is a two step process.

The first step in the process is to limit the market dimension, which has embedded totals at the district, region, and total U.S. level, to the region-level data. This is done using the relation mlv.market, which is a relation between market and marketlevel.

The following command produces the report shown below it, which shows the values of mlv.market.

REPORT mlv.market
MARKET MLV.MARKET
-------------- ----------
TOTUS TOTUS
EAST REGION
BOSTON DISTRICT
ATLANTA DISTRICT
CENTRAL REGION
CHICAGO DISTRICT
DALLAS DISTRICT
WEST REGION
DENVER DISTRICT
SEATTLE DISTRICT

The following commands limit the values of market to the desired values and display the values that are currently in status for the market dimension.

LIMIT market TO mlv.market 'REGION'
STATUS market
The current status of MARKET is:
EAST, CENTRAL, WEST

The second step in the process is to drill down on the district-level data from the region level. You can use the self-relation market.market to perform the drill down. For each value of the market dimension, this relation contains the name of its parent.