2 Replies Latest reply: Sep 5, 2012 9:40 AM by Nasar-Oracle RSS

    Limiting to descendants to specfic ancestor

    957239
      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.-Oracle
          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-Oracle
            .
            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'))

            .