Rule of Thumb

In the previous post I showed how to designate a field in Microsoft Project to hold cost type information and how to enter this information. Now I’ll show you how to get this information into a nice Microsoft Excel pivot table.

As always, we’ll work with Microsoft Project data stored in Microsoft Access database.

We’ll create two queries in Microsoft Access, first one to get cost types and secons one to combine it with assignments data into final table ready to turn it into pivot table.

To create Task Cost Type query, copy the following query into the Microsoft Access query’s SQL view.

SELECT
    MSP_TEXT_FIELDS.PROJ_ID,
    MSP_TEXT_FIELDS.TEXT_REF_UID AS TASK_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="Task Text1";

Cost type, if stored in tasks Text1 field, is stored in MSP_TEXT_FIELDS.TEXT_VALUE, but it has to linked to MSP_CONVERSIONS through MSP_TEXT_FIELDS.TEXT_FIELD_ID = MSP_CONVERSIONS.CONV_VALUE and filtered so that MSP_CONVERSIONS.CONV_STRING="Task Text1".

Next, create a new query, Resource Costs By Cost Type, based on Resource Costs query by left joining Cost Type query.

SELECT
    MSP_PROJECTS.PROJ_NAME AS Project,
    [Task Cost Type].TEXT_VALUE AS [Cost Type],
    [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.PROJ_ID = [Resource Rates].PROJ_ID)
            AND (MSP_ASSIGNMENTS.RES_UID = [Resource Rates].RES_UID)
            AND (MSP_ASSIGNMENTS.ASSN_COST_RATE_TABLE = [Resource Rates].RR_RATE_TABLE))
            ON MSP_PROJECTS.PROJ_ID = MSP_ASSIGNMENTS.PROJ_ID)
        INNER JOIN MSP_RESOURCES
            ON (MSP_ASSIGNMENTS.PROJ_ID = MSP_RESOURCES.PROJ_ID)
            AND (MSP_ASSIGNMENTS.RES_UID = MSP_RESOURCES.RES_UID))
        LEFT JOIN [Resource Groups]
            ON MSP_RESOURCES.RES_UID = [Resource Groups].TEXT_REF_UID)
            LEFT JOIN [Task Cost Type]
            ON (MSP_ASSIGNMENTS.PROJ_ID = [Task Cost Type].PROJ_ID)
            AND (MSP_ASSIGNMENTS.TASK_UID = [Task Cost Type].TASK_UID)
GROUP BY
    MSP_PROJECTS.PROJ_NAME,
    [Task Cost Type].TEXT_VALUE,
    [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;

You can download queries here:

• Cost Type.sql
• Resource Costs by Cost Type.sql

From here, it’s only a matter of creating a query from Microsoft Excel and building a pivot table out of it to suite your needs.

Now that you know how get resource workload and costs out of Microsoft Project, you can go further and add some custom groupings for further slicing and dicing.

In my case, we have two distinct phases on projects:

• feasibility study / project definition phase and
• implementation phase.

We have to prepare and report separate budgets for each phase.

Next, there are different type of costs on the project. They can be booked either as operative expense (OPEX) or capital expense (CAPEX).

These and all other attributes can be configured in Microsoft Project and exported to a nice Microsoft Excel pivot table.

Here’s how. Microsoft Project has 8 groups of customizable fields:

• Cost
• Date
• Duration
• Finish
• Flag
• Number
• Start
• Text

You can use them for whatever you want. By default they’re just numbered, like Text1, Text2, etc. but you can rename them, assign value lists, define default values, define behaviors of summary tasks, define graphical display of the values, etc.

We’ll use Text1 field and turn it into Cost Type with predefined value list:

1. Switch to Gantt View, then select Cost Table from View, Table.
2. Bring up Customize Fields dialog box by right clicking on any of column headings and selecting Customize Fields or through menus Tools, Customize and Fields.
3. Choose Text in the Type dropdown box.
4. Select Text1.
5. Click Rename, type in Cost Type and confirm it by clicking OK.
6. Now click Value List and type OPEX into the Value column of the first row and CAPEX into the second row.
7. You can define default value by checking Use a value from the list as the default entry for the field, selecting default value and clicking Set Default.
8. Confirm the settings for Value List with OK.
9. Click OK to finish field customization.

Field is now defined. You still need to add it to the Cost table.

1. Right click on a column heading, before which you want to add Cost Type field.
2. Select Insert Column.
3. Find Text1 (Cost Type), define optional attributes and confirm with OK.

You can now define each task’s cost type with drop down box in the task sheet or in Task Information dialog box on Custom Fields page.

Getting this new information into Microsoft Excel pivot table will be a subject of the next post.

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.