The BEI Report Revisited

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.


The Query

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  
Union ALL
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:

  1. 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.
  2. 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.
  3. 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.
  4. 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.

Field Name Formula Description
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:


The BEI Report Revisited

One thought on “The BEI Report Revisited

Leave a Reply

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

You are commenting using your 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