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.
Add a connection from the Query String filter to the Excel Webpart.
…and I now have a filtered report showing project specific data.
…in tomorrow’s post….the same, but filtered by resource name.