다음을 통해 공유

Connecting Dashboard Filters to Excel Services Pivot Table Report Filters

  • 아티클

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.

XLServicesFilter1

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.

XLServicesFilter2

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.

XLServicesFilter3

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.

XLServicesFilter4

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.

XLServicesFilter5

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:

XLServicesFilter6

8. Save, Publish and Export your dashboard. The Pivot Table should receive the filter value from a dashboard filter value change and update accordingly.

XLServicesFilter7

Alyson Powell Erwin (alysonp@microsoft.com)

Comments

  • Anonymous
    December 17, 2007
    PingBack from http://geeklectures.info/2007/12/17/connecting-dashboard-filters-to-excel-services-pivot-table-report-filters/

  • Anonymous
    March 20, 2008
    I am fortunate to have a colleagues that are contributing to my Blog. Time does not always permit me

  • Anonymous
    September 04, 2008
    How do you wire up a filter to the content on the Rows.

  • Anonymous
    January 12, 2011
    Thanks for nice and simple method. I would like to know if we can apply default filters to dashboard page. I have used SQL Server Analysis Services 2005 filter web part to pass filter values to spreadsheet. Filters are for Date dimension. its working fine for single value. but when date gets changed i need to use changed member (Current Date) as filter value. Can you please give any suggestions?

  • Anonymous
    March 19, 2014
    Can you please share the image files

  • Anonymous
    March 25, 2014
    Hi is the data displaying from in the reports as per the logged user role..?

  • Anonymous
    July 27, 2014
    The images are broken on the blog. Can you add the images?