SharePoint Dashboard Filtering Multiple Pivot Tables on Multiple Sheets within One Workbook

Guest Author: Tim Stalder
AEGON USA Investment Management, LLC

This is an update to Tim’s original post from September 2nd, 2009.

Update: 

The above procedure works only when your Sharepoint Server hasn’t been upgraded to SP2.  After we upgraded our servers to SP2 we lost the functionality as well.  Dashboards created prior to the upgrade still functioned correctly but we weren’t able to create new dashboards using the same steps listed out above.  After working with Microsoft to find another work around we did come up with the following fix that seems to work very well and seems to also take less time to implement.

Once you have your filter and your EWA set up and connection at the UI level then open it within Sharepoint Designer.  Proceed to your code tab and towards the top of your code you will see your IFilterValues.  This will be within <SPWebPartConnections><WebPartPages:SPWebPartConnection …  You will see your parameter name that you are currently connected to. 

Step 1: 

Copy from <WebPartPages:SPWebPartConnection … to </WebPartPages:SPWebPartConnection> then paste right below. 

Step 2: 

Once you have 2 of the same connections to the ConsumerID (ie: EWA) coming from the ProviderID (ie: Filter) then you need to change the ParameterName to your second tab parameter name. 

Step 3: 

Then you need to find the ID and increment that number by 1.  (This is important because each connection needs its own ID.) 

Once you have done this and saved within Sharepoint Designer then the 1 filter to multiple tabs within a single EWA should function for you.

This method seems to work very well when adding a lot of tabs that all consume the same filters.  I have tested this method on prior to Sharepoint SP2 upgrade and it does work as long as you don’t exceed the 200 connections limit.  Microsoft has tested this method on Sharepoint 2010 and has informed me that this method will continue to work under that version of Sharepoint.

I hope this helps.

Also I would like to acknowledge help on our latest research from Doug Wilkerson, Adv. Sr. Programmer Analyst with AEGON USA.  Doug worked with Microsoft to get them the Sharepoint Administrative needs that was needed to test this fix on multiple Sharepoint Server platforms.

Guest Author: Tim Stalder
AEGON USA Investment Management, LLC

Tim Stalder is a Sr. Programmer Analyst with AEGON USA located in Cedar Rapids, IA.  He is currently focused on the Sharepoint Dashboard development and the Microsoft BI Stack.  Tim has over 15 years technical experience ranging from Data Management, Data Processing, Report Writing, ETL and Web Development.