none
Lookup field with MultiSelect is not synced to Reporting DB RRS feed

  • Question

  • Hi All,

    I am creating the Custom fields which uses look up table, which is multi-select, also work-flow controlled. Custom field is created. But When i try to access in 'MSP_EpmProject_UserView' in projectserve_reporting DB, it is not available.

    No Queue job is failed for this. Is there anything tool restriction for this? any work around?

    Please find the screen print for the properties selected for the custom field.

    Any one knows the solution?

    Thanks !


    Muniappan

    Friday, November 30, 2012 10:51 AM

Answers

  • Hi there--

    For Multivalue custom fields , Reporting database includes an association table view for each multivalue custom field that contains values. 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.

    SELECT proj.ProjectName, lt.MemberFullValue AS N'Department(s)'
    FROM dbo.MSP_EpmProject_UserView AS proj
    -- Use the association table view.
    INNER JOIN [dbo].[MSPCFPRJ_Project Departments_AssociationView] AS depassoc
       ON proj.ProjectUID = depassoc.EntityUID
    INNER JOIN dbo.MSP_EpmLookupTable AS lt
       ON depassoc.LookupMemberUID = lt.MemberUID
    WHERE lt.LCID = 1033
    AND lt.LookupTableUID = N'e7397277-1ab0-4096-b2dd-57029a055ba4' -- Department Lookup Table

    for more infomation, Please see below article:
    http://msdn.microsoft.com/en-us/library/office/ee767688(v=office.14).aspx

    Hope that helps.


    If you found this post helpful, please “Vote as Helpful”. If it answered your question, please “Mark as Answer”. Thanks, Amit Khare |EPM Consultant| Blog: http://amitkhare82.blogspot.com http://www.linkedin.com/in/amitkhare82

    • Proposed as answer by Ray Letts Monday, December 3, 2012 8:34 PM
    • Marked as answer by Muniappan S Tuesday, April 12, 2016 5:30 PM
    Monday, December 3, 2012 8:50 AM

All replies

  • Multivalue lookup tables are treated differently within the reporting database.  Check the Project Server SDK for an example of how to report against these fields.

    Andrew Lavinsky [MVP] Blog: http://azlav.umtblog.com Twitter: @alavinsky

    Friday, November 30, 2012 12:31 PM
    Moderator
  • Hi there--

    For Multivalue custom fields , Reporting database includes an association table view for each multivalue custom field that contains values. 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.

    SELECT proj.ProjectName, lt.MemberFullValue AS N'Department(s)'
    FROM dbo.MSP_EpmProject_UserView AS proj
    -- Use the association table view.
    INNER JOIN [dbo].[MSPCFPRJ_Project Departments_AssociationView] AS depassoc
       ON proj.ProjectUID = depassoc.EntityUID
    INNER JOIN dbo.MSP_EpmLookupTable AS lt
       ON depassoc.LookupMemberUID = lt.MemberUID
    WHERE lt.LCID = 1033
    AND lt.LookupTableUID = N'e7397277-1ab0-4096-b2dd-57029a055ba4' -- Department Lookup Table

    for more infomation, Please see below article:
    http://msdn.microsoft.com/en-us/library/office/ee767688(v=office.14).aspx

    Hope that helps.


    If you found this post helpful, please “Vote as Helpful”. If it answered your question, please “Mark as Answer”. Thanks, Amit Khare |EPM Consultant| Blog: http://amitkhare82.blogspot.com http://www.linkedin.com/in/amitkhare82

    • Proposed as answer by Ray Letts Monday, December 3, 2012 8:34 PM
    • Marked as answer by Muniappan S Tuesday, April 12, 2016 5:30 PM
    Monday, December 3, 2012 8:50 AM
  •  Hello, also be aware that once you check the multi value they will not show up in cubes. Amit is correct above in that once you select multi value the reporting DB creates some *associationView  views.

    cheers

    Ray


    Ray Letts Arbutus Solutions

    Monday, December 3, 2012 8:34 PM