Well, a great first day at SharePoint Conference already. Yesterday, I attended Rafal Lukawiecki’s entertaining pre-conference session on the latest in Microsoft BI where he walked through many of the latest and greatest offerings, and included a demo of the new PowerView map report. His prediction was that every BI demo at the conference would likely include that specific use case, as apparently in this day and age, BI = maps.
Well, ever one to be a trend follower, I figured I’d contribute my part and offer you this Project Server example.
Before going much farther, I must point out that I’ve never been enthusiastic about creating map reports out of Project Server data. I’m not against those sort of representations, and neither are my clients. In fact, most of my clients have large GIS departments in charge of such things. I just generally have not seen much value from a project management perspective in adding latitude and longitude to project data, and then plotting the project on a map. Well, with PowerView, and the ease of creating a geographic report, I might just be tempted to change my mind on this.
So to create the data, I added a custom field in Project Server called “Office.” This field represents the geographic office that will own each of the projects in my portfolio.
From there, I open Excel 2013 and add a data connection either directly back to SQL or through an OData feed using the following URL: http://demo/PWA//_api/ProjectData/Projects()?$filter=Office ne null&$select=ProjectName,TotalCost,Office
Next, I navigate to the PowerPivot manager. If it’s not visible, ensure it’s activated as a COM add in in the Excel options menu. Click on the Office column, select the Advanced tab, and note that you can classify the data as “City.” This is part of a new host of features where Excel can actually classify your data and recommend actions based on the specific data type.
Go back to the normal Excel window and insert a PowerView sheet.
From here, it’s pretty easy. Select the fields you want to appear in your PowerView report. Note the globe icon next to our Office field indicating that it is in fact geographic data.
Click on the newly created table, and then select the map option in the top left. This transmits the Office column data to Bing and reconciles it with an online map.
…and well, that’s pretty much it. Add another table below the map to show a filtered list for clicking and drilling-down purposes.
…as you click on the dots on the map, the table below will filter.
I sense a new demo added to the repertoire.