none
Fetch data from multiple selection lookup field from project server published database RRS feed

  • Question

  • Hi,

    Regarding my SSRS report in need to fetch data of multiple selection look up fields value in single row with comma delimitted, can any one help me to find which tables or views in published database contain this information.

    Like:

    Project Name       Project Type(Mulitple Selection Lookup Field)

    Demo                     A,B,C

    Test                       A,C,D

    So on..

     

     


    Sachin Vashishth MCTS
    • Moved by Alexander.BurtonModerator Tuesday, December 14, 2010 8:40 AM Customisation / Programming Question (From:Project Server General Questions and Answers)
    Tuesday, December 14, 2010 6:25 AM

Answers

  • Yes, reporting DB does have the multi select values, as you could see below, all the Tables referenced are from reporting DB, i have meade them bold, you will get all values of custom field values checked in multiple rows, here is when you need to get them in one single row :)  

    SELECT     MSP_EpmProject_UserView.ProjectUID,MSP_EpmProject_UserView.ProjectName, [MSPLT_Country Project Team_UserView].MemberValue AS [R10Value]

    INTO #CountryInfo

    FROM         MSP_EpmProject_UserView INNER JOIN

                          [MSPCFPRJ_Country Project Team_AssociationView] ON

                          MSP_EpmProject_UserView.ProjectUID = [MSPCFPRJ_Country Project Team_AssociationView].EntityUID INNER JOIN

                          [MSPLT_Country Project Team_UserView] ON

                          [MSPCFPRJ_Country Project Team_AssociationView].LookupMemberUID = [MSPLT_Country Project Team_UserView].LookupMemberUID


    Thanks | Sunil Kr Singh | http://epmxperts.wordpress.com
    Tuesday, December 14, 2010 9:44 AM
    Moderator
  • Hi Sachin,

    I have to apologize - I didn't read carefully enough in the first instance. I missed "multi value".

    Sunil gave you the good answer in the meantime.

    Just a short description: 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.

    By the way: it works in the same way for entities resource and task, linking ResourceUID or TaskUID to EntityUID in the respective views.

    Good luck!
    Barbara

    Tuesday, December 14, 2010 10:38 AM
    Moderator

All replies

  • Hi,

    you haven't mention the version you are working with. But in this case, the answer is the same. In Reporting DB, you will find views named ...._Userview. These views exist for each entity like assignment, task, resource and project. So in your case you are interested in ....Project_Userview. All custom fields are added automatically to these views and you can find them in there (at the end).

    Does that help?
    Barbara

    Tuesday, December 14, 2010 8:05 AM
    Moderator
  • Hi Sachin

    To get all the multi select custom field values in one row, i used a cursor to rewrite the custom field values to get transposed in single row, as we get all those values in multiple rows :) assuming this is the same problem you are facing,

    Here is the query i wrote, i know it’s not an efficient query so for performance,like using table variable instead of temp table :), you would need to tweak it but just would help in giving some pointers, i know there could be better ways to do it, but this was just written as draft for achieving same kind of results, but was obviously worked on by SQL team and refined

    /*********************** GETTING CUSTOM FIELD TABLE DATA *****************************************/

    SELECT     MSP_EpmProject_UserView.ProjectUID,MSP_EpmProject_UserView.ProjectName, [MSPLT_Country Project Team_UserView].MemberValue AS [R10Value]

    INTO #CountryInfo

    FROM         MSP_EpmProject_UserView INNER JOIN

                          [MSPCFPRJ_Country Project Team_AssociationView] ON

                          MSP_EpmProject_UserView.ProjectUID = [MSPCFPRJ_Country Project Team_AssociationView].EntityUID INNER JOIN

                          [MSPLT_Country Project Team_UserView] ON

                          [MSPCFPRJ_Country Project Team_AssociationView].LookupMemberUID = [MSPLT_Country Project Team_UserView].LookupMemberUID

    /******************GETTING REST OF THE PROJECT RELEVANT DATA**********************************/

    SELECT

         p.ProjectUID,

         p.ProjectName AS [Project Name],

         p.ProjectModifiedDate AS [Last Modified],

         po.ResourceName AS [PM],

         p.ProjectPercentCompleted AS [Percent Completed],

         p.ProjectCondition As [Condition],

         p.OverAllStatus As [OverAllStatus],

          (

          ISNULL(#r.[Risk KPI],1) +

          ISNULL(#i.[Issue KPI],1))/5 AS [Overall KPI],

          ISNULL(#r.[Risk KPI],1) AS [Risk KPI],

          ISNULL(#i.[Issue KPI],1) AS [Issue KPI],

          ISNULL(#skpi.[Schedule KPI],1) AS [Schedule KPI],

                'http://servername/pwa/ProjectDrillDown.aspx?_projectUID=' + convert(char(38),p.ProjectUID) AS [Project Detail View URL],

         'http://servername/pwa/' + RTRIM(convert(char(100),p.ProjectName)) AS [Project Workspace URL]

    into #Proj

    FROM

         dbo.MSP_EpmProject_UserView p LEFT OUTER JOIN

         dbo.MSP_EpmProjectOwner_OlapView po ON

              p.ProjectOwnerResourceUID = po.ResourceUID LEFT OUTER JOIN

         #r ON

              p.ProjectUID = #r.ProjectUID LEFT OUTER JOIN

         #i ON

              p.ProjectUID = #i.ProjectUID LEFT OUTER JOIN

         #skpi ON

             p.ProjectUID = #skpi.ProjectUID

    ALTER TABLE #Proj ADD Contry_PM VARCHAR(20) NULL ;

    ALTER TABLE #Proj ADD Country_QA VARCHAR(20) NULL ;

    ALTER TABLE #Proj ADD Country_Dev VARCHAR(20) NULL ;

    ALTER TABLE #Proj ADD Country_Arch VARCHAR(20) NULL ;

    ALTER TABLE #Proj ADD Country_Usability VARCHAR(20) NULL ;

    /***************************UPDATE #Proj TABLE VALUES FOR COUNTRY***********************/

    DECLARE @prj_id varchar(100), @val varchar(20)
    DECLARE r10_val CURSOR FOR SELECT projectuid,r10value FROM # CountryInfo
    OPEN r10_val
    FETCH NEXT FROM r10_val INTO @prj_id,@val
    WHILE @@FETCH_STATUS = 0
    BEGIN

          if (@val='PM')

          begin

                update #Proj set Contry_PM = 'Yes' where ProjectUID=@prj_id

          end

          else if (@val='QA')

          begin

                update #Proj set Country_QA = 'Yes' where ProjectUID=@prj_id

          end

          else if (@val='Dev')

          begin

                update #Proj set Country_Dev = 'Yes' where ProjectUID=@prj_id

          end

          else if (@val='Architect')

          begin

                update #Proj set Country_Arch = 'Yes' where ProjectUID=@prj_id

          end

          else if (@val='Usability')

          begin

                update #Proj set Country_Usability = 'Yes' where ProjectUID=@prj_id

          end

    FETCH NEXT FROM r10_val INTO @prj_id,@val

    End

    DROP TABLE #r

    DROP TABLE #i

    DROP TABLE # CountryInfo

    DROP TABLE #skpi

    close r10_val

    deallocate r10_val

    Select * From #Proj where Condition!='Completed' AND Country_PM = 'Yes' or Country_QA = 'Yes' or INDIA_Dev = 'Yes' or Country_Arch = 'Yes' or Country_Usability = 'Yes'

    DROP Table #Proj

     

     

     


    Thanks | Sunil Kr Singh | http://epmxperts.wordpress.com
    Tuesday, December 14, 2010 8:37 AM
    Moderator
  • Thanks Barbara,

    Sorry I forget to mention, I am still working wid PS 2007.

    One thing I want to clear, is reporting db contain value from look up custom fields which have allow multiple selection true, I think for that we need to query published db.

    Correct me if I am wrong and pls if u hav any query related to such requirement pls fwd it to me.

     

     


    Sachin Vashishth MCTS
    Tuesday, December 14, 2010 9:19 AM
  • Thanks Sunil Sir, I understand that I have to iterate row vise so that I can collect all the custom field values related to particular project in a single row, Sir I have one doubt can I get this information from reporting database, as per my basic understanding reporting db does not contain multiple selection look up field values. Correct me if I am wrong.
    Sachin Vashishth MCTS
    Tuesday, December 14, 2010 9:34 AM
  • Yes, reporting DB does have the multi select values, as you could see below, all the Tables referenced are from reporting DB, i have meade them bold, you will get all values of custom field values checked in multiple rows, here is when you need to get them in one single row :)  

    SELECT     MSP_EpmProject_UserView.ProjectUID,MSP_EpmProject_UserView.ProjectName, [MSPLT_Country Project Team_UserView].MemberValue AS [R10Value]

    INTO #CountryInfo

    FROM         MSP_EpmProject_UserView INNER JOIN

                          [MSPCFPRJ_Country Project Team_AssociationView] ON

                          MSP_EpmProject_UserView.ProjectUID = [MSPCFPRJ_Country Project Team_AssociationView].EntityUID INNER JOIN

                          [MSPLT_Country Project Team_UserView] ON

                          [MSPCFPRJ_Country Project Team_AssociationView].LookupMemberUID = [MSPLT_Country Project Team_UserView].LookupMemberUID


    Thanks | Sunil Kr Singh | http://epmxperts.wordpress.com
    Tuesday, December 14, 2010 9:44 AM
    Moderator
  • Hi Sachin,

    I have to apologize - I didn't read carefully enough in the first instance. I missed "multi value".

    Sunil gave you the good answer in the meantime.

    Just a short description: 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.

    By the way: it works in the same way for entities resource and task, linking ResourceUID or TaskUID to EntityUID in the respective views.

    Good luck!
    Barbara

    Tuesday, December 14, 2010 10:38 AM
    Moderator
  • Thanks sir...

    Its work for me....now I'll fetch it row wise using cursor.


    Sachin Vashishth MCTS
    Tuesday, December 14, 2010 10:47 AM
  • Thnaks,

    Great explanation Barbara..now um able to fetch those information..


    Sachin Vashishth MCTS
    Tuesday, December 14, 2010 10:48 AM