none
SQL Query from MSP_EpmProject_UserView View RRS feed

  • Question

  • Hi There

     

    I have created some "Enterprise custom fields" which are completed in the PDP ProjectDetails.  I am using the view "MSP_EpmProject_UserView" to pull details about projects via a SQL query which is working fine for all the custom fields except when the custom field uses a lookup.  Can somebody please advise on how I retrieve the values for this custom field via SQL please?  Thanks

    Tuesday, September 6, 2011 1:54 PM

Answers

  • Hi Darren,

    Try this query below against the PWA_Reporting database:

     SELECT   proj.ProjectName
      , proj.ProjectStatus -- example single value lookup field
      , lt.MemberFullValue AS N'Products'
    FROM   dbo.MSP_EpmProject_UserView AS proj
    LEFT OUTER JOIN  [dbo].[MSPCFPRJ_Products_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

    That should give you the multi value data. I have added an example single value lookup field called ProjectStatus so you will need to modify this query for your own fields but it should give you an idea of how to get the data.

    Thanks

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com
    Tuesday, September 6, 2011 4:48 PM
    Moderator

All replies

  • Hi Darren,

    Project Level custom fields that use a single value from a look table will appear in that view. Are you talking about multi value lookup tables?

    Thanks

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com
    Tuesday, September 6, 2011 3:02 PM
    Moderator
  • Hi Paul,

     

    You are correct - I should have made that clear.  The field can have multiple values from a lookup table.

     

    Thanks


    • Edited by Darren.PM Tuesday, September 6, 2011 4:08 PM
    Tuesday, September 6, 2011 4:07 PM
  • Hi Darren,

    In that case you will see an association view for that mulit value lookup field. You will need to join to that view and also the MSP_EpmLookupTable to get the values. If you give me the custom field name I could mock up a query for you if that helps.

    Thanks

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com
    Tuesday, September 6, 2011 4:14 PM
    Moderator
  • Hi Paul,

    Thanks - I see the association view you are talking about but it's not clear how I do the join so your offer of help is appreciated!  The custom field name is "Products" and the lookup table that products uses is called "ProductsTable"

    Cheers

    Tuesday, September 6, 2011 4:25 PM
  • Hi Darren,

    Try this query below against the PWA_Reporting database:

     SELECT   proj.ProjectName
      , proj.ProjectStatus -- example single value lookup field
      , lt.MemberFullValue AS N'Products'
    FROM   dbo.MSP_EpmProject_UserView AS proj
    LEFT OUTER JOIN  [dbo].[MSPCFPRJ_Products_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

    That should give you the multi value data. I have added an example single value lookup field called ProjectStatus so you will need to modify this query for your own fields but it should give you an idea of how to get the data.

    Thanks

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com
    Tuesday, September 6, 2011 4:48 PM
    Moderator
  • Hi Paul,

     

    Thanks for this.  Your query worked well but it only returns the first selected value if multiple values are selected for a project.

     

    I messed about with your query to come up with this:

     

    SELECT  proj.ProjectName
       , proj.ProjectStatus-- example single value lookup field
       , lt.MemberValue AS N'Products'
     FROM   dbo.MSP_EpmProject_UserView AS proj
     LEFT OUTER JOIN  [dbo].[MSPCFPRJ_Products_AssociationView] AS MVassoc -- view for multi value field
      ON  proj.ProjectUID = MVassoc.EntityUID
     LEFT OUTER JOIN dbo.[MSPLT_1E Products_UserView] AS lt
      ON  MVassoc.LookupMemberUID = lt.LookupMemberUID
     order by  proj.ProjectName asc

     

    It now returns all of the values.  Its not perfect as I would prefer it to be in the format of one line per project and maybe concatenate each product with a ",".  Thanks for your help - I wouldn't have got this without your help.

     

    Cheers

    Wednesday, September 7, 2011 1:01 PM
  • Hi Darren,

    Glad this helped, strange it didn't return all the values for you, it did on my environment, but as you say it will return multiple rows for projects with more than one value. To resolve this you could write a function.

    Thanks

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com
    Wednesday, September 7, 2011 1:15 PM
    Moderator
  • Hi Darren,

    Further to this, this may help you with getting the values on one row:

    http://jagbarcelo.blogspot.com/2007/02/how-to-convert-several-rows-into-csv.html

    There will be many ways you could do this, have search and you will find something :)

    On a side note, the MSPLT_ProductsLT_UserView is just a filtered version of the MSP_EpmLookupTable, still doesn't explain why you couldn't see all values projects, I ran both queries on my SQL server and got the same results returned with both versions.

    Thanks

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com
    Wednesday, September 7, 2011 2:00 PM
    Moderator