Rule of Thumb

You carefully planned your project, entered all the tasks, resources, resource costs, assigned resources and now you need to present some numbers. For example, like this:

Easy task on first hand, but Microsoft Project would not be Microsoft Project, if it didn’t complicate simple tasks. So let’s start at the beginning.

Basic Microsoft Project tables are MSP_TASKS and MSP_RESOURCES. Since there can be many different resources assigned to a single task, many-to-many relationship is between tasks and resources and intermediate table is needed. This table is MSP_ASSIGNMENTS.

From MSP_ASSIGNMENTS you can get work and cost of each assignment and by linking it to MSP_RESOURCES, you get resource names and standard rates. Resource groups (if needed) are stored in MSF_TEXT_FIELDS and you’ll need MSP_CONVERSIONS as well. Details on getting resource groups can be found in previous post.

It gets a bit tricky with rate tables. Each resource can have up to 5 rate tables named A, B, C, D and E, and each rate table can have up to 25 entries or time intervals.

Rate tables can be used if you use different rates depending on what kind of work someone is doing. John Smith can charged differently if he’s working on business analysis, testing or training material. You can specify rate tables in Resource Information dialog box.

Rate table time intervals, can be used for the resource price changes through the lifetime of the project. If nothing else, for multiyear projects pricing will change at least once a year and you can use time intervals to keep a complete history of price changes.

The difficult part is how this information is stored in tables. Part of the rate tabels is stored in MSP_RESOURCES and part of it in MSP_RESOURCE_RATES. To make things worse, some records get duplicated in MSP_RESOURCE_RATES. To work around this “features”, I created the following helper query:

SELECT DISTINCT
    MSP_RESOURCE_RATES.PROJ_ID,
    MSP_RESOURCE_RATES.RES_UID,
    MSP_RESOURCE_RATES.RR_RATE_TABLE,
    IIF(MSP_RESOURCE_RATES.RR_FROM_DATE<=#1/1/1984#,Null,
        MSP_RESOURCE_RATES.RR_FROM_DATE) AS RR_FROM_DATE,
    IIF(MSP_RESOURCE_RATES.RR_TO_DATE>#31/12/2049#,Null,
        MSP_RESOURCE_RATES.RR_TO_DATE) AS RR_TO_DATE,
    MSP_RESOURCE_RATES.RR_STD_RATE,
    MSP_RESOURCE_RATES.RR_STD_RATE_FMT,
    MSP_RESOURCE_RATES.RR_OVT_RATE,
    MSP_RESOURCE_RATES.RR_OVT_RATE_FMT,
    MSP_RESOURCE_RATES.RR_PER_USE_COST
FROM
    MSP_RESOURCE_RATES
WHERE
    ((Not (MSP_RESOURCE_RATES.RESERVED_DATA)='X')) OR
    (((MSP_RESOURCE_RATES.RESERVED_DATA) Is Null) AND
    MSP_RESOURCE_RATES.RES_UID>0)
UNION (
    SELECT
        MSP_RESOURCES.PROJ_ID,
        MSP_RESOURCES.RES_UID,
        0,
        MSP_RESOURCES.RES_AVAIL_FROM,
        MSP_RESOURCES.RES_AVAIL_TO,
        MSP_RESOURCES.RES_STD_RATE,
        MSP_RESOURCES.RES_STD_RATE_FMT,
        MSP_RESOURCES.RES_OVT_RATE,
        MSP_RESOURCES.RES_OVT_RATE_FMT,
        MSP_RESOURCES.RES_COST_PER_USE
    FROM
        MSP_RESOURCES
    WHERE 
        ((Not (MSP_RESOURCES.RESERVED_DATA)='X')) OR
        (((MSP_RESOURCES.RESERVED_DATA) Is Null)  AND
        MSP_RESOURCES.RES_UID>0)
)
ORDER BY 1, 2, 3, 4;

From here it is now easy to link MSP_ASSIGNMENTS and Resource Rates through PROJ_ID, RES_UID and ASSN_COST_RATE_TABLE=RR_RATE_TABLE.

SELECT
    MSP_PROJECTS.PROJ_NAME AS Project,
    [Resource Groups].TEXT_VALUE AS [Resource Group],
    MSP_RESOURCES.RES_NAME AS [Resource Name],
    [Resource Rates].RR_RATE_TABLE AS [Rate Table],
    [Resource Rates].RR_STD_RATE AS [Standard Rate],
    Sum((MSP_ASSIGNMENTS.ASSN_WORK/
        MSP_PROJECTS.PROJ_OPT_MINUTES_PER_DAY/1000)) AS [Work],
    Sum((MSP_ASSIGNMENTS.ASSN_COST/100)) AS Cost
FROM
    ((MSP_PROJECTS
        INNER JOIN (MSP_ASSIGNMENTS
        INNER JOIN [Resource Rates]
            ON (MSP_ASSIGNMENTS.ASSN_COST_RATE_TABLE =
                    [Resource Rates].RR_RATE_TABLE)
                AND (MSP_ASSIGNMENTS.RES_UID =
                    [Resource Rates].RES_UID)
                AND (MSP_ASSIGNMENTS.PROJ_ID =
                    [Resource Rates].PROJ_ID))
            ON MSP_PROJECTS.PROJ_ID = MSP_ASSIGNMENTS.PROJ_ID)
        INNER JOIN MSP_RESOURCES
            ON (MSP_ASSIGNMENTS.RES_UID = MSP_RESOURCES.RES_UID)
                AND (MSP_ASSIGNMENTS.PROJ_ID =
                    MSP_RESOURCES.PROJ_ID))
        LEFT JOIN [Resource Groups]
            ON MSP_RESOURCES.RES_UID = [Resource Groups].TEXT_REF_UID
GROUP BY
    MSP_PROJECTS.PROJ_NAME,
    [Resource Groups].TEXT_VALUE,
    MSP_RESOURCES.RES_NAME,
    [Resource Rates].RR_RATE_TABLE,
    [Resource Rates].RR_STD_RATE,
    MSP_ASSIGNMENTS.RES_UID
HAVING
    (((MSP_ASSIGNMENTS.RES_UID)>=0))
ORDER BY
    MSP_PROJECTS.PROJ_NAME,
    [Resource Groups].TEXT_VALUE,
    MSP_RESOURCES.RES_NAME,
    [Resource Rates].RR_RATE_TABLE;

Now get the result into Microsoft Excel pivot table and arrange it as you wish.

You can also download queries:

• Resource Rates.sql
• Resource Costs.sql

I know you didn’t bother with monster SQL SELECT and instead waited for me to come up with something chewable. And here it is. We reached the limits of Microsoft Query so we can now switch to Microsoft Access for some more flexibility.

First we’ll create a helper query in Microsoft Access called Resource Groups by copy-pasting the following SQL SELECT into query’s SQL view:

SELECT
    MSP_TEXT_FIELDS.TEXT_REF_UID,
    MSP_TEXT_FIELDS.TEXT_VALUE
FROM
    MSP_TEXT_FIELDS
        INNER JOIN MSP_CONVERSIONS ON
            MSP_TEXT_FIELDS.TEXT_FIELD_ID = MSP_CONVERSIONS.CONV_VALUE
WHERE
    (((MSP_CONVERSIONS.CONV_STRING)="Resource Group"));

Next, create a query in Microsoft Access called Resource Assignments by copy-pasting the following SQL SELECT into query’s SQL view:

SELECT
    [Resource Groups].TEXT_VALUE AS [Resource Group],
    MSP_RESOURCES.RES_NAME AS [Resource Name],
    MSP_TASKS.TASK_NAME AS [Task Name],
    MSP_ASSIGNMENTS.ASSN_WORK /
        MSP_PROJECTS.PROJ_OPT_MINUTES_PER_DAY /
        1000 AS [Assignment Work]
FROM
    (((MSP_PROJECTS
        INNER JOIN MSP_ASSIGNMENTS
            ON MSP_PROJECTS.PROJ_ID=MSP_ASSIGNMENTS.PROJ_ID)
        INNER JOIN MSP_RESOURCES
            ON MSP_ASSIGNMENTS.RES_UID=MSP_RESOURCES.RES_UID)
        INNER JOIN MSP_TASKS
            ON MSP_ASSIGNMENTS.TASK_UID=MSP_TASKS.TASK_UID)
        LEFT JOIN [Resource Groups]
            ON MSP_RESOURCES.RES_UID=[Resource Groups].TEXT_REF_UID
WHERE
    (((MSP_PROJECTS.PROJ_NAME)="SOFTDEV"))
ORDER BY
    [Resource Groups].TEXT_VALUE,
    MSP_RESOURCES.RES_NAME,
    MSP_TASKS.TASK_NAME;

And that’s it. Now you get grab the assignments data into Microsoft Excel by selecting Resource Assignments query.

If you’re stuck, read the post on how to create a query in Microsoft Excel.

Project = uncertainty. Managing a project means dealing with uncertainties. Microsoft Project doesn’t really help you there. It applies Critical Path Method to determine, which tasks will delay the project if they are delayed and calculates how much slack time other tasks have.

If you can elaborate a bit on tasks durations and provide optimistic, pessimistic and expected durations, you can use Microsoft Project’s PERT Analysis tool to enter this data and calculate estimates for tasks.

But still, life is more complex than that. If you know optimistic, pessimistic and expected (most likely) durations, and assume that actual outcomes are triangularly distributed, you can run a Monte Carlo simulation and see how the overall project duration is distributed and what are the chances that you meet a certain deadline.

I found a neat VBA script to run the simulation right out of Microsoft Project. It works by using the same fields as Microsoft Project’s PERT Analysis Tool, that is Duration1, Duration2 and Duration3, assigns random, triangularly distributed duration to each task and at the end of each run, it exports durations to Microsoft Excel worksheet. As a result, you get a list of possible outcomes for each task, summary task as well as the whole project. Put it on chart and you’ll know how thing might turn out.

I rewrote the simulation from scratch to make it faster by storing all data in VB instead exporting it to Microsoft Excel and programed analytical charts like duration, work and cost sensitivity, criticality, project finish, duration, work and cost distribution right into the simulation.

You can find more on the project page on SourceForge.

In the previous post you’ve seen how is it possible to get each resource’s work and cost divided on a monthly basis across the entire project into Microsoft Excel spreadsheet.

You can get timephased data for resources, tasks and assignments. All relevant data fields are available. You can find more information in the description of TimeScaleData method.

With task data it gets a bit tricky to export as they are hierarchically structured. By exporting everything, you won’t be able to sum them up. Here you have two options:

1. Export only non summary tasks (If Not Task.Summary Then …),
2. or export the outline structure as well.

And here’s how you do it:

Sub ExportTimephasedTaskData()
    ' Sub will export timephased taskdata (work) into Excel worksheet
   
    ' Define time interval for timephased data
    Dim Start, Finish As String
    Start = "1.1.2004"
    Finish = "31.5.2004"
   
    ' Define timescale unit. Can be one of the following PjTimescaleUnit constants:
    '   pjTimescaleYears, pjTimescaleQuarters, pjTimescaleMonths, pjTimescaleWeeks,
    '   pjTimescaleDays, pjTimescaleHours, pjTimescaleMinutes
    Dim TimescaleUnit As PjTimescaleUnit
    TimescaleUnit = pjTimescaleMonths
   
    Dim Pj As Project
    Dim PjRes As Resources
    Dim PjTasks As Tasks
    Dim XlApp As Excel.Application
    Dim IdSheet As Integer
    Dim XlSheet As Excel.Worksheet
    Dim XlBook As Excel.Workbook
   
    Set Pj = ActiveProject
    Set PjTasks = Pj.Tasks
   
    Dim PjTask As Task
   
    Set XlApp = New Excel.Application
    XlApp.Visible = False
    Set XlBook = XlApp.Workbooks.Add
    XlBook.Title = Pj.Title
    Set XlSheet = XlBook.ActiveSheet
   
    Dim TSVWork As TimeScaleValues
    Dim T As Long
    Dim Ts As Long
    Dim Row As Integer
    Dim d As Single
    Dim CurrencyFormat As String
   
    ' Choose work unit divisor depending on the Tools | Options | Schedule | Work.
    ' Work is stored in minutes in MS Project.
    Select Case Pj.DefaultWorkUnits
        Case pjMinute
          d = 1
        Case pjHour
          d = 60
        Case pjDay
          d = Pj.HoursPerDay * 60
        Case pjWeek
          d = Pj.HoursPerWeek * 60
        Case pjMonthUnit
          d = Pj.DaysPerMonth * Pj.HoursPerDay * 60
        Case Else
          d = 1
    End Select
   
    If Pj.Tasks.Count > 0 Then
       
        XlSheet.Cells(1, 1) = "Task Name"
       
        For T = 1 To PjTasks.Count
       
            Set PjTask = PjTasks(T)
            ' Set Outline level of the task
            XlSheet.Rows(T + 1).OutlineLevel = PjTask.OutlineLevel
            XlSheet.Cells(T + 1, 1).Value = PjTask.Name
            ' Indent tasks
            XlSheet.Cells(T + 1, 1).IndentLevel = PjTask.OutlineLevel - 1
            XlSheet.Cells(T + 1, 1).AddIndent = True
       
            Set TSVWork = PjTask.TimeScaleData(Start, Finish, _
                Type:=pjTaskTimescaledWork, TimescaleUnit:=TimescaleUnit)
           
            For Ts = 1 To TSVWork.Count
                If T = 1 Then
                    XlSheet.Cells(1, 1 + Ts) = TSVWork(Ts).StartDate
                End If
               
                Select Case TimescaleUnit
                    Case pjTimescaleMonths
                        XlSheet.Cells(1, 1 + Ts).NumberFormat = "Mmm Yy"
                End Select
                       
                If Not TSVWork(Ts).Value = "" Then
                    XlSheet.Cells(T + 1, 1 + Ts) = TSVWork(Ts).Value / d
                    XlSheet.Cells(T + 1, 1 + Ts).NumberFormat = "#,##0"
                End If
            Next Ts
            If PjTask.Summary Then
                XlSheet.Rows(T + 1).Font.Bold = True
            End If
        Next T
        XlSheet.Outline.SummaryRow = xlSummaryAbove
    End If
   
    XlSheet.Columns.AutoFit
   
    XlApp.ScreenUpdating = True
    MSProject.ScreenUpdating = True
    AppActivate "Microsoft Project"
   
    XlApp.Visible = True
    AppActivate "Microsoft Excel"
End Sub

Result:

It is easy to get complete tasks, resources or assignments work, units or cost, but getting this data split by weeks or months is a real pain. Microsoft Project does provide a table namend MSP_TIMEPHASED_DATA, but due to Microsoft Project’s performance reasons it is not stored in way that it would be easily extracted.

Microsoft provides some SQL scripts that can help you out if you’re using Microsoft SQL Server, but working with Microsoft Query on Microsoft Access database this doesn’t help much.

But there’s another way to tacle this. We can use Microsoft Visual Basic and a bit of programming to get the data into Microsoft Excel.

1. From Microsoft Project select Tools, Macro then Microsoft Visual Basic Editor.
2. Select Tools and References and make sure that Microsoft Project 11.0 Object Library and Microsoft Excel 11.0 Object Library are selected.

1. In Visual Basic select Insert, then Module.
2. You will see a new module in the Project Explorer pane on the tope left part of the window.
3. In the Properties Window pane, click on the name of the newly created module ("Module#") and rename it into ExportTimephasedResourceData.
4. Copy the following code into the editor:


Sub ExportTimephasedResourceData()
' Sub will export timephased resource data (work, cost) into Microsoft Excel worksheet
The output is data in Excel spreadsheet, which you can then use to create a pivot table.

‘ Define time interval for timephased data
Dim Strat, Finish As String
Start = “1.1.2004″
Finish = “31.5.2004″

‘ Define timescale unit. Can be one of the following PjTimescaleUnit constants:
‘   pjTimescaleYears, pjTimescaleQuarters, pjTimescaleMonths, pjTimescaleWeeks,
‘   pjTimescaleDays, pjTimescaleHours, pjTimescaleMinutes
Dim TimescaleUnit As PjTimescaleUnit
TimescaleUnit = pjTimescaleMonths

Dim Pj As Project
Dim PjRes As Resources
Dim XlApp As Excel.Application
Dim IdSheet As Integer
Dim XlSheet As Excel.Worksheet

Set Pj = ActiveProject
Set PjRes = Pj.Resources

Set XlApp = New Excel.Application
XlApp.Visible = False
Set XlBook = XlApp.Workbooks.Add
XlBook.Title = Pj.Title
Set XlSheet = XlBook.ActiveSheet

Dim TSVWork As TimeScaleValues
Dim TSVCost As TimeScaleValues
Dim T As Long
Dim R As Long
Dim Row As Integer

‘ Choose work unit divisor depending on the Tools | Options | Schedule | Work.
‘ Work is stored in minutes in MS Project.
Select Case Pj.DefaultWorkUnits
Case pjMinute
d = 1
Case pjHour
d = 60
Case pjDay
d = Pj.HoursPerDay * 60
Case pjWeek
d = Pj.HoursPerWeek * 60
Case pjMonthUnit
d = Pj.DaysPerMonth * Pj.HoursPerDay * 60
Case Else
d = 1
End Select

‘ Set up currency format for Excel
CurrencyFormat = SetCurrencyFormat(Pj)

If Pj.Resources.Count > 0 Then

XlSheet.Cells(1, 1) = “Group”
XlSheet.Cells(1, 2) = “Resource”
XlSheet.Cells(1, 3) = “Date”
XlSheet.Cells(1, 4) = “Work”
XlSheet.Cells(1, 5) = “Cost”

Row = 2

For R = 1 To Pj.Resources.Count
Set TSVWork = PjRes(R).TimeScaleData(Start, Finish, _
Type:=pjResourceTimescaledWork, TimescaleUnit:=TimescaleUnit)
Set TSVCost = PjRes(R).TimeScaleData(Start, Finish, _
Type:=pjResourceTimescaledCost, TimescaleUnit:=TimescaleUnit)

For T = 1 To TSVWork.Count

If Not TSVWork(T).Value = “” And Not TSVCost(T).Value = “” Then
XlSheet.Cells(Row, 1) = PjRes(R).Group
XlSheet.Cells(Row, 2) = PjRes(R).Name
XlSheet.Cells(Row, 3) = TSVWork(T).StartDate
Select Case TimeUnits
Case pjTimescaleMonths
XlSheet.Cells(Row, 3).NumberFormat = “Mmm Yy”
End Select

If Not TSVWork(T).Value = “” Then
XlSheet.Cells(Row, 4) = TSVWork(T).Value / d
XlSheet.Cells(Row, 4).NumberFormat = “#,##0″
End If
If Not TSVCost(T).Value = “” Then
XlSheet.Cells(Row, 5) = TSVCost(T).Value
XlSheet.Cells(Row, 5).NumberFormat = CurrencyFormat
End If

Row = Row + 1

End If

Next T
Next R

End If

XlApp.ScreenUpdating = True
MSProject.ScreenUpdating = True
‘and finally display a message that we are finished
AppActivate “Microsoft Project”

XlApp.Visible = True
AppActivate “Microsoft Excel”
End Sub

Function SetCurrencyFormat(Pj As Project)

‘ Set currency number format
CurrencyFormat = “”

Select Case Pj.CurrencySymbolPosition
Case pjBefore
CurrencyFormat = “”"” & Pj.CurrencySymbol & “”"”
Case pjBeforeWithSpace
CurrencyFormat = “”"” & Pj.CurrencySymbol & “”"” & ” ”
End Select

CurrencyFormat = CurrencyFormat & “#,##0″

If ActiveProject.CurrencyDigits > 0 Then
CurrencyFormat = CurrencyFormat & “.”
For i = 1 To Pj.CurrencyDigits
CurrencyFormat = CurrencyFormat & “0″
Next i
End If

Select Case Pj.CurrencySymbolPosition
Case pjAfter
CurrencyFormat = CurrencyFormat & “”"” & Pj.CurrencySymbol & “”"”
Case pjAfterWithSpace
CurrencyFormat = CurrencyFormat & ” ” & “”"” & Pj.CurrencySymbol & “”"”
End Select

SetCurrencyFormat = CurrencyFormat

End Function

The output is exported into new Microsoft Excel spreadsheet, which is easily converted into pivot table.

Now that we are masters of the data, let’s see who needs to do what on the project. We’ll start by replicating Microsoft Project’s view:

We’ll need tables MSP_PROJECTS, MSP_RESOURCES, MSP_TASKS and MSP_ASSIGNMENTS to start:

1. Add before mentioned tables to the query.
2. MS Query will automatically interconnect (join) tables. And it will overdo it. Remove the extra connections by double clicking each of the redundant joins then clicking Remove in the Join dialog box. You only need to keep MSP_ASSIGNMENT.PROJ_ID = MSP_PROJECTS.PROJ_ID, MSP_ASSIGNMENT.RES_UID = MSP_RESOURCES.RES_UID and MSP_ASSIGNMENTS.TASK_UID = MSP_TASKS.TASK_UID.
3. Add MSP_PROJECTS.PROJ_NAME to criteria list and type the name of your project into its value.
4. Add MSP_RESOURCES.RES_UID>0 and MSP_TASKS_TASK_UID>0 to criteria list.
5. Add fields MSP_RESOURCES.RES_NAME, MSP_TASKS.TASK_NAME and MSP_ASSIGNMENTS.ASSN_WORK to the query output.
6. Add sorting keys (Records then Sort) MSP_RESOURCES.RES_NAME and MSP_TASKS.TASK_NAME.

For the final result we’re still short of Resource Group data and this is where things get interesting, since you won’t find a corresponding field in MSP_RESOURCES table. This is because Microsoft Project keeps some of its fields in custom fields tables (MSP_*_FIELDS). In our case, Resource group is stored in MSP_TEXT_FIELDS as it is a text field.

Custom fields from MSP_*_FIELDS are related to corresponding records through *_REF_UID.

In order to get Resource Group out on the plain, we need to:

1. Add MSP_TEXT_FIELDS to the query.
2. Remove all joins connected to MSP_TEXT_FIELDS.
3. Join MSP_RESOURCES.RES_UID to MSP_TEXT_FIELDS.TEXT_REF_UID by dragging the first field and dropping it to the second.
4. Add MSP_TEXT_FIELDS.TEXT_VALUE to query output and drag it the first position on the left and rename it to Resource Group.

We’re almost there. Resources can have many custom values and in such situations, we would get all values in a display. So we want to limit the output to Resource Group only. Table MSP_CONVERSIONS holds the key to the answer:

1. Add MSP_CONVERSIONS to the query.
2. Connect MSP_CONVERSIONS.CONV_VALUE to MSP_TEXT_FIELDS.TEXT_FIELD_ID.
3. Add criteria MSP_CONVERSIONS.CONV_STRING='Resource Group'.

This is how final SQL statement looks like:

SELECT
   TF.TEXT_VALUE AS 'Resource Group',
   R.RES_NAME AS 'Resource Name',
   T.TASK_NAME AS 'Task Name',
   A.ASSN_WORK/P.PROJ_OPT_MINUTES_PER_DAY/1000 AS 'Work'
FROM
   MSP_ASSIGNMENTS A,
   MSP_CONVERSIONS C,
   MSP_PROJECTS P,
   MSP_RESOURCES R,
   MSP_TASKS T,
   MSP_TEXT_FIELDS TF
WHERE
   A.PROJ_ID = P.PROJ_ID AND
   A.RES_UID = R.RES_UID AND
   T.TASK_UID = A.TASK_UID AND
   R.RES_UID = TF.TEXT_REF_UID AND
   C.CONV_VALUE = TF.TEXT_FIELD_ID AND
   P.PROJ_NAME='SOFTDEV' AND
   A.RES_UID>0 AND
   A.TASK_UID>0 AND
   C.CONV_STRING='Resource Group'
ORDER BY
   TF.TEXT_VALUE,
   R.RES_NAME,
   T.TASK_NAME

Now you can create a pivot table out of the data:

1. Add Resource Group, Resource Name and Task Name to Row Area.
2. Add Work to Data Area.

And here’s how the result should look like:

There’s still a minor difference to the view in the Microsoft Project. If you look at the first picture at the top, you’ll see that it displays Unassigned tasks as well not grouped resources. Without going into details, SQL statement grows into this monstrosity:

SELECT
   'No Group' ,
   MSP_RESOURCES.RES_NAME ,
   MSP_TASKS.TASK_NAME ,
   MSP_ASSIGNMENTS.ASSN_WORK/MSP_PROJECTS.PROJ_OPT_MINUTES_PER_DAY/1000
FROM
   MSP_ASSIGNMENTS MSP_ASSIGNMENTS,
   MSP_CONVERSIONS MSP_CONVERSIONS,
   MSP_PROJECTS MSP_PROJECTS,
   MSP_RESOURCES MSP_RESOURCES,
   MSP_TASKS MSP_TASKS,
   MSP_TEXT_FIELDS MSP_TEXT_FIELDS
WHERE
   MSP_ASSIGNMENTS.PROJ_ID = MSP_PROJECTS.PROJ_ID AND
   MSP_ASSIGNMENTS.RES_UID = MSP_RESOURCES.RES_UID AND
   MSP_TASKS.TASK_UID = MSP_ASSIGNMENTS.TASK_UID AND
   MSP_RESOURCES.RES_UID = MSP_TEXT_FIELDS.TEXT_REF_UID AND
   MSP_CONVERSIONS.CONV_VALUE = MSP_TEXT_FIELDS.TEXT_FIELD_ID AND
   MSP_PROJECTS.PROJ_NAME='SOFTDEV' AND
   MSP_ASSIGNMENTS.RES_UID>0 AND
   MSP_ASSIGNMENTS.TASK_UID>0 AND
   MSP_CONVERSIONS.CONV_STRING='Resource Group' AND
   MSP_TEXT_FIELDS.TEXT_VALUE Is Null
UNION(
SELECT
   MSP_TEXT_FIELDS.TEXT_VALUE ,
   MSP_RESOURCES.RES_NAME ,
   MSP_TASKS.TASK_NAME ,
   MSP_ASSIGNMENTS.ASSN_WORK/MSP_PROJECTS.PROJ_OPT_MINUTES_PER_DAY/1000
FROM
   MSP_ASSIGNMENTS MSP_ASSIGNMENTS,
   MSP_CONVERSIONS MSP_CONVERSIONS,
   MSP_PROJECTS MSP_PROJECTS,
   MSP_RESOURCES MSP_RESOURCES,
   MSP_TASKS MSP_TASKS,
   MSP_TEXT_FIELDS MSP_TEXT_FIELDS
WHERE
   MSP_ASSIGNMENTS.PROJ_ID = MSP_PROJECTS.PROJ_ID AND
   MSP_ASSIGNMENTS.RES_UID = MSP_RESOURCES.RES_UID AND
   MSP_TASKS.TASK_UID = MSP_ASSIGNMENTS.TASK_UID AND
   MSP_RESOURCES.RES_UID = MSP_TEXT_FIELDS.TEXT_REF_UID AND
   MSP_CONVERSIONS.CONV_VALUE = MSP_TEXT_FIELDS.TEXT_FIELD_ID AND
   MSP_PROJECTS.PROJ_NAME='SOFTDEV' AND
   MSP_ASSIGNMENTS.RES_UID>0 AND
   MSP_ASSIGNMENTS.TASK_UID>0 AND
   MSP_CONVERSIONS.CONV_STRING='Resource Group' AND
   MSP_TEXT_FIELDS.TEXT_VALUE Is Not Null
)
UNION(
SELECT
   'No Group' ,
   MSP_RESOURCES.RES_NAME ,
   MSP_TASKS.TASK_NAME ,
   MSP_ASSIGNMENTS.ASSN_WORK/MSP_PROJECTS.PROJ_OPT_MINUTES_PER_DAY/1000
FROM
   MSP_PROJECTS MSP_PROJECTS,
   MSP_TASKS MSP_TASKS,
   MSP_ASSIGNMENTS MSP_ASSIGNMENTS,
   MSP_RESOURCES MSP_RESOURCES
WHERE
   MSP_PROJECTS.PROJ_NAME = 'SOFTDEV' AND
   MSP_PROJECTS.PROJ_ID = MSP_ASSIGNMENTS.PROJ_ID AND
   MSP_TASKS.TASK_UID = MSP_ASSIGNMENTS.TASK_UID AND
   MSP_RESOURCES.RES_UID = MSP_ASSIGNMENTS.RES_UID AND
   MSP_RESOURCES.RES_UID >0 AND
   MSP_RESOURCES.RES_UID NOT IN (
      SELECT
         MSP_TEXT_FIELDS.TEXT_REF_UID
      FROM
         MSP_CONVERSIONS MSP_CONVERSIONS,
         MSP_PROJECTS MSP_PROJECTS,
         MSP_TEXT_FIELDS MSP_TEXT_FIELDS
      WHERE
         MSP_TEXT_FIELDS.TEXT_FIELD_ID = MSP_CONVERSIONS.CONV_VALUE AND
         MSP_PROJECTS.PROJ_ID = MSP_TEXT_FIELDS.PROJ_ID AND
         MSP_PROJECTS.PROJ_NAME = 'SOFTDEV' AND
         MSP_CONVERSIONS.CONV_STRING='Resource Group')
   )
UNION(
SELECT
   'No Group' ,
   'Unassigned' ,
   MSP_TASKS.TASK_NAME ,
   MSP_ASSIGNMENTS.ASSN_WORK/MSP_PROJECTS.PROJ_OPT_MINUTES_PER_DAY/1000
FROM
   MSP_ASSIGNMENTS MSP_ASSIGNMENTS,
   MSP_PROJECTS MSP_PROJECTS,
   MSP_TASKS MSP_TASKS
WHERE
   MSP_TASKS.TASK_UID = MSP_ASSIGNMENTS.TASK_UID AND
   MSP_PROJECTS.PROJ_ID = MSP_ASSIGNMENTS.PROJ_ID AND
   MSP_PROJECTS.PROJ_NAME='SOFTDEV' AND
   MSP_ASSIGNMENTS.RES_UID<0
)
ORDER BY 1, 2, 3

Query can’t handle column names if UNION statement is used in SQL SELECT, so you’ll have to rename columns once you’re back in Microsoft Excel.

Let’s start with basic question: how much is each resource allocated on the project? With this we’ll replicate Microsoft Project’s Resource Sheet, with Work field added to it.

To get this out of Microsoft Project database, you need to construct something like:

Step by step instructions, assuming you know how you pull the data out of Microsoft Project:

1. Add tables MSP_PROJECTS and MSP_RESOURCES.
2. Add PROJ_NAME from MSP_PROJECTS into Criteria pane and set criteria value to the name of the project. In the above sample its SOFTDEV.
3. Add RES_UID from MSP_RESOURCES into Criteria pane and set criteria value to >0. Only resources with unique ID greater than 0 are valid actually resources. Other records in resource table are for the purpose of Project.
4. Add RES_NAME and RES_WORK from MSP_RESOURCES to the query.
5. Now double click RES_WORK in the query and change it to MSP_RESOURCES.RES_WORK / MSP_PROJECTS.PROJ_OPT_MINUTES_PER_DAY / 1000 to have the work displayed in days.
6. Change the work column heading by double clicking it and putting Work into Column heading field.
7. Change resource name column heading in the same way to Resource Name.
8. To sort the resulting table, click anywhere in the resource name column and click Sort Ascending button.
9. Click Return Data button and you’ll get the table in the Excel.

Now that you got direct access to the Microsoft Project data from Microsoft Excel, you’re faced with tens of funny named tables and their attributes. I’ll show you how to sort out this mess to find what you’re looking for.

Let’s start with basics. Microsoft Project database can hold data for many projects. To list which project are in the database, you would construct a query like this:


SELECT MSP_PROJECTS.PROJ_NAME
FROM `D:\My Documents\Sample Project\SOFTDEV`.MSP_PROJECTS MSP_PROJECTS


To make these SELECT statements readable, I will be leaving out file paths. I’ll also use shorter table alias, but don’t worry about this, as this is legal SQL syntax.

In short:

SELECT P.PROJ_NAME
FROM MSP_PROJECTS P


The output will be something like:

How do you actually construct a query in Microsoft Query?

1. When Microsoft Query is started, you’re automatically presented with Add Tables dialog box.
2. Select the table you want to work with and click Add.
3. Repeat this for all tables, you need for a specific query.
4. Click Close when you’re finished.

You can always bring back Add Tables dialog box by clicking Add Table(s) button in the toolbar or by selecting Table and Add Tables from the menu.

Selected tables are shown in the upper pane of the Query window.

If you want to remove the table, select it and press Delete key, or select Table and Remove Table from the menu.

To choose which fields you weant to see in the query result, find each field in tables and double click it. Selected field will show in the Query Pane of the window.

To remove a field, click it and press Delete key.

Now let’s get task list out of database.

1. Add tables MSP_PROJECTS and MSP_TASKS.
2. Add field TASK_NAME from MSP_TASKS table.

If database contains only one project, then this query is fine, but if there is more than one project in the database, you may not be happy with a list of tasks, without knowing which task belongs to which project.

You have two options.

1. Add field PROJ_NAME from table MSP_PROJECTS. This will now produce a table with two fields, first will be Task Name and the second will be Project Name.
2. In case you only want tasks for a specific project, click Show/Hide Criteria to show Criteria Pane and then drag PROJ_NAME field from table MSP_PROJECTS to first column in the Criteria Field. One row below in the Value field, type the name of the project, for which you want to retrieve tasks.

You might also want to order the records. Select Records and Sort from the menu and add MSP_TASKS.TASK_ID in the ascending order to the sort list.
You will notice a couple of strange records. These are used by Project and should be ignored. You can eliminate them by adding another criteria into criteria table: MSP_TASKS.TASK_UID >= 0.

SQL statement for that query looks like this:

SELECT T.TASK_UID, T.TASK_NAME
FROM MSP_PROJECTS P, MSP_TASKS T
WHERE T.PROJ_ID = P.PROJ_ID AND ((P.PROJ_NAME='SOFTDEV') AND (T.TASK_ID>=0))
ORDER BY T.TASK_ID

Let’s now add some duration, work, start and finish information to the tasks. Add fields TASK_DUR, TASK_WORK, TASK_START_DATE and TASK_FINISH_DATE from MSP_TASKS to the query output.
You will notice some weird numbers for duration and work. This is because duration, work, rate, and cost values can be displayed using different units, Microsoft Project saves each using a standard multiple:

• Duration values are saved as minutes * 10. Eight hours would be saved as 4800 (that is, 8*60*10).
• Work values are saved as minutes * 1000. Eight hours would be saved as 480000 (that is, 8*60*1000).
• Rate values are saved as units per hour. For example, fifteen dollars an hour would be saved as 15.
• Cost fields are saved as units * 100. For example, seventy dollars and twenty-five cents would be saved as 7025.

But if we wanted to convert duration and work into let say days, we also need to know, how many hours (or minutes) are in a workday. We need project settings.

MSP_PROJECTS table has a couple of fields, which you can use to convert time units:

• PROJ_OPT_MINUTES_PER_DAY, number of minutes in a working day,
• PROJ_OPT_MINUTES_PER_WEEK, number of minuets in a working week,
• and PROJ_OPT_DAYS_PER_DAY, number of working days in a month.

For example, to show duration and work, you would double click TASK_DUR in a Query pane and replace MSP_TASKS.TASK_DUR with MSP_TASKS.TASK_DUR/MSP_PROJECTS.PROJ_OPT_MINUTES_PER_DAY/10. Also replace MSP_TASKS.TASK_WORK with MSP_TASKS.TASK_WORK/MSP_PROJECTS.PROJ_OPT_MINUTES_PER_DAY/1000.

Our SQL statement has now turned into the following monster:
SELECT T.TASK_ID, T.TASK_NAME, T.TASK_DUR/P.PROJ_OPT_MINUTES_PER_DAY/10, T.TASK_WORK/P.PROJ_OPT_MINUTES_PER_DAY/1000, T.TASK_START_DATE, T.TASK_FINISH_DATE
FROM MSP_PROJECTS P, MSP_TASKS T
WHERE T.PROJ_ID = P.PROJ_ID AND ((P.PROJ_NAME='SOFTDEV') AND (T.TASK_UID>=0))
ORDER BY T.TASK_ID

To make the output a bit more readable, you can rename the column headings. Double click each of them and provide a more descriptive name in Column heading.

Now you can explore the rest of the tables and fields on your own. I’ll present some tips and tricks in future posts. In the meantime explore Microsoft Office Project 2003 Data Reference
located on your disk in something similar to C:\Program Files\Microsoft Office\OFFICE11\1033\PJDB.HTM for a detailed description of the project database. You might as well download Microsoft Office Project 2003 Database Schema.

Microsoft Project is very clumsy in providing usable reports. Some basic reports are there, but not very helpful and not really customizable.

Exporting (saving) Project data to Microsoft Excel is as helpful as reports. Start with duration and work fields, which are exported as text, with different time units (hours, days, months, …), and not as numbers, so you can’t do any analysis on them, without first converting them to numbers, probably with Data, then Text to Columns, and even then, you’d have to write one hell of a formula to convert those numbers to the same time unit.

Project provides quite extensive tool to define fields you want to export, headings and column sequence, but the end result is useless:

You’d think that exporting to Microsoft Excel PivotTable might do the trick, but no, work and duration are still textual, so PivotTable can only count them and cannot possibly sum. Why on earth would anyone need that?

But it does a decent job in replicating the outline structure, if that’s of some use to you.

Copy/pasting is as useless as exporting the data, so what can you do?

The solution is to dig really deep into the root of the problem. The data. Microsoft Project can work with ODBC databases instead of .mpp files, which is really clever. Forget about Microsoft Project reporting and exporting capabilities and use Microsoft Excel or Microsoft Access for this by directly attaching to the Microsoft Project database.

You don’t need a Microsoft Project Server to do this and neither a database server. The idea is to create an Microsoft Access database by saving the project in Microsoft Access Database (*.mdb) format.

The result is, that all the data, including all the options and settings, are in the Microsoft Access Database file, from which it is relatively easy to get it into Microsoft Excel, either as a worksheet table or as a PivotTable.

1. Start Microsoft Project with an empty project or by opening an existing one.
2. Select File, Save As and choose Microsoft Access Database (*.mdb) file format (Save as type drop down).
3. Browse to your preferred location and type the filename.
4. Confirm your choice by clicking Save.

1. Click Next.

1. Choose between a full or partial project export and click Next.

1. Microsoft Access Database can hold many projects. In the Export Wizard – Project Definition you need to define the name of the current project under Project name. This is not a filename, which is already set and you can see it next to Data Source.
2. Click Finish when you’re done.

To work with Microsoft Access Database file in Microsoft Project, you don’t double click it, as this will open Microsoft Access and not Microsoft Project. Instead, you run the Microsoft Project first and then you select File and Open.

Now you have two options:

• Select Microsoft Access Database (*.mdb) in Files of type and browse to your file,
• or click ODBC and choose the appropriate data source from the list. For this to work, you need to create a data source first. I suggest this approach, as you’ll need the data source for queries anyhow.

Now you have everything set up to create database query in Microsoft Excel. I’ll describe how to decipher project data in Access Database in upcoming posts.

You know that the data is out there and you want to bring it into your homely Microsoft Excel. Here’s how you do it.

1. If you don’t have a data source pointing to your database yet, create one.
2. In Microsoft Excel choose Data, Import External Data and then New Database Query.

1. Select your data source and click OK.
2. For interesting queries un-check Use the Query Wizard to create/edit queries.
3. Click OK and Microsoft Query appears.
4. Choose tables, fields, joins, filters, groups and sort orders as you like or write your SQL SELECT directly by clicking on SQL button (View, then SQL).
5. When you’re done, click Return Data button on the toolbar (File, then Return Data to Microsoft Office Excel) and you’re back in Excel with selected data.

1. Just choose where exactly do you want that data. You can also choose Create a PivotTable report to have Microsoft Excel create a PivotTable report right away or click Properties, to tweak some settings on how this data is imported or Edit Query, to return to Microsoft Query.

Now you can right click anywhere on the table and either Refresh the data, Edit Query or change the External Data Range Properties.