Following Mike McLean and my triumphant debut as Project Server 2013 BI masters at the recent SharePoint conference, I wanted to document one of the demos – specifically the one where we filtered Excel reports automatically based on external parameters. I’ve more or less covered this before in Project Server 2010 and PerformancePoint, so if you’re looking for more information, please feel free to check out this link.
This post is focused specifically on implementing the same solution with the Excel Services Webparts, which are pretty much your obligatory go-to option when working in Project Online as PerformancePoint Webparts are not available.
On OData and OData Refresh
The main issue I ran into when trying to filter data sets was around the OData refresh issue. Specifically, as of the time of this writing, OData refresh is not supported in Excel Services. That’s not as big a deal as some might think, as I can always just open the Excel document to refresh it in the desktop….or if all else fails, write some VBA to cycle through my reports and refresh them on a daily basis.
Since I’ve already seen some discussion of this in the online forums, I’d point out that I’m pretty confident (as a non-Microsoft employee) that the fact that OData will not refresh in Excel Services is a temporary issue that will get resolved in the future. Needless to say, lack of online OData refresh is a significant obstacle to establishing parity between the online and on-prem reporting world.
Now how does that impact the specific use case we’re talking about today, i.e. the ability to filter Excel charts dynamically within SharePoint? As near as I can tell, we can still filter OData based reports – provided they’re not using a PowerPivot data model. My current hypothesis is that the filter mechanism somehow involves an interaction with PowerPivot which is not at present supported within Excel Services. Hence, to support this scenario, I’ll use vlookups and other Excel trickery rather than resorting to using PowerPivot and DAX expressions to build my data model.
As a disclaimer, I’ll point out that this post is all based on observations of how the tool works and are not based on in-depth technical analyses of the underpinning code. I’ll defer to the readers of this blog for that. I’d also stress that if you’re reading this post more than several months after it was posted, check the documentation to confirm my conclusions are still valid. If things should change, and I be so motivated, I’ll add a postback in the comments section to any updates.
Assembling the Data Set
Here you have a number of options. If you’re using an on-prem deployment, you could use the “traditional” method of creating Office Data Connection (ODC) files with embedded SQL queries. In this case, we simply create the data model as part of the SQL query. As far as I can tell, we can create reports from this, promote the parameter, and filter to our heart’s content.
In the interest of artificially creating a challenge, I’ll be using OData for my reports in this example.
First off, I am going to create a new report. Using my new OData skills, I fire up LINQPad and generate a URL to create a simple project work report. This URL will provide the assignment data:
…to add some difficulty, we can also pull in a field from the Project table.
As I mentioned in this post, I’m using an on-prem version of Project Server to generate and validate the URLs, then I’ll append everything from the …/_api bit onwards onto my O365 instance to ensure it works. In the case of the first one, that will look like this:
Creating the Data Model (Option 1 – No Filters)
I wrote this part of the post before I figured out what worked in the filtering mechanism and what didn’t. Since I had it written, I figured I’d include it for thoroughness and with the hope that it’s not too confusing. Note that this method will not work with the filtering mechanism I am demonstrating. It is an effective way of building reports however…..so if you don’t plan to filter your reports automatically using SharePoint filter Webparts, I’d definitely recommend using PowerPivot to create your data model.
Hopefully this should clarify:
Per one of the comments I got at SharePoint Conference, feel free to use OData in your on-prem reporting as well. Personally, I feel that since I’m comfortable in SQL, I’ll just continue using that – but it’s probably important to note that it’s available and useful in on-prem as well.
Anyway, I add both of those feeds into Excel 2013 and flip to PowerPivot to ensure they are joined properly. (Check some of my prior posts for more information on that.)
Now, back in the main Excel interface, I add a PivotTable based on the PowerPivot data model.
…which looks like this…
…to illustrate a second concept, I’ll go ahead and create a second pivot table that looks like this…
Turn them both into a bar chart 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.
…and well that’s pretty much it for this option. Upload to SharePoint to allow your users to click the slicers and filter the Excel reports manually. In my next post, I’ll talk about how to connect these (or rather similar) reports to SharePoint filter webparts to get that automatically filtered experience that we demo’d in Vegas a couple of weeks ago.