Build KPIs with Project Online and Excel 2013

This article illustrates how easy it is to use a built-in Microsoft font as a set of graphical indicators within an Excel report to show key performance indicator (KPI) values for your projects.

We’ll use OData and Excel 2013 to create a simple report based on an easy-to-read “stoplight” view detailing the performance of the portfolio. To do this, we’ll retrieve “Project KPI” custom field values with OData, convert those values within the PowerPivot data model, then add a bit of conditional formatting to color the new icons.

Typically, KPIs are derived from numeric values that vary from a set goal. We’re going to focus on a simple, subjective KPI — our “Project KPI” — that’s a custom field developed for our environment. This custom field exists to allow the project manager to provide a subjective opinion on the current project status. It’s attached to a lookup table with three values:

• On Track
• On Watch
• Troubled

These same principles could be applied to typical calculated KPIs or KPIs that show green, yellow or red in Project Center by comparing a numeric value to a threshold.

While it’s possible to create the conditional formatting rules based on graphical indicators built into Excel, these green, yellow and red circles aren’t as customizable as a report author may like, and the sets provided are somewhat limited. By using the Wingdings fonts, however, we have access to a great many more shapes, and we can control the colors through our conditional formatting rules.

Follow this step-by-step process to create a quick but visually appealing report.

Retrieve your Custom Field KPI Values with OData

Our first step will be to construct a query to retrieve project level data from Project Online. Here we want to retrieve certain fields that will allow us to build a pivot table that shows Project Names grouped by Project Department and includes other columns of project data, such as % Complete, Start Date, Finish Date, Project Owner Name and, of course, our subjective Project KPI.

1. Open Excel 2013 and on the Data tab, click From Other Sources then From OData Feed.

2. Use the format for your Project Online tenant Project Data service. It may look like the following:

3. Next append the OData query to this:

_api/ProjectData/Projects()?$select=ProjectName,ProjectDepartments,ProjectPercentCompleted,ProjectOwnerName,ProjectStartDate,ProjectFinishDate,ProjectKPI

4. Our entire query is:

https:///YOURCOMPANY.sharepoint.com/sites/pwa/_api/ProjectData/Projects()?$select=ProjectName,ProjectDepartments,ProjectPercentCompleted,ProjectOwnerName,ProjectStartDate,ProjectFinishDate,ProjectKPI

5. Enter the full URL in the Location of the data feed box and click Next.

Then select the Projects table, and click Next.

6. Provide a file name and friendly name for the Data Connection and click Finish. Excel will initially save the Data Connection as a local file; in the next step we’ll change this to an embedded data feed.

7. On the next dialog, click Properties. We’re going to modify the data connection so that it becomes a data feed embedded in the report, instead of a local data connection file.

8. On the Definition tab, the easiest way to alter the data connection so that it becomes embedded in the report is to change the last digit of the Max Received Message Size integer to a 0 instead of the 4. Then click OK.

9. On the pop-up dialog about the workbook connection, click Yes. We’re confirming that we want to break the linkage between the local file and the report.

10. On the final dialog, click Only Create Connection. Then click OK. At this point the data is now being downloaded to the PowerPivot data model within the workbook. Nothing will appear on the main worksheet; however, the data will show in a table within the data model.

Now that we have our data, we need to customize our data model with a new column that will hold the values for our KPIs. We know that in our Project Online tenant, the Project KPI has three values (and blank): “On Track,” “On Watch” and “Troubled.” We’ll use a nested IF function within our data model to provide a single letter value for each of the three possible Project KPI values. We’re then going to convert the letter values to graphical indicators using the Wingdings font.

Modify the Data Model

11. In Excel, on the PowerPivot tab, click Manage within the Data Model section.

Note: If you haven’t enabled the PowerPivot add-in, you’ll need to go to File, Options, Add-ins, select COM Add-ins at the bottom of the dialog, then enable the Microsoft Office PowerPivot for Excel 2013.

12. We’re now going to add a calculated column with the following formula:

=IF([ProjectKPI]=”On Track”,”l”,IF([ProjectKPI]=”On Watch”,”n”,IF([ProjectKPI]=”Troubled”,”u”,”o”)))

This formula will turn the Project KPI string values into shapes when using the Wingdings font. Click into the column to the right of the columns that are being retrieved from the query and plug in the formula.

If desired, rename the column to something a bit friendlier, such as “KPI.”

14. Click the PivotTable button to insert a pivot table in the workbook.

15. Choose Existing Worksheet. Place the pivot table where you’d like.

Create the Report

Now that we have our data and our new KPI values, we can build the report. Drag and drop the fields that you’d like from the field well to the rows area of the PivotTable fields pane.

16. Group the Project Name by Project Department, then have the rest of the fields display in tabular form.

17. Add the Project Department field to the pivot table rows first, and for every field we add after the Project Department field, click the field, and then field settings. On the Layout & Print tab, change the field to Show item labels in tabular form. Here we can see that I’ve added (in this order) ProjectDepartments (outline form), ProjectName (tabular form), ProjectOwnerName (relabeled OWNER, tabular form), ProjectPercentCompleted (relabeled % COMPLETE, tabular form), ProjectStartDate (relabeled START DATE, tabular form), ProjectFinishDate (relabeled FINISH DATE, tabular form), and finally our calculated column KPI (tabular form).

18. After all fields have been added and adjusted, remove the subtotals by clicking Do Not Show Subtotals from the Subtotals menu on the Design tab of the PivotTable Tools ribbon.

19. We’ve turned off Grand Totals (on the Design Tab) and the Field List and +/- buttons on the Analyze Tab.

20. Then we add a bit of formatting to the pivot table and reformat the dates. Collapsing and expanding the groupings of projects by department is still possible by double-clicking the grouping bar.

After making a few more changes, the report may start to look like the report below.

21. The last few steps focus on the KPI column. We need to change the font for that column to Wingdings.

22. Finally we’ll add conditional formatting rules for the KPI column. (Note that I’ve inserted an extra blank column to the left of the pivot table, column A.) Highlight your column of indicators; if you haven’t inserted the extra column, it will be column F.

23. From the Home tab, click Conditional Formatting, then New Rule.

Create the new rule by clicking Format only cells that contain, then set the drop down menu for the test to equal to, and start with the first test. We want the font color to be green if the value is “l” (for on track). Click the format button to set the font color and click OK.

24. Add the other two rules and your final Rules Manager should look similar to this dialog.

25. After that, it’s just a matter of adding slicers or other Excel functionality that would be useful for a person viewing the report to get to the data they care about quickly.

Creating reports with OData and Excel for Project Online is fairly fast and easy. With such a versatile tool, you can develop great charts and tables that use live data and communicate project status efficiently.

Traffic light image by Fin Fahey, cropped and transformed under a Creative Commons CC BY-SA 2.0 license.