Drillthrough with Multiple Selected Values in Excel2007

Excel2007 is a great tool for power users/analysts to access a SSAS cube. They can slice ‘n dice the data as never before and even make appealing reports on the fly. Yet the new drillthrough capability is not completely bug free. First of all, it is impossible to drillthrough if the user has selected multiple values in a filter hierarchy or in a row or column axis. Okay, that is a restriction with the current state of SSAS2005. (I’ll explain how to get around it via MDX in another post.) What is worse is that in certain situations it does allow a drillthrough but then displays wrong results.

Consider this breakdown in some fictitious product sales hierarchy:

– Fruit 120
—- Apples 80
—- Pears 10
—- Oranges 30
– Other 100
Total 220

If the user selects apples and pears in the product hierarchy then the Excel report changes to:
– Fruit 90
—- Apples 80
—- Pears 10
Total 90

A drillthrough on apples works as expected: it will return the records that contributed to the sales value 80. The same for pears.
A drillthrough on the total will result in a warning messagebox from Excel telling you it is impossible to perform that drillthrough.
Yet performing the same drillthrough on the fruit level will return records. But not those that contributed to the sales value 90, but all those that contributed to the value 120 !

Try to explain that to your user… Not being able to perform a drillthrough when multiple values are selected in the filter or on an axis is a major drawback. It is a weakness inherent to SSAS2005, even though if you present the question in nicely formulated MDX it can answer it. But Excel uses the “standard” way of formulating a drillthrough in MDX. Which is probably ok, as it is a problem that should be tackled at the SSAS2005 side.


Comment by Jane
2008-10-31 21:24:06

We are facing the same problem. Is there any solution for this issue from Microsoft?

Comment by Michal
2009-12-09 07:58:20

Did you find a solution for that?

Name (required)
E-mail (required - never shown publicly)
Your Comment (smaller size | larger size)
You may use <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> in your comment.

To prove you're a person (not a spam script), type the security word shown in the picture. Click on the picture to hear an audio file of the word.
Click to hear an audio file of the anti-spam word

Trackback responses to this post