Thomas.I Microsoft BI & Analytics

A Site Dedicated to General BI Issues and MS BI Issues: Architecture and Technology

Multiselect in different clients illustrated

Posted by thomasivarssonmalmo on December 10, 2007

One of Mosha’s most popular posts is about multiselect in AS2000 and SSAS2005. I have nothing to add technically to that only some comments and pictures of what can happen with cubes and clients.
Multselect is fully supported in  SSAS2005 and it means that you can have sets in the slicer/where clause when you write and MDX Select.
You can have a where clause like this:
Where ([Measures].[Internet Sales Amount],[Date].[Calendar Year].&[2001]:[Date].[Calendar Year].&[2004]);
It will work fine in ProClarity Professional 6.3 or MDX-studio. You cannot enter a MDX Select in Excel 2007 or earlier.
Please note that ProClarity will construct a calculated member with Aggregate as Mosha showed in his post, so ProClarity still use the AS2000 way of solving this problem when you slice on multiple members. Still it fully supports a set in the slicer.
The problem occurs if you have a multiselect where clause and a current member MDX expression in a calculated member. You can paste these two calculated members into the calculations tab in BIDS and process the cube..
 AS Count(Filter(Descendants([Customer].[Customer Geography].CurrentMember, [Customer].[Customer Geography].[Customer]),
[Measures].[Internet Sales Amount]< ([Measures].[Internet Sales Amount],[Date].[Calendar].PrevMember))),
FORMAT_STRING = "### ### ### ###",
 AS Count(Filter(EXISTING[Customer].[Customer Geography].[Customer],
[Measures].[Internet Sales Amount]< ([Measures].[Internet Sales Amount],[Date].[Calendar].PrevMember))),
FORMAT_STRING = "### ### ### ###",
VISIBLE = 1  ;
It is the first calculated member([NoDecliningCustomerSales]) that will not work with multiselect. If you use it in ProClarity Professional 6.3 you will see a result like this.
ProClarity MultiSelect 2
If we test the MDX Select generated from ProClarity in MDX Studio we get this result with a clear error message in the cells.
MDX Studio
Now I will try the same first calculated member in Excel 2007 and see if it works. I have choosen the same countries in the Excel slicer as in ProClarity above.
Excel2007 multiselect
 This seems to work fine but why? With Marco Russo’s macro I was able to catch the MDX being send from Excel 2007.
SELECT NON EMPTY Hierarchize({DrilldownLevel({[Date].[Calendar].[All Periods]})})
NON EMPTY Hierarchize({DrilldownLevel({[Product].[Product Categories].[All Products]})})
(SELECT ({[Customer].[Customer Geography].[Country].&[Canada],
[Customer].[Customer Geography].[Country].&[France],[Customer].[Customer Geography].[Country].&[Germany],
[Customer].[Customer Geography].[Country].&[United Kingdom],
[Customer].[Customer Geography].[Country].&[United States]}) ON COLUMNS
 FROM [Adventure Works]) WHERE ([Measures].[NoDecliningCustomerSales])
It is a subcube or subselect and you can copy and paste it into management studio. It will not work in ProClarity, however.
Finally, if we use the second one ([NoDecliningCustomerSales1]),that is Mosha’s suggestion of to rewrite the expression for it to work with multiselect, what will happen in ProClarity?
Multiselect is working in ProClarity
 Finally the data will show up also in ProClarity. What we can learn from this is that clients have different ways of implementing MDX multiselect and Excel 2007 seems to be stronger in this respect than ProClarity 6.3 . Perhaps this is caused by the fact that ProClarity will have to support AS2000 cubes.
Multiselect with Excel 2003 will cause an error message in the cells.

One Response to “Multiselect in different clients illustrated”

  1. Sam Kane said

    Here are this and some other articles on MDX Multi-Select:

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: