Clear Pivot Cache with multiple tables on single connection

May 14, 2014 at 9:00 AM
Hi, in our project we are using pivot tables connected to OLAP sources. However, we have an issue with clearing the cache of pivot tables (users are not supposed to see the initial data, as they shouldn't have access to the entire cube).

I tried using the clear cache functionality in the extension, which worked well, but only when the table had its own data connection. This breaks our workbook as multiple tables/charts share common slicers, which in turn forces them to be on the same connection.

Is there no way to get around this limitation (clear the cache of a connection with multiple tables)?

Thanks!

Regards,
Egil Sørensen
Coordinator
Jun 27, 2014 at 12:08 PM
Edited Jun 27, 2014 at 12:08 PM
There was a technical reason I made sure the PivotTable had its own connection. Your slicer scenario is a good one though. I will revisit this code to see if there is another way than separate connections. I will post here if I am successful. Thanks for the feedback!
Jun 27, 2014 at 12:29 PM
Edited Jun 27, 2014 at 12:30 PM
No worries, I sidestepped the issue by creating a calculated member (MDX) with the value (null) and set the initial filter to this member. This cleared all the data for me, so that I was able to save the spreadsheet without data. A "hack", I know, but it got the job done.

Egil
Coordinator
Jun 27, 2014 at 9:24 PM
The other trick I've seen is to create a low privilege test account that has permissions to no dimension members. Then do a runas to launch Excel as that user. Refresh the workbook and all the PivotTables should be blank. Save it and you should be good.

What's the MDX of your calculated member? Was it some hack like the following? I'd love for you to share your trick with everyone.
IIf(Now() < CDate("6/28/2014"),Null,[Measures].[Your Measure])
Jun 30, 2014 at 12:29 PM
Hi, in our reports we have a global "filter" for organization (as in an organization tree). When the correct level of the organization is selected, the data for that unit should be loaded in the report. This is also the reason we could not save the report with "all" data, as people would get offline data they should not have access to.

What I did was to add a calculated member in excel under the Organization Dimension, set the parent to all and the MDX to simply:

(null)

When the calculated member is set to null, there will never be any data for this member. As this "filter" is global for us, it cleared all the data from the report when the slicer was set to this value.

How to make a filter apply over multiple tables/charts/sheets is another, yet more interesting "hack":

We created an empty pivot table, added Organization as filter and left it at that. Then we created a slicer on a separate sheet and connected it to the filter-table as well as all other tables/charts. The sheet was then hidden from the user. Now, when the filter is changed in the filter table (which only has the filter visible, so it looks like a simple filter), the hidden slicers where changed and all the corresponding tables and charts where updated in turn.

I should probably write a blog entry on how to do this, as it was not easy to figure this out... If I only had the time... :-)

Regards,
Egil
Thu at 10:24 AM
Hi,
are there any news about a solution in the OLAP-PT-Extension itself ... would be great since the existing "Clear Pivot Cache" is really great if it would also work with connections that have multiple items connected ... I would be also curious about the technical reason to force for the own connection.
Thanks,
Robert