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:
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
, ", "
)
…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:
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:
Thanks. Great job!
Hi Chirss ,
I am a frequent reader of your posts . Could you help me in this scenario .
I am not able to figure out how to do it .
Could you please give me a solution for the problem . I already posted the problem in MSDN .Below is the URL of this .
http://social.msdn.microsoft.com/Forums/en/sqlanalysisservices/thread/edfdb901-bd82-412e-86b8-67ad855d2496
Any suggestions would be greatly appreciated . Thanks in advance .
Did Martin’s suggestion using FREEZE() solve your problem?
Yeah chirss , Thanks for your response . Freeze() has actually solved only half of my requirement .
I have replied to Martin’s answer . Could you please tell me if there is a solution for my problem .
Thanks in advance chirs .
See the above link or this http://social.msdn.microsoft.com/Forums/en/sqlanalysisservices/thread/edfdb901-bd82-412e-86b8-67ad855d2496
I’ll let Martin continue helping you – he’s more than competent and it would be rude to step in on his thread.
Chris, could you enable Onswipe plugin? TIA
I’ll think about it..!
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.
Hmm, it’s hard to say then, but my suspicion is that you’ve hit the maximum string length.
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
Can you send the whole of the MDX query that Excel is generating?
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
What do you mean by the ‘default items’ exactly?
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.
Do you need to use these slicers with a PivotTable or with Excel Cube Functions?
I need to use them with a PowerPivot Table.
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.
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.
Thanks, very helpful, you just save me 🙂
Hi Chris,
my Google search for “Slicer performance” brought me to your post – even though my questions doesn’t perfectly fit, it would be great to get your thoughts on it: do you have an idea how to improve performance when it comes to pivot tables containing multiple measures which run complex calculations? The problem is that slicers evaluate ALL measures to decide which elements should be shown (due to selection “Hide elements with no data” – an option that users like a lot) even though it would be enough to evaluate it for just one measure because all the others depend on this one.
Is there a way to achieve this or to decide which measure should be used to evaluate?
Thanks in advance!
There’s no way to choose which measures are used, unfortunately, you can only turn the hide elements behaviour off. One thing I have done is use cube formulas instead of a PivotTable and then (because cube formulas don’t hide elements in slicers) use a hidden PivotTable with one measure in to hide elements in the slicer.
Thanks a lot for your feedback!
Hi Chris,
NOT able to build this set. Any reason why?
Essentially use the slider set as the first argument of CROSSJOIN
=CUBESET(
“ThisWorkbookDataModel”,
“CROSSJOIN({” & Slicer_ZoneLabel & “}, [opcenter].[Op Center].Children)”,
“OpCentersCROSSJOIN”)
Thanks al lot for your respons!
Max