Solution for SSAS 2008 multi-selects in Excel (Dynamic Sets)
Being able to select multiple dimension elements in an MDX client requires some care in designing calculations for the cube. However, with SQL Server 2005 it was really difficult to design calculations in a way that work fine with Excel 2007. The reason for this is that Excel 2007 uses sub cubes for filtering and sets in SQL Server 2005 did not reflect sub cubes. So this article is about multi-select friendly queries in SSAS 2008 using dynamic sets.
There have been some posts about the problems around multi-selects. Of course you cannot use a currentmember reference in your calculation (if a set is in the where condition, there is no single current member) but use sets in most cases. However, also sets do not react on where conditions in an MDX statement by default. You have to add the EXISTING keyword to get the desired result. In order to illustrate this, we’ll start with a very simple calculated measure. Our measure should just return the number of days being selected as the filter for our date dimension. I’ll use the good old Adventure Works example database for my tests here.