Apply a SET as an exclusion filter to a MDX query in SSRS

Ever have to remove a set of data from an MDX query when running a report?    Ever have SSRS and SSMS then reject its use, after getting the set defined in the cube to allow us to use it, because it did not have the same dimensionality as the rest of the query?    The answer for me is yes, and yes.   It would appear to be a straight forward operation, but, as with MDX, that is not always the case.  The use of the NOT IN operator when defining a MDX query parameter is very restrictive and can grief to the person coding towards it.

Here is what we would have expected the filter to be setup in the SSRS report query  (Note: we are using the query designer in SSRS to create our MDX query against the Analysis Services cube).   
 
Here we have a set of days that special sales are in effect that we wish to include from a report as it will throw of the trending of data for the product line.   


Calculation in the cube:
CREATE DYNAMIC SET CURRENTCUBE.[EventSalesDates]  AS
   '  EXISTS (  [Date].[Full Date].CHILDREN , [Event].[Event Id].CHILDREN,   "Sales"   )';  
 
However, this is what you will get if your set does not have the same dimensionality as the rest of the query.  
That sucks…  

A solution for you

Don’t give up however.   There is still an approach that you can use to allow you to perform the filter, just try some reverse logic.  You can exclude a set of data by explicitly providing the list of members (tuples, that is) that it can use.   Remember, that Analysis Services processes based on sets of data, not by rows and columns that you are used to with a relational database.   Rule of thumb.  If you cannot tell Analysis Services what it should not include, then tell it what it can.    
 
First, you will still need to define your set that you will need to filter out a set (as have done above).    Secondly, you will need to define a set that has what you can include, and use the first set with the excluded members defined to filter out what you want to not report on.          
 
Calculations in the cube:
Exclude set:
CREATE DYNAMIC SET CURRENTCUBE.[EventSalesDates]  AS
   '  EXISTS (  [Date].[Full Date].CHILDREN , [Event].[Event Id].CHILDREN,   "Sales"   )';  
Include set:
CREATE DYNAMIC SET CURRENTCUBE.[NonEventSalesDates]  AS
   ' EXCEPT(  [Date].[Full Date].CHILDREN , [EventSalesDates] )  ';  
Once you have the include set created, you can place a Query parameter on it.
 
The IN operator on the query parameter is far more forgiving that the NOT IN operator.    The effect is that the days of the special sale are not in include set and you place a query parameter on it.    I was not exactly dancing a jig after defining this, but was quite happy with what I was using.