In this post I’ll show how you can create query string parameters in SharePoint (on-premise or online) to filter an Excel 2013 pivot table appearing in the Excel web part. This is a really useful feature if you want to provide a link to a SharePoint page that will set pivot table slicers based on a value you provide in the query string.
Creating the Excel Workbook
First we need some data to play with. I’ve used Power Query to search for sample ‘movie’ data and chose the first result that came back in the search; “Notable deaths – 2011 in film”. Here’s my data:
Now I create a pivot table from the data itself by selecting the table and clicking the PivotTable button in the ribbon. I’ve also added a “Nationality” slicer to the pivot table. We’ll use this to filter the data by nationality through the query string.
Before uploading to SharePoint head to the File tab (in Excel 2013) and click the “Browser View Options”.
In the Browser View Options click on the Parameters tab.
Then click the “Add…” button and add the Slicer_Nationality as a parameter.
Now let’s save the document and upload to a SharePoint document library.
Filtering in SharePoint using the Query String
In SharePoint let’s create a new page and add the Excel Web Access web part and configure it to display our workbook:
Now add a Query String (URL) Filter web part to the page.
Next, we’ll open the tool pane to configure the Query String (URL) Filter web part. Here I’ve used the query string parameter “Nationality” with a default value of “American”. In the advanced filter options I’ve configured the web part to “Send all values” so I can provide multiple values to the pivot table slicer.
The last thing we need to do is to hook up the Query String (URL) web part to the Excel Web Access web part. In the Excel Web Access web part choose Connections->Get Filter Values From->Query String (URL) Filter. You may need to select the “Edit Web Part” for this option to appear.
In the dialog configure the connection settings to filter the Slicer_Nationality parameter in the Excel workbook:
Save the page and note that the pivot table automatically defaults to “American” in the Nationality slicer (I set this as my default value in the Query String Filter web part):
I can also change the slicer using the query string by adding ?Nationality=Canadian
Gives…
Multiple values are also possible. A query string of ?Nationality=French&Nationality=German results in both selections being made in the slicer.
Note that specifying a value for the Nationality name value pair that does not exist in the slicer results in an error:
I should also be possible to use the Current User Filter web part to provide the current user to an Excel parameter in order to provide user-specific filtering in an Excel Web Part document. I’ll cover this in a future blog.
