OBIEE: Report Drilldown

As I am doing mainly metadata work right now, I figured it would be good to get a better grasp of how the reporting works on the presentation layer, i.e. Answers.

To that extent, I've been having problems getting the drilldown reports to work the way I want them to. The problem is that the ID associated with the level is displaying, not the value.

I'm using the Time Dimension:

Total --> Year --> Quarter --> Month



After initially creating it, I create an Answers report with one column, CALENDAR_YEAR.



Might be hard to notice, but the year is hyperlinked, I should be able to click it and drill down to the quarter right?



That's not what I want. I don't want to see the ID associated with it. That's meaningless to the user. You might be thinking, "What an idiot, why did he make the ID available in the presentation layer?"

I just thought of that too...removed it, and I received an error.

Anyway, the same thing (%_ID columns showing up instead of their description).

Click on Year (again) and I get the Quarters...well, the ID anyway.



Click on the quarter (ID) and I get the month ID.



So how do I fix this?

First, go back to the Admin tool, find the dimension and double click on Month to bring up the properties page



Select the Keys tab



You should see CALENDAR_MONTH_ID with the Chronological Key box checked. Select New



Now check CALENDAR_MONTH_NUMBER and make sure that Drilldown is checked.



That should take you back to the Keys screen. Now either double-click CALENDAR_MONTH_ID or select it and click Edit down at the bottom



Now uncheck the Use for Drilldown box



Rinse and repeat for each of your dimension levels; Quarter and Year.

Go back to your report, remove the filters and remove everything but CALENDAR_YEAR, run the report, drill down and you should see this:



Voila!

You can use whatever columns you want to display. You don't have to use the Month Number, you could use March instead.