none
MS Project 2010: SQL Query Expansion to custom field RRS feed

  • Question

  • In a further forum topic I got the solution for searching for server admins approvals pending, not publisched projects and team members not sended times.

    We defined a new enterprise custom field called "Status reporting" from type Flag (yes/no).

    Can someone help me to expand the following MS SQL query get the state of "Status Reporting" in the solution table?

    SELECT DISTINCT  R.RES_NAME as [RESOURCE NAME], RB.RES_NAME STATUS_MANAGER, P.PROJ_NAME,A.TASK_NAME,
    [STATUS] = Case TR.ASSN_TRANS_ACTION_ENUM When 0 Then
    'Pending' When 1 Then 'Accepted' When 2 Then 'Rejected' End,
    IS_PUBLISHED = CASE WHEN P.WPROJ_LAST_PUB >TR.ASSN_TRANS_SUBMIT_DATE THEN 'Yes' ELSE 'No' END,
    TASKSTATUS = CASE WHEN AU.ASSN_UID IS NULL AND TR.ASSN_TRANS_ACTION_ENUM = 0 THEN 'Saved' ELSE 'Submitted' END,
    BASELINED = CASE AB_BASE_NUM WHEN 0 THEN 'Yes' ELSE 'No' END
     FROM MSP_ASSIGNMENT_TRANSACTIONS TR WITH(READPAST)
    LEFT OUTER JOIN dbo.MSP_CONVERSIONS C ON ((C.CONV_VALUE =
    TR.ASSN_TRANS_ERROR_ENUM) AND (STRING_TYPE_UID = 'A16A340A-4AD1-4F62-880C-564C45021502'))
    LEFT OUTER JOIN MSP_ASSIGNMENTS_SAVED A WITH(READPAST) ON TR.ASSN_UID = A.ASSN_UID
    LEFT OUTER JOIN MSP_ASSIGNMENTS_SUBMITTED AU WITH(READPAST) ON TR.ASSN_UID = AU.ASSN_UID
    LEFT OUTER JOIN MSP_ASSIGNMENT_BASELINES AB WITH(READPAST) ON TR.ASSN_UID = AB.ASSN_UID AND AB.AB_BASE_NUM = 0
    LEFT OUTER JOIN MSP_PROJECTS P ON A.PROJ_UID = P.PROJ_UID
    LEFT OUTER JOIN MSP_TASKS_SAVED TA ON TR.TASK_UID = TA.TASK_UID
    LEFT OUTER JOIN MSP_PROJECT_RESOURCES R ON A.RES_UID = R.RES_UID AND R.PROJ_UID = P.PROJ_UID
    LEFT OUTER JOIN MSP_PROJECT_RESOURCES RD ON
    TR.ASSN_TRANS_DELEGATEE_RES_UID = RD.RES_UID AND P.PROJ_UID = RD.PROJ_UID
    LEFT OUTER JOIN MSP_RESOURCES RD1 ON TR.ASSN_TRANS_DELEGATEE_RES_UID = RD1.RES_UID
    LEFT OUTER JOIN MSP_PROJECT_RESOURCES RS ON
    TR.ASSN_TRANS_SUBMITTER_RES_UID = RS.RES_UID AND P.PROJ_UID = RS.PROJ_UID
    LEFT OUTER JOIN MSP_RESOURCES RS1 ON TR.ASSN_TRANS_SUBMITTER_RES_UID = RS1.RES_UID
    LEFT OUTER JOIN MSP_RESOURCES RA ON TR.ASSN_TRANS_APPROVER_RES_UID = RA.RES_UID
    LEFT OUTER JOIN MSP_RESOURCES RB ON A.WRES_UID_Manager = RB.RES_UID
    LEFT OUTER JOIN PS_TestFarm01__Prodcp20130606_Reporting.dbo.MSP_EpmResource mer on A.WRES_UID_Manager=mer.ResourceUID

      

    Murol

    Thursday, December 4, 2014 1:05 PM

Answers

All replies

  • Hello,

    From the table names it looks like you are querying the Published database tables? This in not supported, you should only query the Reporting Database when using T-SQL. Getting custom fields from the Reporting database is far easier too! Depending on what field the new "Status Reporting" is (Project, Task, Resource etc.) you will find user views in the Reporting database that contains this field. For example, if it was a project level field, you will find this field in the MSP_EPMProject_UserView.

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    Thursday, December 4, 2014 2:52 PM
    Moderator
  • Hello Paul,

    thank you for your answer.

    But, I haven't programmed the SQL query. I don't understand it, I only use it. I don't know how to get the information of the query from the reporting database as you described. I found the enterprise custom field called "Status reporting" in the MSP_EPMProject_UserView. But I don't know how to expand the query to get information about the enterprise custom field called "Status reporting".

    PS_TestFarm01__Prodcp20130606_Reporting -> dbo.MSP_EpmProject_UserView -> Columns -> Status Reporting (bit, not null)

    Kind regards

    Peter


    Murol

    Friday, December 5, 2014 8:00 AM
  • Try doing something like:

    LEFT OUTER JOIN PS_TestFarm01__Prodcp20130606_Reporting.dbo.MSP_EpmProject_UserView PV
    ON PV.ProjectUID = P.Proj_UID

    Then add PV.[Status Reporting] into the select.

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    • Marked as answer by Murol Tuesday, December 9, 2014 3:13 PM
    Tuesday, December 9, 2014 2:02 PM
    Moderator
  • Hello Paul,

    great, it works. I inserted:

    ,

    Statusreporting = CASE PV.[Status Reporting] WHEN 1 THEN 'Yes' ELSE 'No' END

    LEFT OUTER JOIN PS_TestFarm01__Prodcp20130606_Reporting.dbo.MSP_EpmProject_UserView PV
    ON PV.ProjectUID = P.Proj_UID

    The first line I understand a little bit but I don't understand  PV.[Status Reporting]

    But, the second line "LEFT OUTER ...", I doesn't understand

    Many thanks and kind regards

    Peter


    Murol

    Tuesday, December 9, 2014 2:41 PM
  • Glad to hear it is working for you.

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    Tuesday, December 9, 2014 2:47 PM
    Moderator