Slicers - connect to two pivot tables with different data sources?

[L33]

Hi,
Can't believe this mistake in my idea took me so long to realise.

I have a list of "machines" a client has as one data source, and a list of "call outs" to those machines as another. I have pivot table pointed at each of them, and then several charts, dashboard elements etc informed by these two pivot tables.

Not all Machines will necessarily have Calls, so I have slicers pointed at the Machines pivot table (for example, that will capture all the different machine "types" even if there were zero calls). Both data sources share a huge number of 'dimension' data, but I've just realised as I'm trying to tie it together, that you can't connect slicers to pivot tables with different data sources no matter how many dimensions they might share.

Is there any hope? Is there any way I can make a slicer selection and have that control both pivot tables? (Both I and my users have Excel 2010).


Thanks in advance.

 

[brian.wethington]

There is an add-in available for 2010 that is called Power Pivot that allow you to do something like this as long as there is a relational table that would connect the 2 tables. Other than that, I would guess there is no way to connect 2 different data sets to the same slicer, but I am mostly going by the fact that I never was able to figure it out on my own. Perhaps I gave up too early on it.

 

[L33]

Thanks for response. PowerPivot is something I've finally gotten around to starting to learn how to use. I suspect you're right and this it would work, but I'm pretty sure all the users would need PowerPivot too, and that's not going to happen. The Excel model I'm building needs to work independently of it.

I think I have a couple of options:
1) Somehow via VBA get a list of all the active items from each slicer and then somehow feed those back in to a corresponding slicer from a second set pointing at my second pivot table

2) Somehow have a way to detect that any slicer is "in use", and if that's the case conditionally grey out the sections of my dashboard that don't get changed via slicers. (This isn't actually as disastrous as I first thought).

3) Ask my users very nicely if they wouldn't mind replicating every selection they've made in one set of slicers in a second set.

I'd rather avoid option 3.

Has anyone got any tips on how I go about achieving the other two?

 

[rossa]

Would the method used by Bill Jelen in this video be of use?
Learn Excel - Change Chart Type with Slicers - Podcast 1853 - YouTube

 

[L33]

Unless you're seeing an aspect of that method that I'm missing, then I don't think so, no.

 

[RoryA]

Your best bet will be to use code to synchronise the two pivot tables, using the PivotTableUpdate event. From what you're describing I don't think powerpivot will be any use to you for this.

 

[L33]

Thanks for that advice, Rory. I searched around and found this Contextures piece: http://blog.contextures.com/archives/2012/01/03/change-all-pivot-tables-with-one-selection/

Although it's aimed at synchronising the page fields of multiple pivot tables pointing at the same source data, I've been trying to adapt it to my requirements, and have had some success. I think I'm almost there - thanks for the pointer.

 

[L33]

I think I've done it!

That's the answer - the slicers are incidental, you don't do anything with those. You need to make sure all slicers have a corresponding page field for all your pivot tables (fairly easily done if you provide end users with a customised method to insert new slicers (which also allows you to restrict what they're allowed to insert)).

I did run in to trouble when trying to select an item in the first table that didn't exist in the selectable items of the corresponding page field of the second. Page fields must have at least one option selected, so I was ending up with a completely different item to that selected - just whatever was last in the list. But I believe I've got round that by ensuring there's always an entirely blank row included in the second pivot table's data source. That way the code I've amended slightly from Debra Dalgleish's Contexture's article cycles through the pivot items and lands on the "(blank)" option, so I still get the result I wanted.

Rory - very grateful to you for the guidance on that. Tight deadline on this and now my original idea is back on track thanks to this forum!

 

[RoryA]

Glad to help.

(Just seen Patrick's reply to your Tweet, so now I know who you are )

 

[Blunky]

Hi L33,

what you have done is something I need to replicate for a dashboard but I won't have the problem of not having the same values in each selectable item.

Ive got 0 VBA experience with a course coming up soon but I'll need to work this out before then for work. Can you help me out at all?

Read the contexture article and understand the logic behind it but no idea how to alter the code so it will work on a slicer.

Appreciate any assistance you can give me!

thanks