Drill Through for Multiple Cells

OK, I should have known that for the name Schnieder.
You didn't have a colon ( at the end so I did not realize that the
text that followed it was the actual error message, but my bad still.

So the mutlti-select is on the filters, and not a multiple-cell
selection, thanks for the details.

Thus the resultset (row and column) represents a blended result for
Germany & Italy (we all remember the last time those two countries
were combined, so be careful! ). I don't like that that is the way
any UI handles it...for this, it ought to provide multiple grids, and
not just blend them into one, at least I'd prefer the former being an
option.

You might try to build a simple ASP or ASP.Net page that you could
pass it a SQL Query that would reach out to the relational database to
retrieve the Facts for the right facts rows. It would generally be
simple to build the SQL dynamically from MDX functions. Try to do
simple simple stuff to get the DimA.CurrentMember.Name and so on to
get the right stuff to display. Then only thing that might be
challenging is the multi-select filter stuff because the CurrentMember
function wold be peculiar becauise there would be more than one. Not
sure on this part of it via MDX. Maybe there would be something in
VBA from Excel in the PTS object model that would expose that part of
the problem.

All of this seems like a lot of trouble, so not sure how inflexible
the en-users would be in just having the single-select drill through
and not the multi-select.

Here is a guy from a couple years ago who had the same problem:
http://blogs.msdn.com/toffer/archive/2006/03/28/563439.aspx

And Mosha put something up about 3 years ago:
http://www.mosha.com/msolap/articles/mdxmultiselectcalcs.htm

I would look at the PTS model through VBA and Excel exposing the mutli-
select items, pulling them via the code and dynamically building SQL
and passing that query over to an ASP page. Somewhat of a pain, more
of a pain to get all the syntax just right than anything terribly
complex.

-exologic
 >> Stay informed about: Drill Through for Multiple Cells 

schrieb im Newsbeitrag


Thanks a lot for the answer.

> So the mutlti-select is on the filters, and not a multiple-cell
> selection, thanks for the details.

Yes, I tried by selecting several members from a dimension. It doesn't
matter, if it is done with a global filter or with the dimension-specific
filter box that is tied to a row or a column.

I do not know, what other way I could go for such a selection. What else
could be done in Excel for a "multiple-cell selection".

> Thus the resultset (row and column) represents a blended result for
> Germany & Italy (we all remember the last time those two countries
> were combined, so be careful! ). I don't like that that is the way
> any UI handles it...for this, it ought to provide multiple grids, and
> not just blend them into one, at least I'd prefer the former being an
> option.

Multiple grids? I think users like to see all data in one list.

> You might try to build a simple ASP or ASP.Net page that you could
> pass it a SQL Query that would reach out to the relational database to
> retrieve the Facts for the right facts rows.

In general this will not be that easy, if it has to work with any scenario.
You do not know which dimensions a user has included into the grids, what
selections he has selected.

- Maybe there is a Country dimension, maybe it has been filtered to C1 and
C2 and C3
- Maybe there is a Product dimension, maybe it has been filtered to P1 and
P2 and P3 and P4
- Maybe there is a Time dimension, maybe it has been filtered to dates D1
and D2

You then would have to build all possible permutations on these filter
values and ask for the rows that contributed to

- C1 and P1 and D1
- C1 and P1 and D2
- and so on

and then all rows had to be merged into a single list.

> ProClarity

I downloaded ProClarity v6.3.133.500 and tried. It also offers filter
conditions, with totals being calculated for the filtered cells. There is a
"Drill to Detail" action showing the rows. However, this action is not
offered, if you select a cell in the grid which is a sum.

Michael G. Schneider
 >> Stay informed about: Drill Through for Multiple Cells 

Michael,

The grid thing is merely a matter of taste, but both should be
options.

On the permutations, the code would take care of that, and in part
check for whether the item selected is "All".
What would it do if the UI allows children and parents to be selected
(not sure if Excel does this), because then the blended result would
be nonsense unless the children just dod not natually rollup, a la
"~".

Can you try using Excel macros to help you get at what code is
generated when you pick several members as filters, and then stop the
macro and see what the code looks like? If you had that, it would be
a good deal of the problem solved.

After that, the rest would be failrly elemental, just building the SQL
from the MDX strings that were output from the dim.Currentmember.Name
routine.

I thought I'd at least get a phony chuckle from the Germany, Italy
comment...oh well, all business on this group...

-exologic
 >> Stay informed about: Drill Through for Multiple Cells 

schrieb im Newsbeitrag


Thanks a lot for the answer.

I will have a look at the problem from a dot.net developer's view. Chrismas
is coming, maybe I have some time for playing with this.

Michael G. Schneider
 >> Stay informed about: Drill Through for Multiple Cells 

I'm having a similar issue to what Michael describes in his 3b example. I'm
hoping someone can fill me in on if there is a fix for this. Basically, I'm
running SQL server 05 and Excel 2007 and working with a data cube. When I
build my pivot table off the cube, I set up a report that lists dollars by
group and by month/year. To build off the previous example, suppose I have a
table like:

Jan 08 Feb 08 Mar 08
Germany 100 100 100

When I chose just the month of March to filter, everything looks good on the
pivot table. However, if I drill down on 2008 Total, even though it lists a
value of 100 because of the filter, it brings back all of the rows from Jan,
Feb AND March. Is there a setting I can change to fix this, since I would
expect, and only want the rows returned that made up the 100 value. I know
in this case I could just click on March, but if I picked February and March,
and then wanted to return rows for those two months, it doesn't work (it
brings back all the 2008 rows again). Any help or insight would be
appreciated. Does anyone know if this is considered a problem and a fix is
being worked on, or is this the expected behavior in Excel?

Thanks

"Michael G. Schneider" wrote:

> With MS SQL Server 2005, MS Excel 2007.
>
> I appreciated some more info about this subject "Drill Through for Multiple
> Cells" that we already discussed some days ago.
>
> [1] Why is it so difficult for SQL Server to achieve this? Is it a
> limitation of MDX?
>
> [2] Is there any workaround? Any client side tools, maybe macros within
> Excel, that are able to deliver the requested results?
>
> [3] Here are two similar scenarios.
>
> [3a] A dimension is placed into the global report filter. Multiple values
> are selected. Then, when performing a drill through, the known error message
> is given.
>
> [3b] A dimension is placed into the row area. Multiple values are selected.
> Then, when performing a drill through, no error message is given. Instead a
> drill through is silently performed, just as if all vaues had been selected.
>
> The scenario [3b] only seems to happen, if another dimension is placed
> before the dimension with the multiple selected values. So you have to
> double click a sum-cell in Excel. I think this is an error with Excel. At
> least, it delivers results that the user does not expect to see.
>
> Michael G. Schneider
>
>
>
 >> Stay informed about: Drill Through for Multiple Cells 

"William C. Branch III"
schrieb im Newsbeitrag


> I'm having a similar issue to what Michael describes in his 3b example.
> I'm
> hoping someone can fill me in on if there is a fix for this.

I think that the situation can be described as follows

- If a client tool such as Excel wants to perform a drillthrough, it does
not collect the data by "looking here and there". It simply executes a
single command that is sent to SQL Server.

- A drillthrough can only be executed on a single cell in the cube. It must
be the intersection of "member m1 in dimension d1" with "member m2 in
dimension d2" with ...

- A drillthrough cannot be executed on "a list of members in a dimension".
The MDX language doesn't allow this.

- It is not a problem with Excel. It is not a problem with SQL Server. It is
a missing part in the language.

- I wouldn't expect that a solution is delivered with some fix.

Please keep in mind that some days ago I was the one who asked. So don't
trust my words too much. If you can show that I am wrong, I will be happy.

Michael G. Schneider
 >> Stay informed about: Drill Through for Multiple Cells