Developing a Cumulative Milestone Report (Part II)

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.

image_thumb[1]

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….

image

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.

image

Do the same for the second instance of each field, but this time also change the value to show as a Running Total. 

image

You should end up with something like this:

image

Your pivot table should now look like this…

image

Click on the pivot table and select the option in the ribbon to generate a pivot chart.  Choose a column chart.

image

The result will look like this…

image

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.

image

Pick the line chart option.

image

The chart may now look something like this…

image 

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.

image

The chart may now look something like this:

image

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.

image

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.

image

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.

Advertisements
Developing a Cumulative Milestone Report (Part II)

2 thoughts on “Developing a Cumulative Milestone Report (Part II)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s