Task Level Multivalue Custom Fields and the Reporting Database (Tsk tsk!)

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.

Yeah.

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.

SELECT
MSP_EpmProject_UserView.ProjectOwnerName,
MSP_EpmProject_UserView.ProjectName,
MSP_EpmTask_UserView.TaskName,
MSP_EpmLookupTable.MemberFullValue AS N’Item(s)’

FROM
MSP_EpmProject_UserView INNER JOIN
MSP_EpmTask_UserView ON
MSP_EpmProject_UserView.ProjectUID = MSP_EpmTask_UserView.ProjectUID

LEFT OUTER JOIN
MSPCFTASK_Item_AssociationView ON
MSP_EpmTask_UserView.TaskUID = MSPCFTASK_Item_AssociationView.EntityUID

LEFT OUTER JOIN
MSP_EpmLookupTable ON
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!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s