I have an Excel 2010 workbook where I have multiple pivot tables connecting to several SQL server 2008 analysis services cubes. I have created a slicer from a dimension in one of the cubes. This dimension is shared among all the cubes (a year/month dimension). However when I want to connect the slicer to all the pivot tables - only the pivot tables based on the cube the slicers was created from show up. Does anyone know why I can not connect the slicers to the pivot tables based on other cubes that share the same dimension?
Hi Abe, sorry it took some time to get back to you; I was testing different ideas.
So I think perhaps a workable solution is this:
1. go to Pivot Table Tools -> OLAP Tools -> Convert To Formulas. do this only for one of the pivot tables. in this way it cells will be converted to CUBE formulas
2. create a slicer for the first pivot table (the one you did *not* convert to forumulas)
3. Excel 2010 creates a reference to the slicer automatically, so you can use it in your CUBE formulas anywhere in the spreadsheet. So go to the 2nd pivot table (the one that is just formulas now) and add the extra parameter referencing the slicer, for example:
if the formula is:
and I added a slicer in the Color dimension to my first pivot table, then use:
Excel will show the names available when you type "slicer" in the formula, it will use the name "slicer_[dimension]"
in this way every time you change the value in the slicer it will now affect both the pivot table and the CUBE formulas referencing it by name.
I tested this with only two pivot tables connected to one cube, but I think it should work too with multiple cubes with one linked dimension as the reference in the slicer object points to the same cube member
Let me know that helps
Thank you for your help so far. I tried your solution. Although it works as long as one of the pivot tables is converted to formulas - the problem is that I need all the pivot tables to remain as pivot tables. Once they are converted to formulas, the rows will be static - and I need them to be dynamic, so unfortunately converting to formulas is not an option for me. Are there any other ways of getting around this?
By the way, the cubes are on a SQL server 2008 R1 server. Will upgrading to R2 fix this issue?
I don't think R2 will fix it; though a bit of a tedious solution to build, using the CUBERANKEDMEMBER functions will restore the dynmic feature you had with the pivot table. There is a really great video on how to use this function (as well as other cube functions) here:
Is not as easy as the simple drag & drop of pivot tables, but once you work though all the cube formulas you will have the same effect that you are looking for (dynamic member list and including the slicer in the formula as well)
Thanks. The video was very interesting. I see that using the cube functions gives me a possibility to create the dynamic reports I need. However I have workbooks with around 100 pivot tables. It becomes way to much work to create all of these manually with formulas. I think a better workaround may be if I create a slicer for each cube I connect to (there are 4 of them) - and then I assign a macro to one of the slicers. If the user changes this slicer, then the identical slicer selection is applied to the other three slicers through the macro. The three other slicers can be hidden so the user does not see them. I have not tested this yet, but in theory it should work.
I have one question though the fact that slicers can not be connected to shared dimensions from more than one cube must be a bug in Excel 2010 am I right? Can anyone confirm (I am worried that it is me doing something wrong here) ?
If it is a bug, it really limits the usability of slicers if you are not into coding vba...
I was wondering if you find any solution to your problem. I'm facing exactly the same inconvenience: multiple cubes, shared dimensions, single slicer to filter them all.
If you find out how to solve it, could you please post the solution?
Thanks a lot.
I just came up with the solution to this conundrum...
The key is that the PivotTables must share the same data source. By default, when you add a second PivotTable to a sheet, Excel will create a second instance of the same data source. What you need to do is click inside of the second pivottable, then on the ribbon, click "PivotTable Tools", then "Change Data Source". Click on the "Choose Connection..." button, and pick from the connections at the top in the "Connection in this workbook" group - the one you need is probably the first one.
Then click on your slicer. From the ribbon, click on "Slicer Tools", then "PivotTable Connections". Click the checkboxes as appropriate, and like magic, one slicer controls more than one PivotTable!
Hope that helps!
- Proposed as answer by DotNetDan Tuesday, January 11, 2011 11:06 PM
The suggestion to change the datasourceconnection only seems to work when the pivot tables connect to the same cube. This is not what the original issue was : 'multiple pivot tables connecting to several SQL server 2008 analysis services cubes'
So far no proper solution I think ?
This should be something that made possible in a next version ! This is a feature that can really bring the slicers to a usefull functioanlity for example to build up global dashboards in Excel.
As far as I know, this is unfortunately working as intended. It is not possible to connect the same slicer to different cubes.
I just tested this with Excel 2013, and it's the exact same behavior as in Excel 2010.
- Proposed as answer by Clive Tompkins Wednesday, April 10, 2013 12:00 PM
- Unproposed as answer by Clive Tompkins Wednesday, April 10, 2013 12:01 PM
- Proposed as answer by Clive Tompkins Wednesday, April 10, 2013 12:01 PM
- Unproposed as answer by Clive Tompkins Wednesday, April 10, 2013 12:03 PM
- Proposed as answer by Clive Tompkins Wednesday, April 10, 2013 12:13 PM
- Unproposed as answer by Clive Tompkins Wednesday, April 10, 2013 12:13 PM
Here is a basic solution, probably as Microsoft intended.
3 cubes in my Data Model, each containing different types of information about a group of Hotels.
I wanted to create a report with a single slicer that selected Hotel(s) from each of the cubes.
Hotel is a common field in each of the cubes. By adding a 4th cube, with a single column "Hotel", containing the unique Hotel names, I was able add a relationship to each of the 3 cubes.
Since all the cubes are now related, the slicer connecting to Hotel in the 4th cube works for all.
Essentially adding additional cubes for each slicer.
- Proposed as answer by Clive Tompkins Wednesday, April 10, 2013 12:15 PM