This discussion is archived
2 Replies Latest reply: Sep 5, 2012 9:40 AM by Nasar RSS

Limiting to descendants to specfic ancestor

957239 Newbie
Currently Being Moderated
How can I limit a dimension to obtain specific descendants of of a hierarchy?

Example:

YEAR
QTR
MONTH
DAY

I'd like to obtain all days for Year X.

Or

CATEGORY
SUBCATEGORY
PRODUCT

I'd like to obtain all products for a specific Category or Subcategory.

Another example could be.

If you have

MONTH
DAY

How can I determine all previous days (in that month) compared to the current day

Thanks!
  • 1. Re: Limiting to descendants to specfic ancestor
    Shankar S. Journeyer
    Currently Being Moderated
    Use Limit to modify dimension status as required.
    It exists as LIMIT command and also as LIMIT function "LIMIT()".
    Read Oracle OLAP DML Reference 11g Release 2 document for details/examples.
    Find it via google or via oracle olap home page.
  • 2. Re: Limiting to descendants to specfic ancestor
    Nasar Journeyer
    Currently Being Moderated
    .
    If you want the leaf-level members, then use BOTTOMDESCENDANTS function.

    There are many ways of doing this. Lets say that your dimension name is TIME and its hierarchy name is TIMEHIER. Here are some examples:

    (1). You can do it multiple commands:
    LIMIT TIME_HIERLIST to 'TIMEHIER';
    LIMIT TIME TO 'YR2010';
    LIMIT TIME TO BOTTOMDESCENDANTS';

    (2). If you have one hierarchy:
    LIMIT TIME TO (limit(limit(time to 'YR2010') to bottomdescendants));

    (3). If you have multiple hierarchies, then you can qualify it with hierarchy name:
    limit time to (limit(limit(time to 'YR2010') to bottomdescendants using relation TIME_PARENTREL qualify TIME_HIERLIST 'TIMEHIER'))

    .

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points