2 Replies Latest reply: May 9, 2013 11:58 AM by Alex1 RSS

    Order Prompt with two levels of hierarchy

    Alex1
      Hi experts!
      I'm using obiee 11.1.1.5.0

      I would like to give a correct order in my prompt mixing two levels of one dimension.

      Example:
      Dimension vehicle
      Level 1 (Bikes, Cars, Trucks) If I want to drill-down in bikes I can see Bike 111, Bike 999 (level2):
      Cars
      ----->Car 234
      ----->Car 112
      Bikes
      ---->Bike 111
      ---->Bike 999
      Trucks
      ----->Truck 332
      ----->Truck 112

      So I have created a dashboard prompt with column: Vehicles.Level1.Description
      Then I have created a SQL Sentece to list values:

      SELECT Level1.Description FROM "DEMO" ORDER BY Level1.ID UNION ALL SELECT Level2.Description FROM "DEMO" ORDER BY Level2.ID

      With these sentence I see values with alphabetical order (not by ID). First appear members of Level1 and then members of Level2.

      Bikes,Cars,Trucks, Bike 111, Bike 999, Car 112, Car 234, Truck 112, Truck 332

      In my case I would like to see in my dropdown prompt this order:

      Cars, Bikes, Trucks, Car 234, Car 112, Bike 111, Bike 999, Truck 332, Truck 112

      The same order ID that exists in each level.

      How can I do that??

      Thank you very much!!
        • 1. Re: Order Prompt with two levels of hierarchy
          Turbokat
          Alex1 wrote:
          Dimension vehicle
          Level 1 (Bikes, Cars, Trucks) If I want to drill-down in bikes I can see Bike 111, Bike 999 (level2):
          Cars
          ----->Car 234
          ----->Car 112
          Bikes
          ---->Bike 111
          ---->Bike 999
          Trucks
          ----->Truck 332
          ----->Truck 112
          Assuming you have a dim hierarchy setup in your repository, by setting under 'Keys' tab .. 2 keys, one is the Primary Key which is your ID at that level and Desc/Name which you will set for Display.

          Now use the Descriptor ID (Double Column) , read here -> http://www.rittmanmead.com/2010/08/oracle-bi-ee-11g-handling-double-columns-iddescription-interoperability/

          and here -> http://docs.oracle.com/cd/E23943_01/bi.1111/e10544/analyses.htm#CHDJDDGC

          HTH,
          SVS
          • 2. Re: Order Prompt with two levels of hierarchy
            Alex1
            Thanks for your answer

            But I think that I can't use your solution. I don't want to filter by ID. My assignment is to show with one column (in my case Level1) and add members of Level2 (same hierarchy) and give it a custom order.

            Any help??