This discussion is archived
2 Replies Latest reply: May 9, 2013 9:58 AM by Alex1 RSS

Order Prompt with two levels of hierarchy

Alex1 Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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??

Legend

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