Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
In PWA multiple values custom field is not available in Reporting DB. RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    In PWA we have custom field, let's say Agency. This field allows multiple values to be selected from lookup table. When we design query to select data from Reporting database this custom field is not available. What should we do to be able to select data from this field? 
    • Moved by Sapna Shukla, MCTS, Project MVP Thursday, September 15, 2011 2:41 PM PS Specific (From:Project Standard and Professional General Questions and Answers)
    Thursday, September 15, 2011 1:45 PM

Answers

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    You'll have to write a query to pull up that field.  Take a look at the Project
    Server SDK (free download).  That use case is specifically documented there.
     
    I think someone also blogged that up recently, but can't recall who.  Try
    plugging it into your search engine to see what comes up.
     
     
    Andrew Lavinsky [MVP] Blog: http://azlav.umtblog.com Twitter: @alavinsky
    Thursday, September 15, 2011 3:14 PM
  • Question
    Sign in to vote
    1
    Sign in to vote

    Hi there,

    Please see my blog post below :)

    http://pwmather.wordpress.com/2011/09/07/accessing-custom-fields-that-allow-multiple-values-from-look-up-tables-in-projectserver-via-t-sql-msproject-ps2010-ps2007/

    Thanks

    Paul

    Paul Mather | Twitter | http://pwmather.wordpress.com
    Thursday, September 15, 2011 3:20 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    there are some downsides of using custom field with multi values - Brian has already blogged the limitation of multi-value custom field to be used with the cubes. you can see the details here: http://blogs.msdn.com/b/brismith/archive/2011/02/04/project-server-2010-take-care-changing-custom-fields-to-allow-multiple-values.aspx

    But it should be available if you query the RDB - what query you using? are you aware about using the lookuptableuid in your query: LookupTableUID = N'e7397277-1ab0-4096-b2dd-57029a055ba4' ???

    although writing query to retrieve multi-value custom fiel is lot of hassle, but it should work.

    please see this msdn article: http://msdn.microsoft.com/en-us/library/ee767688.aspx, its all explained in there nicely.

    hope this helps..

    | Khurram Jamshed | Follow my blog about Enterprise Project Management Solution | http://khurramjamshed.blogspot.com |
    Thursday, September 15, 2011 3:23 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Just a short description on the same: For each custom field on project level with multi value enabled, a view MSPCFPRJ_YourFieldName_AssociationView is created. EntityUID can be linked to ProjectUID in any project table or view. Moreover, you will find a view MSPLT_YourLookUpTableName_UserView. You can link LookUpMemberUID of MSPCFPRJ_YourFieldName_AssociationView to LookUpMemberUID of MSPLT_YourLookUpTableName_UserView.

    Note Above explanation is for Project Level Multi-Value Custom Field, same is true for Task and Resource.

    Sachin Vashishth MCTS
    Thursday, September 15, 2011 4:50 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thank you for clear and logical explanation.
    Friday, September 23, 2011 8:01 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I use you query as prototype.

    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 helped me to solve my proble with little modification. Thank you very much. I have silly question for you. What is N in N’MVLookupField’ ? I coded with no N and got the same result.

     

    Friday, September 23, 2011 8:14 PM