• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Linked Slicers for OLAP and OLE DB

Misen

New Member
Hello all, I have a similar question to the one posed in the following posts, but my data sources are different.
https://chandoo.org/forum/threads/how-to-update-a-cube-slicer-based-on-user-input-vba.31942/

I started out with some source code from Mr.Excel and was able to duplicate his results by making a small mockup file but was unable to reproduce the results with the file getting data from two separate cubes. By that time I had added a bunch of debug statements to try and better understand the code.

Eventually, I realized that SlicerItems was not going to work, and I needed to use SlicerCacheLevel.
https://docs.microsoft.com/en-us/office/vba/api/excel.slicercache.sliceritems

Despite my efforts, I haven't been able to adjust the code to work. I have only dabbled in VBA, so any clarification on the differenced between sliceritems, slicercaches, and slicercachelevels would be greatly appreciated. Also any insight on modifying the code would be greatly appreciated.

Thanks,
Luke
 

Attachments

  • example2.xlsm
    27.1 KB · Views: 6
Hi ,

Rather than trying to tell forum members to adjust the code , can you explain your requirement ? What is it that you wish to do ?

I have understood that you have two data sets , and two pivot tables which work from those two data sets.

You also have two slicers each of which has a connection to its own pivot table.

Now , what is it that you want done ?

Narayan
 
In summary,
I am attempting to control two pivot tables with one slicer. I created example2.xlsm in an effort to confirm the code works, but upon inserting it into my business dashboard the code crashes. (This dashboard has two pivot tables generated from an OLAP and an OLE DB) From my current understanding, the cause of the crash is the source of the data. I believe that I need to adjust the code from slicercaches to slicercachelevels but have gotten tangled in the weeds along the way.
 
Why VBA?

In general I advise against controlling slicers via code, it's prone to error and requires more effort than what benefit it affords in majority of the cases.

It should rather, be set up using relationship(s) within data model, so that selection made in one slicer will control both pivots. This is especially true for OLAP based pivots.
 
See attached for basic set up using Data Relationship (available Excel 2013 and later), without using data model.
 

Attachments

  • example2.xlsm
    176 KB · Views: 6
It appears that I can not link the two slicers together by reporting a connection.

64060

So I attempted to recreate the pivot table and include the data in the Data Model, but the option was grayed out once I selected "Use an external data source."
64061

Am I missing something?
- Luke
 
You stated you created using OLAP model. So instead of creating pivot directly from connection. Load to data model and then create pivots based on the model.

Note that I don't usually recommend this approach, as slight change in source structure can break the model and needs to be built back from scratch. Better method is to first use Get & Transform (PowerQuery) to connect to the data and perform any necessary transformation. Then load to the model.

Provided, that you are using Excel 2013 or later.
 
Back
Top