Retaining Drill Up & Down for Hierarchy with Union Query

[torre2011]

So, I am attempting to build a report that is using a PowerCube as a source.  For my test report, I am using a Time dimension, a Distributor Dimension, and one measure.

The requirement is that the time period will be dynamic, so an edn user will select Period #1 from a drop-down list and then select another time period as the comparison period from a 2nd drop-down box.  Then i need to calculate the variance between these two time periods within the same cross-tab.

The only way at present that I was able to accomplish this, was to create 2 queries that have the same dimension values but with different filters for the time period...and then use a Union Query to pull them together.  This works and provides the dynamic ability of aggregating on the time selections as well as any change in the Distributor dimension.

The issue i am encountering, is that I lose the ability to drill up and down on the Distributor hierarchy within the Cross-tab!

I am not sure, but it must have something to do with the fact that once I create the Union, i lose the hierarchy capabilities within the report???

Any ideas to get this to work within the union or if there are any suggestions to get the same functionality...i am ALL EARS!!

Thanks in advance!

[CognosPaul]

When using a dimensional source, you absolutely do not need to use unions like that. Doing that, and anything similar (joins, subqueries) will result in poor performance and reduced dimensional capabilities.

To begin, I suspect you're building the report similarly to how you would build the report based on a relational model. Are you using detail filters? Are you calculating the variance in the unioned query?

The first thing to do, get rid of each and every detail filter. There are very few cases where detail filters are needed, and this is not one of them.

Next step, identify which filters are based on items that appear in the report, and which do not. Are you showing this for time period versus product line, filtering on a specific region? The prompt for the region needs to go into the slicer.

Instead of using a filter for the time period, you can put the selected value directly into a data item. Create a data item, call it "Period 1", with the expression: #prompt('Period1','mun')#
Do the same thing for Period2.

Next, create another data item, Variance, with the expression: ([Period 1] / [Period 2]) - 1

Your crosstab should have Period 1, Period 2, Variance as peers in the columns. The drill down will still work as expected, and performance should be significantly better than the previous attempt.

There will still be a few challenges for you. What do you expect to happen when the user drills down on one of the periods? How should that affect the variance calculation?

[torre2011]

Great suggestion!  I have recently adjusted my OLAP reports from using detail filters as much as possible!

Unfortunately, I was moved from this project to another one...and I failed to come back here and see this response...my bad!  Anyway, it is great that I did now...since I am working with a similar issue with my Dynamic Cube reports.

I will let you know how it turns out.

Thanks Again!