Multiselect in different clients illustrated

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..

 

CREATE MEMBER CURRENTCUBE.[MEASURES].[NoDecliningCustomerSales]
 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 = "### ### ### ###",
VISIBLE = 1;

 

CREATE MEMBER CURRENTCUBE.[MEASURES].[NoDecliningCustomerSales2]
 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.

 

 

If we test the MDX Select generated from ProClarity in MDX Studio we get this result with a clear error message in the cells.

 

 

 

 

 

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.

 

 

 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]})})
DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS ,
NON EMPTY Hierarchize({DrilldownLevel({[Product].[Product Categories].[All Products]})})
DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS  FROM
(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])
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

 

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?

 

 

 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.