Slicing and Dicing Resource Costs in Microsoft Project
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:
