Baseline and Work Comparison/”Burn-up” Report

Wasn’t it nice of MS to create a lovely AssignmentByDay_UserView for their users?  I mean, I think so.  And it was terribly nice of them to include the baselines in that view, too.  However…

What?  You knew there was a ‘but’ coming…

They went to so much trouble to break those baseline numbers out into separate columns, which is great for some things, but not when you want to have a comparison of the baselines that you can slice and dice in an excel report!  So in all my ‘infinite’ wisdom, I’ve gone back to the AssignmentBaselineByDay table and personally beaten it into submission so that you can compare work, actual work, and any baseline work you choose against one another using a handy dandy slicer.

Additionally, if you use the “running total in” option in your line pivot chart, you can make what could be considered a “Burn-up” report that shows how the work/actuals/bl work compare over time.

The first step is to union the three types of data together (bl work, actual work, work) so that you can slice that dataset and don’t have to go check-marking things and therefore lose any field settings you want to keep. (like not showing fifty hundred decimal numbers for example)

So basically you select baseline work in one query, assignment work in another, and actuals in yet a third.  Then you union them all together.  See the full query at the end of the post, but here’s the concept:

SELECT
ProjectName,
BaselineNumber as DataSet,
TimeByDay,
BaselineWork

From
Baseline by day Table & some others

UNION ALL

SELECT
ProjectName
’99’ as DataSet (this has to be a number in the query but you can rename it in your pivot table later)
TimeByDay,
AssignmentWork

FROM

Assignment by day table & some others

UNION ALL

ProjectName,
’66’ as DataSet,
TimeByDay,
AssignmentActualWork

FROM

Assignment By Day table & some others

Once you have this going, setting up in the report is pretty easy.

First, put the baseline number into your Rows value and change 99 to Work and 66 to Actual Work.  I also changed the Baseline Numbers into “BL’, ‘BL1’ etc.

Now remove baseline number from the report and add TimeByDay in the row labels, Baseline Number in the Column Labels, and Assignment Baseline Work in the values.  I also grouped the TimeByDay by week. 

pivotfields

Now, if you want the “BurnUp” report, you need to change the “Show Values as” to ‘Running Total in’ and then put TimeBy Day. 

Now add a Pivot Line Chart and all the slicers your little heart can think of and you get something that looks like this:

burnup

Neato, huh?  It’s my newest invention.  I hope I’m not the only one who is in love.

Here’s the query.  Have fun, and don’t hurt anyone with your mighty baseline comparison power!

SELECT
MSP_EpmProject_UserView.ProjectName,
MSP_EpmAssignmentBaselineByDay.BaselineNumber as DataSet,
MSP_EpmAssignmentBaselineByDay.TimeByDay,
MSP_EpmAssignmentBaselineByDay.AssignmentBaselineWork,
MSP_EpmTask_UserView.TaskName

FROM
MSP_EpmAssignmentBaselineByDay

LEFT OUTER JOIN

MSP_EpmProject_UserView ON
MSP_EpmProject_UserView.ProjectUID = MSP_EpmAssignmentBaselineByDay.ProjectUID

LEFT OUTER JOIN
MSP_EpmTask_UserView ON
MSP_EpmAssignmentBaselineByDay.TaskUID = MSP_EpmTask_UserView.TaskUID
UNION ALL

SELECT
MSP_EpmProject_UserView.ProjectName,
’99’ as DataSet,
MSP_EpmAssignmentByDay_UserView.TimeByDay,
MSP_EpmAssignmentByDay_UserView.AssignmentWork,
MSP_EpmTask_UserView.TaskName

FROM
MSP_EpmAssignmentByDay_UserView

LEFT OUTER JOIN

MSP_EpmProject_UserView ON
MSP_EpmProject_UserView.ProjectUID = MSP_EpmAssignmentByDay_UserView.ProjectUID

LEFT OUTER JOIN
MSP_EpmTask_UserView ON
MSP_EpmAssignmentByDay_UserView.TaskUID = MSP_EpmTask_UserView.TaskUID
UNION ALL

SELECT
MSP_EpmProject_UserView.ProjectName,
’55’ as DataSet,
MSP_EpmAssignmentByDay_UserView.TimeByDay,
MSP_EpmAssignmentByDay_UserView.AssignmentActualWork,
MSP_EpmTask_UserView.TaskName

FROM
MSP_EpmAssignmentByDay_UserView

LEFT OUTER JOIN

MSP_EpmProject_UserView ON
MSP_EpmProject_UserView.ProjectUID = MSP_EpmAssignmentByDay_UserView.ProjectUID

LEFT OUTER JOIN
MSP_EpmTask_UserView ON

MSP_EpmAssignmentByDay_UserView.TaskUID = MSP_EpmTask_UserView.TaskUID

Advertisements

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