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.

LIMIT dimension TO {PARENTS|CHILDREN|ANCESTORS|DESCENDANTS|HIERARCHY} -
 USING parent-rel[valuelist]

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.

The valuelist argument can be any inclusive list of values.

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,	SKIP n phrase.
include n generations down from each value of valuelist or, when there is no valuelist, include n generations for each value in status,	DEPTH n phrase.
run a command, represented as a text expression, every time it constructs a group of children,	RUN textexp 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

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.

DEFINE MARKET.MARKET RELATION MARKET <MARKET>
LD Self-relation for the Market Dimension

A report of market.market produces the following output.

MARKET         MARKET.MARKET
-------------- -------------
TOTUS          NA
EAST           TOTUS
BOSTON         CENTRAL
ATLANTA        EAST
CENTRAL        TOTUS
CHICAGO        CENTRAL
DALLAS         CENTRAL
WEST           TOTUS
DENVER         WEST
SEATTLE        WEST

The following commands limit market to the children of the EAST, CENTRAL, and WEST regions and drill down to the district-level data by using the CHILDREN keyword with the LIMIT command.

LIMIT market TO mlv.market 'REGION'
LIMIT market tO CHILDREN USING market.market

A report of market produces the following output and shows the values that are now in status.

MARKET
-------------
BOSTON
ATLANTA
CHICAGO
DALLAS
DENVER
SEATTLE