Returning Selected Items in an Excel Slicer Using MDX in PowerPivot and SSAS

One problem I came up against recently is how to find out what has been selected on an Excel slicer connected to SSAS or a PowerPivot model. There are a number of blog posts showing solutions to this problem, both for scenarios where only one item has been selected and when multiple items have been selected, for example (look at the comments as well as the posts themselves):
http://www.powerpivotpro.com/2010/12/another-way-to-get-and-use-slicer-values-in-formulas/
http://www.powerpivotpro.com/2011/09/catching-multiple-slicer-selections-in-a-formula/

…but I’ve come up with a new, MDX-based approach that handles the multiple selection scenario no matter how many items there are in the slicer, and which I thought was worth blogging about. I was tempted to include this in my series on MDX for PowerPivot but since it’s equally relevant for SSAS users, and the MDX is a bit complicated for an introductory series, I thought I’d make it into a standalone post.

To describe this technique I’m going to use same PowerPivot model I describe here, which is based on the AdventureWorks DW sample database. First of all, create a new PivotTable with CalendarYear in a slicer and CalendarYear on rows and any relevant measure on columns:

image

Selecting a Year in the slicer means that that Year appears on rows in the PivotTable, and selecting multiple years means multiple rows are displayed, as you would expect. Next you need to add a new MDX calculated measure to the PivotTable. If you’re using SSAS you can simply add the calculation onto your cube or you can use the OLAP PivotTable Extensions addin; for PowerPivot users the OLAP PivotTable Extensions approach is the only option, so that’s what I’ll demonstrate. Right-click inside the PivotTable and choose OLAP PivotTable Extensions from the right-click menu, and then in the Calculations tab create a new calculation called SelectedYears with the following definition:

Generate(
Except(
  Axis(1)
  , {[DimDate].[CalendarYear].Defaultmember}
)
, [DimDate].[CalendarYear].Currentmember.Name
, ", "
)

image

…and click Add to PivotTable. The result will be a new measure that returns a comma-delimited list of the names of everything selected on the rows axis of the query, and therefore everything selected in the slicer:

image

How does this work? Well, first of all Axis(1) is used to return the set used on the rows axis of the query used to populate the PivotTable (incidentally, this is why it’s important to have another measure in the PivotTable – if it’s not there, the structure of the query generated by Excel will be different and while the MDX can be altered to handle this, some of the items in the slicer will be partially greyed-out). The set returned by Axis(1) will include the All Member of the CalendarYear hierarchy, so the Except() function is used to remove it; finally, the Generate() function is used to iterate over this set and return the comma-delimited list of member names. In this example the CalendarYear field on the DimDate table in my PowerPivot model has become the MDX hierarchy with the unique name [DimDate].[CalendarYear]; please read this post for some background on how PowerPivot objects map to MDX objects.

Now you have the PivotTable you can refer to the top-right cell in it (in this example it’s cell F3)  in it to return the list of years and hide the PivotTable itself; this allows you to create dynamic titles like the following:

="Sales For Years: " & F3

You can then also create other, new PivotTables and hook them up to the original slicer and they will all work as normal:

 image

22 thoughts on “Returning Selected Items in an Excel Slicer Using MDX in PowerPivot and SSAS

  1. Hey Chris,

    great post, thanks a lot for sharing. Tried to reproduce that for my Needs – it is working in general, the only prob is that i seem to have many rows (around 800), which finally leads to an mdx error. Guess this is a timeout: “server: the operation was cancelled because of locking conflicts”.

    When i filter on just some rows, everything works perfectly fine.

    Maybe the MDX Needs some tweaks – do you have any idea how get this fixed?

    Thanks so much,
    Stefan

    • Hi Stefan, is there any processing happening on the server while you are browsing the cube? That would be one explanation for the error message. Otherwise, it could be that you are hitting the maximum length of a string in MDX, which would be very difficult to work around – but even if you could work around this, you’d be returning a string that would be too long for a user to make any sense of.

      • Thanks for your quick reply Chris! I am not Aware of any processing Happening on the Server while browsing the cube. The reason why i am keen on doing that is that i want to have a Semikolon seperated list of all row items – in my case email aliases, that i am going to use for a email Distribution list. The row items (mail aliases) are changing over time, that why i want to have an automated way to get to the Semikolon seperated list.

        Not sure, maybe there is also a way to do that in a power Pivot data model via dax, but i found your solution pretty cool as i wanted to extend my query with some slicers.

  2. hi,

    i am trying to use your solution to get the values of the slicer, i am using the same way but i am getting “Axis(1) could not be referenced”

    What should i do ?

    Regards,
    Your help is highly appreciated

    Georges

  3. Hi Chris,
    The above posts are really nice.
    I have a different scenario, i have an excel report connected to ssas cube. I created a slicer on the pivot. I want to list the default items of slicer in a cell. So could you please help me how can i do it?
    Thanks in advance

  4. Chris, I have two slicers, one is “From Year” and the Other is “To Year”. I would like to limit the choices on the “To Year” slicer based on the selection on the “From Year” slicer. In other words, prevent a user from choosing on the To Year slicer a year that is before the one selected on the From Year slicer. Please comment on possible solutions. Thank you.

      • There’s no way I know of to get fine grain control over what is available for selection in a slicer and what isn’t. Since the default behaviour is for the slicer item to be greyed out when there is no data associated with it in all connected PivotTables/Charts, what you need to do is make sure that the measures you are using in your PivotTables do not return any data for To Years that are less than From Years. This is very doable in both MDX and DAX, and is probably the better solution anyway because it will guarantee meaningful results are returned however the user constructs their query, whether they use slicers or filters or something else.

  5. Thanks for your reply Chris. I think I saw an example where what I am talking about was done but maybe it was the solution you are proposing and I did not scroll down to the bottom of the slicer to see if the “non-viable” solutions were greyed out.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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