PerformancePoint - Filtering an Excel Workbook with Multiple Pivot Tables

Question

In PPS I have an Excel Services workbook with four pivot tables from the same cube. Each has a filter called 'Week'. Eash Week filter has been set up with a range name. I have a filter set up in PPS using the same cube to filter on the week. PPS allows me to connect one pivot filter as Member Unique Name, but will not allow me to connect the other pivot filter, even though each has a different name.

I tried using an Excel 2010 slicer that I connected to all the pivot tables. Then connecting the PPS filter to the slicer. PPS allows the connection, but it doesn't work.

A friend passed me a post from this site about doing this with a Sharepoint dashboard, but it requires getting into the html of the EWA web part to create the multiple connections. I can't figure out how to do that using PPS web parts. http://social.msdn.microsoft.com/Forums/en-US/sharepointexcel/thread/72ac5463-e244-44fe-b98a-ad9edbae0dbb/

Any ideas on how to do this in PPS?

Thanks!

Cathy

---

C Sherrill

Answer 1

I'm trying to do the same thing, and I have yet to get it to work.  I can attach the PPS filter to an excel services powerpivot workbook pivot table FILTER, but not the slicer.

Has anyone had luck with connecting a PPS filter to a Slicer via the PPS Excel Services report type?

thanks,

Answer 2

I figured out a work around.

If you create your powerpivot workbook, and instead of putting an attribute in the vertical or horizontal slicer, you click on the "insert > Slicer" option from the ribbon, you can then add that SAME attribute to the report filter.    This LINKS the report filter and slicer.

1. Now all you have to do is highlight the cell that is the pivot table filter and create a named range, under the formula tab on the ribbon (use 'New...' option under same manager, not define name, it seems to work better).  Optionally you can hide the 'report filter' so the user will ONLY see the slicer (they are linked so it would be redundant to have both anyway)
2. Then publish this to sharepoint, and in your publish options, make sure you 'Add' the parameter, so you include your newly defined NAME.
3. This will then show up as a workbook parameter in PPS when you create your Excel Services Report type.
4. When you add to the dashboard, make your connection as always, just select the parameter and your off and running!

Answer 3

HA! Now that is clever!

I tried connecting to the slicer and it didn't work. But when I followed your instructions and connected to the FILTER of one of the pivot tables via the named range, it worked. Changed the filter on BOTH pivot tables and the slicer.

So basically, the slicer acts like a connector for the pivot table filters and isn't needed in the PPS dashboard. I can hide the slicer and the whole workbook will filter based on the PPS filter.

Nice!

---

C Sherrill

Answer 4

Glad it worked, the slicer becomes a conduit in this case :)