Contributing author Laura Rogers recently received the following note from Tim Stalder regarding his solution to a SharePoint problem. We thought it was such a neat trick that we wanted to post for everyone to see.

 

Laura,

I first would like to thank you for taking the time to show interest in my find.  It will be interesting to see if you knew this one or if you would be willing to work with me to get it out to the masses.  I first would like to give you a brief description of what it was our client wanted and the limitations we had and then the work around that fixed this.

Business requirements:

Our customer wanted multiple Excel pivot table reports written off of a cube.  They wanted to be able to drive all reports with filters from a dashboard page.  They also wanted this Excel Workbook to have multiple worksheets each containing their own pivot table report and they wanted all of the reports to be driven by the dashboard.

Limitations:

What we found with SharePoint dashboards is that when developing the dashboard with the browser the SQL Filter can only be connected once to any named parameter on an EWA.  After numerous communications with Microsoft even they said they didn’t have an Out of the Box solution. So we had to tell our customer it wasn’t available for us to meet their needs. 

Trick:

After countless hours of asking myself why there isn’t a way to do this I finally found the solution.  I demonstrated my solution to Microsoft and even communicated directly to Shahar Prish, Steve Tullis, via email, and they said they’ve never seen it done like this.  So with that being said I would like to spell out my steps.

From http://social.msdn.microsoft.com/Forums/en-US/sharepointexcel/thread/72ac5463-e244-44fe-b98a-ad9edbae0dbb

Excel Step 1:

Define the parameter (Filter) names.  They need to be defined and named differently on each worksheet/pivot table.  To accomplish this go to Formulas on your Excel 2007 Ribbon and then Define Names on the cell (filter) you would like to name.

SharePoint Step 1:

Build your dashboard using the browser.  “DO NOT” connect any filters to the Excel Web Access at this point.

SharePoint Step 2:

Edit the new dashboard page, that you just created, in SharePoint Designer 2007.

Within SharePoint Designer connect the Filter webparts to the EWA webpart.  You will notice it doesn’t specify the parameter at the report level within the EWA. 
Save the dashboard once you have connected the filter to the Excel Web Access webpart.

SharePoint Step 3:

Now edit the dashboard at the browser level again.  Connect the filter web parts to the multiple named parameters (filters) that you created in Excel Step 1. 

As you will see the Filter webpart doesn’t show it is connected but when you go through the Connections off of the edit webpart dropdown it does show it is connected.
Go ahead and connect the filter webpart to the Excel Web Access twice, making sure that you select the different named parameters.

You need to be extremely methodic when doing this part, especially when you are doing many worksheets or pivot tables.  Make sure you name your parameters within the workbook so you can tell them apart.

Once you have completed connecting the filter webpart to the Excel Web Access webpart you will see that it is now connected to it twice.

Exit Edit Mode and there you have it 

You have now connected a SharePoint filter to a single workbook that contains multiple worksheets with multiple pivot tables and are driving all of the pivot table filters with one single filter from the SharePoint Dashboard.

So with all of this being said I am curious if you have ever done or heard about this feature?  I can put together screen shots for you but I need to build something off of Adventure works first.  This is something that I’m very excited about and would like to make it well known so Microsoft doesn’t close the gap with any future releases.

Tim Stalder,
AEGON USA Investment Management, LLC