Author: Nate Auchter

Nate Auchter, MCTS, MCP, a principal consultant at Sensei Project Solutions, is passionate about leveraging Microsoft's project and portfolio management and collaboration and communication tools to help customers realize the huge benefits that these great tools and platforms have to offer. He is skilled and certified in the installation, configuration and architecture of the SharePoint and Project Server platforms. He helps organizations focus on business process automation, demand management, and production of high-quality reports to display business insights. Nate is expert at distilling how Microsoft's suite of products and new features provide value. He has presented at SharePoint community events all over the west coast and is an active MPUG member.

Traffic lights

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:///$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.

Timeline image

Microsoft Project 2016 Preview: Multiple Timelines

We often encounter project managers and stakeholders who need more customization to their timeline views. Before now it was impossible using the native project views and tools. We’re happy to announce that with the release of Microsoft Project 2016 Preview, we have much more control of our timeline views! NOTE! This blog post introduces features that are included in Microsoft Project 2016 Preview. This software is now available to Office 365 subscribers with Project Pro for Office 365 licenses. For more information visit If you didn’t know, you can access the timeline from the View tab of the ribbon in the split view section. (You can also save specific timeline views and pick them from your Timeline view menu here as well, but we’ll save that info for a future article.) In the example below, I’ve started with a PMBOK-compliant project management template (which can be found in your out-of-the-box templates) on the “new” project page. I then condensed the outline view to level 2 and added all tasks to the timeline. You can see there are many tasks and milestones in the schedule that will appear in the timeline. The view is messy, and we can’t really tell what exactly is going on, as there are a lot of concurrent activities and “phases.” To start to clean up the view and provide some insight into our schedule, I’ll begin by adding a few additional timelines to the view. This is done on the format tab (timeline tools) from within a timeline. Click the “Timeline Bar” button in the “Insert” section to add a blank timeline. I’ve now added three additional timelines to the view. At this point it’s just a matter of dragging and dropping the tasks I want to move. I’ve decided that I want one “main timeline” that shows the overarching phases of the project, and then I want to break down each phase into key tasks to call attention to. My altered view now looks like the image below. This is a great start; however, some of these summary tasks aren’t needed in the view. Perhaps they don’t need the attention on the timeline, so I’ll remove them and begin to focus on the tasks that I care about. I think it’s starting to look pretty good, but everything is light blue and not visually distinct enough, so let’s add some color. Colored bars can be created by right-clicking the bar and then selecting the fill color we want. Great. Now just one final touch. Because I don’t need all that white space in each of my new timelines, I can change the date range of each timeline to reflect the dates I need. I first need to select my timeline bar, then on the Format tab (Timeline Tools), in the Show/Hide section, click the Date Range button. (Note that if I don’t select a timeline bar first, the button will appear disabled.) On the pop-up dialog, we can set start and end dates for each of our timelines. If we don’t specify, the timeline will use the start and end date for the project, so we can simply shorten or hide specific tasks or milestones if we want. So with a quick modification to the other timeline date ranges, we now have the view shown below. Now with my finished view I can use the “Copy Timeline” button on the Format Tab to copy the set of timelines to other Office applications. Or I can simply select one of the timelines and copy that singular timeline to a PowerPoint slide or some other document. There are quite a few other view options that we have on the Format tab, including specifying how many lines of text we want to show (in case we need to wrap text), changing from a bar to a callout and others. One other great use of this functionality is to show multiple projects at once. I’ll save this project as Project X, then I’ll go to my Project Center (within Project Web App) and open up Project X, Project Y and Project Z all at once in a temporary master project. I’ve added all the summary tasks and milestones that I care about to the timeline here and color-coded them to allow me to see how these projects overlap. Project X is colored yellow, Project Y is colored orange and Project Z is colored light blue. Now with the same process as above I can reorganize the tasks onto multiple timelines and change bars to callouts or remove or add tasks and milestones if I wish. Ultimately, I end up with a great view of these three projects, their summary tasks and milestones. I can clearly see where they overlap, and make decisions around project planning and resource capacity management using this view as an aid. I hope you’ve found this article useful in detailing one of the new great features of Microsoft Project 2016 Preview! Photo: Cropped and resized from an image made courtesy of Gord Fynes; shared through a Creative Commons license (CC BY-ND 2.0).

Webinar: Resource Workload Leveling

Project Management Institute (PMI)® Professional Development Units (PDUs): This Webinar is eligible for 1 PMI® PDU in the Technical Project Management talent triangle category. If you are claiming this session, you must submit it to your MPUG Webinar History after it has been completed in its entirety. Event Description: Resource leveling in Microsoft Project is probably one of the most misunderstood features available, yet it is one of the most important tools in the proactive project manager’s tool kit. In a nutshell, it is a process that ensures resource demand does not exceed resource availability. It schedules work according to priorities on a single project or across multiple projects and ensures that individual resources are not over-allocated (or minimizes over-allocation, at least). In this webinar you will learn what the pre-requisites to resource workload leveling are and how to use it properly on a single project or across multiple projects. Speaker Bio: Nate Auchter, MCTS, MCP is passionate about leveraging Microsoft’s PPM Project and Portfolio Management, Collaboration and Communication tools to help our customers realize the huge benefits that these great tools and platforms have to offer.  He is skilled and certified in the installation, configuration and architecture of the SharePoint and Project Server platforms. He helps organizations focus on business process automation, demand management, and producing high-quality reports to clearly display the business insights that the new Project can deliver to their organization.  Nate is expert at distilling how Microsoft’s suite of products and new features provide value to Sensei’s customers.  He has presented at SharePoint Community events all over the west coast, and is an active MPUG member. Have you watched this webinar recording? Tell MPUG viewers what you think! [WPCR_INSERT]