Well, I seem to be on a bit of a BI tear. Not sure why, but based on past experience, once I start playing with reporting ideas, that generally leads to other ideas, and then I end up with a half dozen random reporting posts all in a row. Oddly enough, that seems to happen annually right around the Discovery Channel Shark Week promotion. Maybe there’s something about sharks that prompts me to ruminate on project data representation.
I haven’t yet done much with Excel based reporting on this blog, so I figured it was about time to set my sights on it and to see if I could generate a portfolio milestone report. This report is a common request that I’ve seen from customers and on the InterWebs, i.e. how to create a simple milestone report for a project program or portfolio.
The goal of the endeavor is to display the dates for predefined key milestones or stage gates, and then to provide some indication as to whether or not that stage gate is on schedule. Essentially, the report will give a quick view at a glance of key milestones within a specific portfolio of projects. Building off of this post I did a couple of weeks ago on developing a portfolio timeline, here’s a quick and easy report to display key milestones in Excel.
The assumption is that the organization has several main stage gates. In this case, I am basing the report off of four stage gates, but it wouldn’t be difficult to accommodate more.
Enterprise Custom Fields
After playing with a number of different options, I realized that the easiest and most elegant solution simply requires a single enterprise custom field attached to a lookup table.
First, I create the lookup table with the stage gate names.
Then I create a custom task level text field married to the lookup table. Set the default value to blank.
That’s pretty much it for the Project Server side of things. To create some sample data, I created a couple of projects and flagged the stage gates in each schedule. The idea is that you would do this with your own stage gate milestones.
(And props to Victor for pointing out that my project illustration is all wrong and that Stage Gate 1 really should come before Phase 1, with Stage Gate 2 authorizing Phase 2 and so on and so forth. I’m still too lazy to actually fix the illustration, but please note the procedural point.)
Generate the SQL Query
Rather than add a whole slew of custom fields to roll these values up to the Project Center, we can just use one of the Task tables in the Reporting database to pull all of the tasks identified as stage gates.
Thus, our next step is to generate the SQL query that we will use to pull data into Excel. Since I am not much of a SQL hand, I create a new custom view in the SQL Management Studio to generate the query, which I then add to an ODC file. Here’s a previous post on creating custom SQL views. Note that you don’t have to save the view, but just use it to create the query.
In this case, I’ll be combining two tables: the EPMTask_UserView and the EPMProject_UserView. I add a join on the ProjectUID field, and then make sure to include the new custom Stage Gate field as well as the TaskFinishVariance and the TaskFinishDate. From the Project table, I include the ProjectName field.
At the bottom of the screen, you’ll see the SQL query generated, which in my example looks something like this…
SELECT dbo.MSP_EpmProject_UserView.ProjectName, dbo.MSP_EpmTask_UserView.TaskName, dbo.MSP_EpmTask_UserView.TaskFinishDate,
dbo.MSP_EpmTask_UserView.TaskPercentCompleted, dbo.MSP_EpmTask_UserView.TaskFinishVariance, dbo.MSP_EpmTask_UserView.[Stage Gate]
FROM dbo.MSP_EpmProject_UserView RIGHT OUTER JOIN
dbo.MSP_EpmTask_UserView ON dbo.MSP_EpmProject_UserView.ProjectUID = dbo.MSP_EpmTask_UserView.ProjectUID
WHERE (dbo.MSP_EpmTask_UserView.[Stage Gate] <> N'””‘)
…don’t forget to filter on the Stage Gate field to exclude all tasks where Stage Gate is null or blank.
Create the ODC File
In order to get this data into Excel, we’ll need to generate an Office Data Connection file. We could create one from scratch in Excel, but I kept running into error messages when doing so. Rather than bothering to troubleshoot these issues, I just grabbed one from the BI Center and downloaded it to my desktop.
Double click on the ODC file to launch Excel. From the Data tab, select the Connections option. Then select the Properties button to edit the connection.
Give the connection a more descriptive name. Then click on the Definition tab.
In the Definition tab, delete the Command Text, and swap it out with your SQL query.
Lastly, click the option to Export the Connection File at the bottom to export the new ODC to your desktop. Either leave it on your local machine or upload it to the BI Center for use in future reports.
Create the Excel Report
Open a new Excel sheet. Insert a PivotTable and choose your new ODC as the data source.
Configure the PivotTable as in the illustration below…
Now let’s add the Finish Date field to the mix. Drag that field to the Values section. You’ll see that it defaults to a count of the finish dates. Click on the drop down next to the field and select the option to edit the Value Field Settings.
Change the field to display the maximum value and give it a more user friendly name.
Format the cells to display a date, and you should have something that looks like this…
Now let’s add the Finish Variance data to the mix. The field is stored in SQL in terms of hours, so we’ll need to divide by 8 to get the appropriate number of days. To do this, click on the PivotTable, and from the Options tab, select the option to create a new calculated field.
Configure the field as below and click on the Add button.
That results in a table that looks like this…
Format the Variance cells as integers. (Tip: Hold down Ctrl and select all of the cells with your mouse). Remove the Grand Total and Subtotals while you’re at it.
Highlight the Variance cells again and add Conditional Formatting. In this case, I’ll choose an icon set. Edit the rule so it looks as follows:
Go back to your Excel table and it should look like this:
…still not quite beautiful enough to put in front of an executive, but at this point, it’s just a matter of finagling the report in Excel to get it to look the way you would like it to.
Add a new project? Simply right click on the PivotTable and select the Refresh option to update it with the latest and greatest information.