OPEX and CAPEX Cost Reporting from Microsoft Project

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:

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.




You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply