A couple of weeks ago, I wrote up instructions on how to create a Baseline Execution Index Report. It’s a pretty nifty concept, but in retrospect I think it was missing a key element, a cumulative record of the task backlog in the portfolio.
Here’s what I offered in the original post:
In that report, you’ll see that we do not display the cumulative backlog. That could give you a misleading view of the portfolio, by implying that the later period of the report is on track, when it really isn’t.
In this report though, you’ll see that even though our BEI may be improving, we still have an accumulated backlog of tasks.
To generate this report, I had to rework my query. It now looks like this:
SELECT Set1.TaskIsMilestone, dbo.MSP_EpmProject_UserView.ProjectName, Set1.TaskFinishDate AS PlannedFinish, NULL AS BaselineFinish,
TaskActualFinishDate AS ActualFinish, DATEADD(day, 0, DATEDIFF(day, 0, Set1.TaskFinishDate + 1-DATEPART (dd,Set1.TaskFinishDate))) AS TotalDate, 1 AS CumActualSource, Null AS CumBaselineSource
FROM dbo.MSP_EpmTask_UserView AS Set1 LEFT OUTER JOIN
dbo.MSP_EpmProject_UserView ON Set1.ProjectUID = dbo.MSP_EpmProject_UserView.ProjectUID
WHERE (TaskBaseline0FinishDate <> 0) AND (TaskActualFinishDate <> 0)AND (TaskActualFinishDate < (GETDATE()-(DATEPART (dd,GETDATE())))) AND TaskIsMilestone = 0 AND TaskIsSummary =0
SELECT Set2.TaskIsMilestone, dbo.MSP_EpmProject_UserView.ProjectName, NULL AS PlannedFinish, Set2.TaskBaseline0FinishDate AS BaselineFinish, NULL
AS ActualFinish, DATEADD(day, 0, DATEDIFF(day, 0, Set2.TaskBaseline0FinishDate + 1-DATEPART(dd,
Set2.TaskBaseline0FinishDate))) AS TotalDate, Null as CumActualSource, 1 as CumBaselineSource
FROM dbo.MSP_EpmTask_UserView AS Set2 LEFT OUTER JOIN
dbo.MSP_EpmProject_UserView ON Set2.ProjectUID = dbo.MSP_EpmProject_UserView.ProjectUID
WHERE (TaskBaseline0FinishDate <> 0) AND (TaskBaseline0FinishDate < (GETDATE()-(DATEPART (dd,GETDATE())))) AND TaskIsMilestone = 0 AND TaskIsSummary =0
ORDER BY TotalDate
Which does a couple of things:
- Eliminates the need to perform grouping in Excel – which breaks our pivot table cumulative calculations – by converting all dates to the first of the month.
- Adds a value of 1 to the CumActualSource field for all completed tasks. We will use this to keep our running total of tasks that finished.
- Adds a value of 1 to the CumBaselineSource field for all baselined tasks. We will use this to keep our running total of tasks that should have finished.
- Filters out all entries for the current month. As everything else is grouped by the month, showing partial results for the current month may skew the tail end of the chart.
Once you’ve got all of that in an ODC file, create the pivot table. Configure it as follows:
Now, we need to create three custom fields. Click on the pivot table, and select the option to create a new calculated field under the Options tab.
|TargetSource||=1||Used to display the target BEI.|
|BEISource||= ActualFinish/ BaselineFinish||Calculates the BEI for a given time period.|
|BacklogSource||= CumBaselineSource- CumActualSource||Calculates the backlog of incomplete tasks (compared to the baseline)|
The table now looks like this:
Change the names to something more user friendly, convert the BEISource field to a two digit decimal, and set the Backlog to a running total.
Add a PivotChart. Select the column chart type.
Right click on the Backlog and Target series to set them as a line chart data series. Set the Backlog to plot on the secondary axis.
Configure the primary Y axis as follows….
…and the X axis like this.
…and there you are:
Filter on a specific project to yield something like this: