none
Get Lookup table data from Reporting DB RRS feed

  • Question

  • Hi,

    I have a lookup table and it has a data upto 3rd level. What are the tables/views stores this lookup tables info in Report DB. I wanted to get lookup data at each level in RDB.

     

    thanks,

    Ram

     


    Monday, October 3, 2011 7:19 AM

Answers

  • Hi Ram,

    Just ran a simple SQL query againest the Litware sample database:

    SELECT[LookupTableUID],[MemberUID],[MemberValue],[MemberFullValue],[ParentMemberUID],[MemberDescription],[LCID] FROM [ProjectServer2007_Litware_Reporting].[dbo].[MSP_EpmLookupTable]

    To make it more clear:
    There are three main columns for your reference:
    1. MemberValue
    2. MemberFullvalue
    3. ParentMemberUID

    For example, you have
    1. GOV Programs
           - GOV Programs.HR - Employee Retention
           - GOV Programs.IT - Training

    MemberValue   MemberFullValue    ParentMemberUID
    GOV Programs    GOV Programs      NULL

    Since "GOV Programs" is parent of others & doesn't have any ParentMemberUID but other child has the ParentMemberUID. You can write your SQl logic which can pull all the values from MSP_EpmLookupTable.


    Thanks, Amit Khare |EPM Consultant| Blog: http://amitkhare82.blogspot.com http://www.linkedin.com/in/amitkhare82
    Tuesday, October 4, 2011 8:03 AM

All replies

  • Hi Ram,

    You can get the data of lookup table using dbo.MSP_EpmLookupTable in reporting database. - MemberFullValue column contains the Full value of the lookup table, including parent members. You can use "MFN_Epm_GetAllCustomFieldsInformation" user defined function (UDF) in the Project Server 2010 RDB to find the custom fields information with lookup table.

    E.g You have project level CF which gets the data from a lookup table: in this example the Departments lookup table

    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 details:
    http://msdn.microsoft.com/en-us/library/ee767688.aspx#pj14_CustomFieldsAndRDB_CFData

    Does that help?


    Thanks, Amit Khare |EPM Consultant| Blog: http://amitkhare82.blogspot.com http://www.linkedin.com/in/amitkhare82
    Monday, October 3, 2011 7:49 AM
  • Hi Ram,

    As Amit Khare mentioned you can find all lookup table data in the table MSP_EpmLookupTable.

    But if you want to quickly get the possible values of a specific lookup table, you could also take a look at the different database views in the Reporting database.

    For each lookup table, an '_OlapView' view is automatically created that contains all lookup table values. The name of this view is of this format: MSPLT_CustomFieldName_OlapView

    I hope this helps,
    Hans


    My EPM blog: Projectopolis
    Tuesday, October 4, 2011 6:44 AM
    Moderator
  • Hi Ram,

    Just ran a simple SQL query againest the Litware sample database:

    SELECT[LookupTableUID],[MemberUID],[MemberValue],[MemberFullValue],[ParentMemberUID],[MemberDescription],[LCID] FROM [ProjectServer2007_Litware_Reporting].[dbo].[MSP_EpmLookupTable]

    To make it more clear:
    There are three main columns for your reference:
    1. MemberValue
    2. MemberFullvalue
    3. ParentMemberUID

    For example, you have
    1. GOV Programs
           - GOV Programs.HR - Employee Retention
           - GOV Programs.IT - Training

    MemberValue   MemberFullValue    ParentMemberUID
    GOV Programs    GOV Programs      NULL

    Since "GOV Programs" is parent of others & doesn't have any ParentMemberUID but other child has the ParentMemberUID. You can write your SQl logic which can pull all the values from MSP_EpmLookupTable.


    Thanks, Amit Khare |EPM Consultant| Blog: http://amitkhare82.blogspot.com http://www.linkedin.com/in/amitkhare82
    Tuesday, October 4, 2011 8:03 AM
  • Thanks Amit for your detailed explanation.
    Tuesday, October 4, 2011 10:29 AM