Drill thru from Totals

[cognostechie]

So I have a Bar Chart that shows the data by a Geography Dimension. The dimension has 3 levels -

1> Region (Europe, Americas, Asia Pacific etc)
2> Country (USA, Switzerland etc)
3> Customer (Customer in those countries)

The Chart has drill downs and they also need to see the detailed data so it has a drill thru to the detailed report by passing the data item and parameter values.

It was working fine except that they have to drill thru from each Bar so if they need to see the detailed report for all of it, they have to drill thru so many times. So I added a Total for all the bars which gives the correct Totals for all levels when I drill down. The problem is when I drill thru from the Total Bar, from the top level, the drill thru shows the correct data but once I drill down to the 2nd level and then drill thru, it still shows the data of the higher level. The Total Bar itself shows the correct data at all levels. It's only the drill thru that is the problem and problem only when I drill thru from the Totals Bar. Drill thru from individual bars is ok at all levels.

This is because the 'Totals' Bar does not have any reference to the individual Bars so it can't pass the Data Item value to the detailed report. The function in the Total bar is (aggregate(currentMeasure within detail [Geography]). 

I am thinking of using some MDX functions like getting value of higher levels in the Total Bar.  Has anybody done this before ? or any other ideas?

[cognostechie]

Nobody ??

PaulM ?

[cognostechie]

Found a solution without MDX..

However, if there is an MDX equivalent, I would like to know. I have tried parent/ancestor/rolevalue/currentmember etc but no luck !

[CognosPaul]

I dislike drilldowns for exactly this reason. You can't save the member in a parameter for easy usage. An easy fix is to put the All level as the total. When you drill down on the region bars the All level will become the member you drilled on.

As a quick aside nice trick when drilling to relational sources is to pass the level name in addition to the category code. Assuming your levels use the same names as your model, you could do something like:

[Namespace].[Geography].#sb(prompt('GeographyLevel','token'))# = #prompt('GeographyCode','token')#

[cognostechie]

That's exactly what I did to get it to work. Thanks Paul for confirming that MDX won't provide the correct solution for this.

I am using a DMR for the summary report and the relational query subjects for the detailed report but I will try your suggection of passing the levels with a cube to a relational drill-thru.

Thanks again!

[holografix]

Hey guys,

From this post I think you might know a solution to a problem I'm having.

I have a crosstab from a DMR and I also have a drill-through report. I'd like to pass to the drill through the path that user has drilled down in the crosstab.

I followed a quick tute on an IBM website on how to show the drill-down breadcrumbs and thought that it would provide me with a solution, but the same data items that correctly show the breadcrumbs in the report get passed as nulls to the drill through...

Any clues?

ps: this is the tute I followed:
https://www-304.ibm.com/support/docview.wss?uid=swg21470373&wv=1