In the last post, I reviewed a SQL query that may be added to an ODC file to generate the required data set. In this post, I’ll talk about how to configure the pivot table within Excel to generate a cumulative milestone report as depicted below.
Once you’ve got your ODC file configured with the SQL query in that last post, go ahead and create a new pivot table. Configure the pivot table so it appears as follows….
Note that I added each of the date fields twice to the Values section. We’re going to use one instance of the field for our bar chart, and the second instance of each field to track our running totals.
Click on the first instance of each field in the Values section, select Field Settings, and change the name to something more friendly.
Do the same for the second instance of each field, but this time also change the value to show as a Running Total.
You should end up with something like this:
Your pivot table should now look like this…
Click on the pivot table and select the option in the ribbon to generate a pivot chart. Choose a column chart.
The result will look like this…
Now we have to convert our cumulative values to a line chart and apply them to the secondary axis. To do this, click within the chart on the tall bars representing the cumulative values. Select the option to Change the Series Chart Type.
Pick the line chart option.
The chart may now look something like this…
Click on each line element, right click, and then select the option to Format the Data Series. Set the option to display on the secondary axis.
The chart may now look something like this:
Filter on the TotalDate field to identify the range you would like to see. I’ll focus on the months in the later part of the data set.
My conclusion from looking at this graph…starting in August 2011, we seemed to have a number of planned and baselined milestones that were never completed. As a result, a number of milestones were probably pushed out, as evidenced by the fact that my Cumulative Planned is greater than the Cumulative Baselined.
Even better…convert the report from a portfolio report to a project report by selecting a single project in the drop down list.
Not bad. Even some team members of a specific Twitter follower who shall not be named would agree that this is pretty easy. Toss it onto SharePoint and leverage the REST.API to automagically update the chart onto project reports.