When integrating Excel Services spreadsheets into your dashboards you will want to connect any report filters used in live pivot tables up to the filters on your dashboard so that the dashboard filter can drive the pivot table filter. The series of steps below will guide you in setting up this scenario.
1. First, you need to set up your pivot table in Excel. Once your pivot table has been created you need to name any cell that contains a report filter that you want to link to a dashboard filter. Do this by selecting the cell and then typing in a name in the name box. In the below example, I click on cell B4 which contains my filter values and then name that cell GeoFilter.
2. Optionally select the cells that contain your pivot table and name this collection of cells – this allows you to only show this object in the dashboard.
3. Publish the file to Excel Services. Make sure to select the Excel Services Options button in the dialog to bring up the following screen.
4. On the Show tab, select which objects you wish to publish and on the Parameters tab make sure and add the filters that you named in step 1.
5. From Dashboard Designer create your report view. Selecting the SharePoint Site, Document Library and Excel Workbook. Once you select the workbook the Workbook Parameters will be automatically populated as long as step 4 was followed. Optionally enter the Item Name if you specified a name for the object you want included. Save and publish this view.
6. From Dashboard Designer add your new Excel Services report view to a dashboard and create a filter based on the same data as your Excel Services pivot table.
7. Add your filter to the dashboard. To connect the filter, drag the Member Unique Name from the filter to the Excel Services report view. In the link dialog, select the Parameter defined in the workbook to map the filter to:
8. Save, Publish and Export your dashboard. The Pivot Table should receive the filter value from a dashboard filter value change and update accordingly.
Alyson Powell Erwin (firstname.lastname@example.org)