SharePoint Conference Project Server BI Linkfest

Thanks everyone for coming out to #SPC171 just now, where Microsoft’s Mike McLean and I skimmed a rock over what’s new in Project Server 2013 BI.  Looking for more?  Well, you came to the right place.

As we mentioned, we’ve got a whole slew of demos that we couldn’t quite fit into this presentation, about 150 minutes worth.  Feel free to catch these recordings from the Project Conference last March.  As you’ll see, this was presented on Project Server 2010 – but they’re equally applicable to Project Server 2013 on-premises – and mostly applicable to Project Server 2013 in the cloud.

  1. Session #1: Intro to Project Server BI
  2. Session #2: Even More of the Same
  3. Special Bonus Track: the same presentation at TechEd in June

Additionally, here’s an inventory of reports, demos, and the step by step instructions to reproduce them all:

  1. Linkfest #1: General Stuff, ODC, Visio Services
  2. Linkfest #2: Excel Services & the REST API
  3. Linkfest #3: PerformancePoint and Fun with VBA

Looking for OData reporting against Project Server?   Stay tuned for more content, but for now, this should get you started…

  1. Reporting on OData with LINQPad
  2. Creating a Major Milestones Report
  3. Creating a Cumulative Milestone Report (Part 1, Part 2, Part 3)
  4. Maps, Maps, Maps

Maybe some general Project Server information is more to your liking?  Yep, we’ve got some of that too.

  1. Becoming a Project Server Administrator
  2. 10 Things SharePoint Admins Should Know About Project Server
  3. 10 Things Project Server Admins Should Know About SharePoint

…and last, but not least, here’s a white paper I wrote a while back talking about the options to report against Project Server 2010.  Stay tuned to this blog and others for more Project Server 2013 fun…

Advertisements
SharePoint Conference Project Server BI Linkfest

Project Server and the Obligatory Geospatial Report

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.

image

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.

image

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.

image

Go back to the normal Excel window and insert a PowerView sheet.

image

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.

image

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.

image

…and well, that’s pretty much it.  Add another table below the map to show a filtered list for clicking and drilling-down purposes.

image

…as you click on the dots on the map, the table below will filter.

I sense a new demo added to the repertoire.

Project Server and the Obligatory Geospatial Report

The Major Milestone Report Meets OData

I’ve been leveraging this report in my demos for a couple of years now – basically an overview of all of the major milestones in the project portfolio color coded by schedule status.  Figured I’d test my newfound OData skills and see how hard it would be to recreate in the new world of Project Server in the cloud.

image

Turns out not hard at all, with perhaps a minor caveat that we’ll need to modify the data model in PowerPivot.  If you’re looking at recreating this report in an on-premise instance, feel free to check out the instructions in my previous post.  This post is only on how to recreate the report using a cloud based instance and OData feeds.

To prepare for this report, we’ll need to create a custom field called “Major Milestone.”  This will be a task text field tied to a look up table.

image

Next, add the field to your project templates and map the project milestones to specific stage gates in the lookup table.

image

The following URL will  now generate the required data to generate the report.

http://demo/PWA//_api/ProjectData/Tasks()?$filter=MajorMilestone ne null&$select=ProjectId,ProjectName,MajorMilestone,TaskFinishDate,TaskFinishVariance,TaskPercentCompleted

Open the Other Connections option in the Excel Data tab, and paste the link into the OData feed option.

The only minor issue is that when you directly use the OData feed to generate the PivotTable, you can’t get the dates to render in the PivotTable properly.  As far as I can tell, when you select the “max” option in the PivotTable to display the date, you get the following error message: “We can’t summarize this field with Max because it’s not a support calculation for Date data types.”

image

Two solutions for this as far as I can tell:

1) Insert the data into a table, and then summarize the table into a Pivot Table – at which point, the date field rolls up just fine.

2) Go into the PowerPivot data model, and set the field in question to be a decimal number.

image

Insert the PivotTable using the PowerPivot table as a source, and you’re back in business.

Drop that into Excel and format per these instructions to get the report….

image

The Major Milestone Report Meets OData

Porting the Cumulative Milestone Report into OData (Part 3)

Now we have an Excel workbook with the appropriate data connections to Project Server….

image

Let’s break out PowerPivot to modify the data model.  Click on the Manage button in the PowerPivot tab.

image

You should see something that looks like this:

image

Rename the tabs to something a bit more useful.

image

Now let’s add a custom field to the Forecast and Baseline tables.  We’ll use this field to map the data back to the TimeSet table.

I used this formula for the Forecast table:

=[TaskFinishDate]-Day([TaskFinishDate])+1-TimeValue([TaskFinishDate])

..and name the new field “Total Date.”

image

I repeat the process for the Baseline data set, but use the TaskBaselineFinishDate value to generate my Total Date field.

image

Now let’s tie it all together.  In the Home tab, select the Diagram view.

image

Add the following joins to the data model.

image

If I select the option to Manage Relationships in the Design tab, I should see the following:

image

At this point, we simply need to add a pivot table to Excel and point it at our data model.

image

Our data model appears as a table in the External Connections list.

image

…from there, you need to add the appropriate fields to the pivot table.

image

Note that I added each of the date fields twice.  I’m going to flip the second instance of those fields to display a running total and give them a more user friendly name…

image

From here, it’s a matter of following these instructions to create a chart.

image

Not entirely sure the juice was worth the squeeze on this particular chart, but still a worthwhile exercise.  Add the slicer and timeline control for extra cred as an Excel 2013 master.

image

Porting the Cumulative Milestone Report into OData (Part 3)