5 thoughts on “Synchronising Slicers”

1. Hi Jan Karel. Nice work.

   I wrote some code over at the Contextures blog some time back for syncing pivots that set up a temp slicer to sync them if they shared the same cache, and otherwise used a Dictionary to store just the visible items of the ‘master’ field in that dictionary, so that those settings could be applied again and again to the various ‘slave’ pivots on other caches. Using a Dictionary approach on pivots with different caches was very fast in the case that you had thousands of PivotItems in your PivotFields and multiple pivots to sync, as you only had to iterate through the master field the one time to record just the visible items, and then could clear the slave field and then just hide any items in the slave that were NOT in the dictionary.

   Code is at http://blog.contextures.com/archives/2013/06/18/update-multiple-pivot-tables-20130618/ if you’re interested . Or a dictionary-only approach at http://blog.contextures.com/archives/2012/08/28/update-specific-pivot-tables-automatically/

   That code’s due for a refresh, as the bit that set up slicers (for pivots on the same cache) errors out if the user has already set up a slicer. Plus it strikes me that I can make it even faster if say I’ve got 3 pivots that share cache A and 3 pivots that share cache B, by joining up all the pivots in Cache A with slicers, then joining up all the pivots in cache B with slicers, then syncing just one pivot on cache A with just one pivot on cache B and let the slicers sync the rest.

   Will work it up and post here in due course.

2. Thanks Jeff, very useful to have two approaches to a very similar problem!

   NB: I’ve just added another page to the article about how to show the slicer selection in a cell:
   http://www.jkp-ads.com/articles/slicers05.asp

3. Jan Karel,

   In that article, you say that … The CUBERANKEDMEMBER function returns an array of selected items and hence needs to be array-entered into as many (vertical) cells as you expect will be selected in the slicer.

   That is not necessary, you can enter it non-array in G6 and just copy down (I really down like block-array formulas, a gotcha waiting to catch me).

   You can also hide the N/As with

   =IFERROR(CUBERANKEDMEMBER(“PowerPivot Data”,Slicer_Name,ROW()-ROW($G$5)),””)

   or count them with

   =IF(ROW()-ROW($G$5)<=CUBESETCOUNT(CUBESET("PowerPivot Data",Slicer_Name)),CUBERANKEDMEMBER("PowerPivot Data",Slicer_Name,ROW()-ROW($G$5)),"")

4. Thanks. Reminder to self: update article…

5. Article updated…

---

Posting code? Use <pre> tags for VBA and <code> tags for inline.