Accessing custom fields that allow multiple values from look up tables in #ProjectServer via T-SQL #MSProject #PS2010 #PS2007

September 7, 2011 at 2:12 pm | Posted in Customisation, T-SQL | 3 Comments
Tags: , , , , ,

At recent query came up on the Project Server forum regarding accessing custom fields that allow multiple values in the PWA_Reporting database. This is quick post to show how this can be achieved. In this example I have a single value lookup custom field called ProjectStatus and a custom field that allows multiple values called MVLookUpField. The example SQL is below:

SELECT        proj.ProjectName
        ,    proj.ProjectStatus — example single value lookup field
        ,    lt.MemberFullValue AS N’MVLookupField’
FROM        dbo.MSP_EpmProject_UserView AS proj
LEFT OUTER JOIN    [dbo].[MSPCFPRJ_MVLookupField_AssociationView] AS MVassoc — view for multi value field
            ON proj.ProjectUID = MVassoc.EntityUID
LEFT OUTER JOIN    dbo.MSP_EpmLookupTable AS lt
            ON MVassoc.LookupMemberUID = lt.MemberUID
order by    ProjectName asc

It is just simple select query but will hopefully it will give others an idea of how to get multi value custom fields from the PWA_Reporting database.

Advertisements

3 Comments »

RSS feed for comments on this post. TrackBack URI

  1. Its such as you read my thoughts! You seem to understand so much about this, like you wrote the guide in it or something. I believe that you could do with some percent to force the message house a bit, but instead of that, this is great blog. An excellent read. I will certainly be back.

  2. Thanks, this was very helpful.

  3. Wow another great blog. I had struggled with getting this to work for several hours, after finding this post it took me 10mins to get it working. Thanks


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

Blog at WordPress.com.
Entries and comments feeds.

%d bloggers like this: