Rule of Thumb

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.