Did you know that the Project 2010 SDK a lying liar?
For reasons I can’t explain, our PMO needed to add a long list of items which “COULD” be associated to a task in project, and report on it. So I created the lookup table and the task field, allowing multiple values. I knew that there was a section in the SDK about multivalue fields in the reporting database, so I fiddled around with that query for a while to no avail.
That’s because the association view naming scheme they outline in the SDK is different from what I found on the server.
They state the following:
“View names for multivalue custom fields are of the form MSPCFxxx_Custom Field Name_AssociationView, where xxx can be PRJ, RES, or TSK, for a project, resource, or task custom field. For example, MSPCFPRJ_Project Departments_AssociationView contains multivalue associations for the Project Departments custom field.” (see official documentation here: http://msdn.microsoft.com/en-us/library/office/ee767688(v=office.14).aspx#pj14_CustomFieldsAndRDB_CFData)
But try as I might, I couldn’t find the task related association view. So I went and peeked on the server and found that the table is actually not named “TSK” but “TASK”.
So their query (ugly, in the eyes of this beholder)(no I do not have tenticles and a giant eye) doesn’t actually work if the multivalue field you want to report on is for a TASK field.
You have to replace the xxx listed above with TASK, not TSK. (Tsk, Tsk, MS!)
My query is below, prettied up and simplified a bit. (simpler to ME, you know how I feel about aliasing everything in my queries!)
In this example, the field and lookuptable are named ‘Item’, and the outer joins ensure that you’ll get results whether or not a task or project has data for the field.
MSP_EpmLookupTable.MemberFullValue AS N’Item(s)’
MSP_EpmProject_UserView INNER JOIN
MSP_EpmProject_UserView.ProjectUID = MSP_EpmTask_UserView.ProjectUID
LEFT OUTER JOIN
MSP_EpmTask_UserView.TaskUID = MSPCFTASK_Item_AssociationView.EntityUID
LEFT OUTER JOIN
MSPCFTASK_Item_AssociationView.LookupMemberUID = MSP_EpmLookupTable.MemberUID
In other news – this is my 30th post, and I’ve had over 1,000 visitors. Not bad for a hack!