In relation to the previous post on Report Drilldown(s), I've been playing with the Dimensions to get a really good idea of how they affect drilling capabilities.
Conceptually, it's not a problem, it's just hierarchical data. I wasn't born with the understanding of hierarchical data...I just kept messing around with it.
Anyway, I've run across a little problem. After perusing the usual places for answers, I couldn't find exactly what I was looking for. It's probably due to the fact that I'm still learning the BI lingo (Levels, Dimensions, Hierarchy, etc.). I did find a useful article from Mr. Rittman, but it didn't help me.
As I build and rebuild these dimensions (specifically Time), when I get to a certain level, the records are not rolling up. Here's what it should look like:
Easy enough right?
For the purposes of this demonstration, I'm only going to go down to the month level. This is also to keep you somewhat engaged.
Let's start with a screenshot of the Time Dimension in the BMM:
Instead of doing a million screenshots, I'll just explain how they are set up.
I pulled CALENDAR_YEAR and CALENDAR_YEAR_ID into the Year Level. From the Logical Level --> Keys tab, CALENDAR_YEAR is set up as a key with "Use for Drilldown" checked. I create another logical key on CALENDAR_YEAR_ID but I uncheck "Use for Drilldown". I then select CALENDAR_YEAR_ID as the Primary key for the table and also check off the Chronological Key for CALENDAR_YEAR_ID.
I did the exact same thing for the Quarter Level pulling in CALENDAR_QUARTER_DESC and CALENDAR_QUARTER_ID.
I switch over to Answers and run the report.
Now I drill down and I should only have one record.
This is where the trouble starts for me.
I do the exact same thing with the Month Level, pulling in CALENDAR_MONTH_DESC and CALENDAR_MONTH_ID.
When I run the report and click on a Quarter, I get the following:
You might not be able to fully see what it's doing...it's giving me a record for each day in that given month (all 3 quarters are represented). Suppressing the values (Column Format --> Suppress), it does the same thing.
If I remove CALENDAR_MONTH_DESC as a logical key and recheck "Use for Drilldown" on CALENDAR_MONTH_ID, it works just fine.
Only I don't care to have the users see the CALENDAR_MONTH_ID, it's meaningless to them.
When I looked at the SQL being issued to the database, I did notice that the first query (from year to quarter) had a DISTINCT clause and the second (from quarter to month) did not.
I've been beating my head against the wall because I know there is something small I am missing.
Any help would be much appreciated.
Update (12 minutes later)
Check out this one for my resolution.