Blogroll

Filtering Excel WebParts in Office 365 (Part 2)

In this post, I’ll continue discussing how to promote parameters into SharePoint and then automatically filter reports using the SharePoint Filter Webparts. Note that this solution should pretty much work both in on-prem and online – with the caveat that you might want to construct your data model differently based on the solution you’re using. Check the last post for guidance on that (and check the bottom of this post to see if things might have changed since I wrote it).

Creating the Data Model (Option 2 – With Filters)

See the chart below to (hopefully) alleviate any confusion…

Picking up from the last post, I have two OData connections that I will add to Excel.

This time, when I add them to the Excel workbook, I select the option to simply add them as tables.

..which yields a workbook with two tables.

Instead of using PowerPivot, I can then move fields between the tables using a vlookup formula.

…then I create a PivotTable from the table itself by selecting the option to “Summarize in a PivotTable.” From here, it’s simply a matter of turning the table into a couple bar charts to get something that looks like this…

I’m now going to add a slicer to each chart. I’ll add a ProjectID slicer to the top chart and a Resource Name slicer to the bottom chart. Click on the slicers to validate the results. At this point, I’d also name the charts to ensure we display the correct ones.

I load this report to SharePoint. When I save to SharePoint, I select the browser options button and configure both of the slicers as parameters.

Displaying in SharePoint

After uploading to SharePoint, we now can display it. In the first case, we’ll add our Resource Report to a PDP that I’ve created called the Project Dashboard. I add two webparts….one Query String Webpart and one Excel Webpart.

I configure the Excel Webpart to display the Resource Report.

…then I configure the Query String Webpart to pull the ProjUID parameter from the URL. The ProjUID is a 36 character identified for each of the projects in the system.

3 thoughts on “Filtering Excel WebParts in Office 365 (Part 2)”

Hi Andrew,
to see updated data opening the PDP, you need to activate “Refresh data when open the file”, and with this option, the parameter filtering doesn’t work with a pop-up error: “Error while setting one or more parameter in the worksheet.. etc.”