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…

image_thumb1[1]

 

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

http://demo/PWA//_api/ProjectData/Assignments()?$select=ProjectName,ProjectId,ResourceName,AssignmentWork

…and this…

http://demo/PWA//_api/ProjectData/Projects()?$select=ProjectId,EnterpriseProjectTypeName

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

image

..which yields a workbook with two tables.

image

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

image

…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…

image16[1]

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.

image32[1]

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.

image_thumb13

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.

image28[1]

I configure the Excel Webpart to display the Resource Report.

image36[1]

…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.

image40[1]

Add a connection from the Query String filter to the Excel Webpart.

image44[1]

…and I now have a filtered report showing project specific data.

image

…in tomorrow’s post….the same, but filtered by resource name.

Advertisements
Filtering Excel WebParts in Office 365 (Part 2)

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

  1. Daniele says:

    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.”

    Did you test it? Any suggestion?
    Thanks
    Regards
    Daniele

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s