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_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 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:
MSP_RESOURCE_RATES.RR_FROM_DATE) AS RR_FROM_DATE,
MSP_RESOURCE_RATES.RR_TO_DATE) AS RR_TO_DATE,
((Not (MSP_RESOURCE_RATES.RESERVED_DATA)='X')) OR
(((MSP_RESOURCE_RATES.RESERVED_DATA) Is Null) AND
((Not (MSP_RESOURCES.RESERVED_DATA)='X')) OR
(((MSP_RESOURCES.RESERVED_DATA) Is Null) AND
ORDER BY 1, 2, 3, 4;
From here it is now easy to link MSP_ASSIGNMENTS and Resource Rates through
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],
MSP_PROJECTS.PROJ_OPT_MINUTES_PER_DAY/1000)) AS [Work],
Sum((MSP_ASSIGNMENTS.ASSN_COST/100)) AS Cost
INNER JOIN (MSP_ASSIGNMENTS
INNER JOIN [Resource Rates]
ON (MSP_ASSIGNMENTS.ASSN_COST_RATE_TABLE =
AND (MSP_ASSIGNMENTS.RES_UID =
AND (MSP_ASSIGNMENTS.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 =
LEFT JOIN [Resource Groups]
ON MSP_RESOURCES.RES_UID = [Resource Groups].TEXT_REF_UID
Now get the result into Microsoft Excel pivot table and arrange it as you wish.
You can also download queries: