none
Project Server 2007 - Get the PM who after some time does not approve the task RRS feed

  • Question

  • Hi Every one, 

    I have some trouble to getting the person who after some time does not approve the task, that their team sended to approve for update % of work of the calendar, I need to put this result in a custom list, where in list all this PM's and which project and task is it, I don't have problems to pass the info to the list, but how can I get this information, please help, I tried to get this info via database but in a publish database this info is binary :S so I can't manipulate.

     

    Thanks!

     

     

     

     


    Raymundo Chapa, MCTS http://blogs.inavant.com.mx/ http://projectaserverzone.blogspot.com/
    Thursday, January 19, 2012 12:01 AM

Answers

  • Hello There,

    You can get the similar information by using the project server 2007's report pack 2. It contains the Task Compliance report which works with Project server 2010 too with few minor chnages.
    You can download the Report from :
    http://archive.msdn.microsoft.com/EPMReportPack

    Here is the SQL query:

    /*
    	EPM Governance - Task Update Compliance
    		Summary Information about EPM task updates is returned and filtered
    		by a specified time period.
    */
    Declare @sqlquery1 nvarchar(max)
    Declare @PublishedDB nvarchar(100)
    Declare @ReportingDB nvarchar(100)
    
    -- Set DB variable to name of EPM Reporting database in your environment.
    Set @ReportingDB = N'ProjectServer2007_Litware_Reporting'
    -- Set DB variable to name of EPM Published database in your environment.
    Set @PublishedDB = N'ProjectServer2007_Litware_Published'
    
    /* These declarations are commented out in reporting services because it will be
    	the reporting services parameters that bind directly to the parameters in the
    	query below. But they are included here for your convenience because
    	when you copy out the query to run directly against SQL Server, it is handy
    	to have the parameters declarations here to help you test values.
    */
    /*
    Declare @RP_StartDate datetime
    Declare @RP_FinishDate datetime
    declare @RP_RBS nvarchar(255)
    declare @RP_Teams nvarchar (255)
    -- Set the Start and Finish Dates that filter the range of data to be examined
    set @RP_StartDate = N'11/1/2006'
    set @RP_FinishDate = N'12/31/2008'
    -- Set value for RBS filter
    set @RP_RBS = N'Litware.CIO.IT PMO'
    --Set value for Team filter
    set @RP_Teams = N'Teams.IT Teams.PMO Team'
    */
    
    set @sqlquery1 = N'use [' + @PublishedDB + N']
    
    SELECT     
    	isnull(MSP_PROJECTS.PROJ_NAME,''<No Project Information Available>'') as [Project], 
    	isnull(MSP_TASKS.TASK_NAME,''<No Task Information Available>'') as [Task], 
    	isnull(MSP_RESOURCES_2.RES_NAME,''<No Approver Information Available>'') as [Approver], 
    	MSP_RESOURCES_1.RES_NAME AS Submitter,
    	isnull(ruv.RBS,''<No RBS Information Available>'') as [RBS],
    	isnull(ruv.[Team Name],''<No Team Information Available>'') as [Team Name],
    	isnull(MSP_RESOURCES.RES_NAME,''<No Status Manager Information Available>'') as [Status Manager], 
    
    	case when (MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_STATE_ENUM = 0 
    		and MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_ACTION_ENUM = 0
    		AND MSP_PROJECTS.PROJ_NAME IS NOT NULL) then 1 end as [Saved but not Submitted],
    
    	case when (MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_STATE_ENUM = 3 
    		and MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_ACTION_ENUM in (1, 2)
    		AND MSP_PROJECTS.PROJ_NAME IS NOT NULL ) then 1 end as [Submitted and Processed],
    
    	case when (MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_STATE_ENUM = 1 
    		and MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_ACTION_ENUM = 0
    		AND MSP_PROJECTS.PROJ_NAME IS NOT NULL ) then 1 end as [Submitted but Not Processed],
    
    	case when (MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_STATE_ENUM = 4 
    		and MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_ACTION_ENUM in (1,2)
    		AND MSP_PROJECTS.PROJ_NAME IS NOT NULL  ) then 1 end as [Submitted and Failed to Process],
    
    	''Tx Type'' = CASE 
    	   WHEN MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_TYPE_ENUM = 0 THEN ''Task Assignment Update''
    	   WHEN MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_TYPE_ENUM = 1 THEN ''Declined Task Assignment''
    	   WHEN MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_TYPE_ENUM = 2 THEN ''Create Task Request''
    	   WHEN MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_TYPE_ENUM = 3 THEN ''Delegate Task Assignment''
    	   WHEN MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_TYPE_ENUM = 4 THEN ''Request''
    	   WHEN MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_TYPE_ENUM = 5 THEN ''Create Task Assignment Request''
    	   WHEN MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_TYPE_ENUM = 6 THEN ''Team delegation Request''
    	   WHEN MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_TYPE_ENUM = 7 THEN ''Delete Task Request''
    	ELSE ''Unknown'' END,
    	''Status Manager Action'' = CASE 
    	   WHEN MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_ACTION_ENUM = 0 THEN ''Pending''
    	   WHEN MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_ACTION_ENUM = 1 THEN ''Approved''
    	   WHEN MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_ACTION_ENUM = 2 THEN ''Rejected''
    	ELSE ''Unknown'' END,
    	MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_STATE_ENUM as ''Update Status'', 
    	MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_ACTION_ENUM as ''Update Action'', 
    	MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_TYPE_ENUM as ''Update Type'', 
    	MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_ERROR_ENUM ''Update Error'', 
    	-- MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_UPDATE_DATE, DATEDIFF(MINUTE, { fn NOW() }, 
    	--  MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_SUBMIT_DATE) AS Expr1,
    	MSP_ASSIGNMENT_TRANSACTIONS.CREATED_DATE, 
    	MSP_ASSIGNMENT_TRANSACTIONS.MOD_DATE'
    
    set @sqlquery1 = @sqlquery1 + N'
    FROM 
    MSP_PROJECTS 
    RIGHT OUTER JOIN
    MSP_ASSIGNMENTS 
    ON MSP_PROJECTS.PROJ_UID = MSP_ASSIGNMENTS.PROJ_UID 
    LEFT OUTER JOIN
    MSP_TASKS 
    ON MSP_ASSIGNMENTS.TASK_UID = MSP_TASKS.TASK_UID 
    RIGHT OUTER JOIN
    MSP_ASSIGNMENT_TRANSACTIONS 
    ON MSP_ASSIGNMENTS.ASSN_UID = MSP_ASSIGNMENT_TRANSACTIONS.ASSN_UID 
    LEFT OUTER JOIN
    MSP_RESOURCES AS MSP_RESOURCES_1 
    ON MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_SUBMITTER_RES_UID = MSP_RESOURCES_1.RES_UID 
    LEFT OUTER JOIN
    MSP_RESOURCES AS MSP_RESOURCES_2 
    ON MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_APPROVER_RES_UID = MSP_RESOURCES_2.RES_UID 
    LEFT OUTER JOIN
    MSP_RESOURCES 
    ON MSP_ASSIGNMENTS.WRES_UID_MANAGER = MSP_RESOURCES.RES_UID
    left outer join
    ' + @ReportingDB +  N'..msp_epmresource_userview ruv
    on ruv.ResourceUID = MSP_RESOURCES_1.RES_UID
    
    where
    MSP_ASSIGNMENT_TRANSACTIONS.CREATED_DATE  between  ''' 
    + cast(@RP_StartDate as nvarchar(50)) 
    + N''' and ''' + cast(@RP_FinishDate as nvarchar(50)) + N'''
    
    AND MSP_PROJECTS.PROJ_NAME IS NOT NULL
    
    and ruv.RBS in (''' + @RP_RBS + N''')'
    
    set @sqlquery1 = @sqlquery1 + N'
    
    and ruv.[Team Name] in (''' + @RP_Teams +  N''')
    
    ORDER BY Project, Task, mod_date
    '
    
    exec (@sqlquery1)
    


    Hope that helps.



    Thanks, Amit Khare |EPM Consultant| Blog: http://amitkhare82.blogspot.com http://www.linkedin.com/in/amitkhare82
    • Marked as answer by Ray Chapa Monday, January 23, 2012 11:09 PM
    Thursday, January 19, 2012 6:33 AM

All replies

  • Hello There,

    You can get the similar information by using the project server 2007's report pack 2. It contains the Task Compliance report which works with Project server 2010 too with few minor chnages.
    You can download the Report from :
    http://archive.msdn.microsoft.com/EPMReportPack

    Here is the SQL query:

    /*
    	EPM Governance - Task Update Compliance
    		Summary Information about EPM task updates is returned and filtered
    		by a specified time period.
    */
    Declare @sqlquery1 nvarchar(max)
    Declare @PublishedDB nvarchar(100)
    Declare @ReportingDB nvarchar(100)
    
    -- Set DB variable to name of EPM Reporting database in your environment.
    Set @ReportingDB = N'ProjectServer2007_Litware_Reporting'
    -- Set DB variable to name of EPM Published database in your environment.
    Set @PublishedDB = N'ProjectServer2007_Litware_Published'
    
    /* These declarations are commented out in reporting services because it will be
    	the reporting services parameters that bind directly to the parameters in the
    	query below. But they are included here for your convenience because
    	when you copy out the query to run directly against SQL Server, it is handy
    	to have the parameters declarations here to help you test values.
    */
    /*
    Declare @RP_StartDate datetime
    Declare @RP_FinishDate datetime
    declare @RP_RBS nvarchar(255)
    declare @RP_Teams nvarchar (255)
    -- Set the Start and Finish Dates that filter the range of data to be examined
    set @RP_StartDate = N'11/1/2006'
    set @RP_FinishDate = N'12/31/2008'
    -- Set value for RBS filter
    set @RP_RBS = N'Litware.CIO.IT PMO'
    --Set value for Team filter
    set @RP_Teams = N'Teams.IT Teams.PMO Team'
    */
    
    set @sqlquery1 = N'use [' + @PublishedDB + N']
    
    SELECT     
    	isnull(MSP_PROJECTS.PROJ_NAME,''<No Project Information Available>'') as [Project], 
    	isnull(MSP_TASKS.TASK_NAME,''<No Task Information Available>'') as [Task], 
    	isnull(MSP_RESOURCES_2.RES_NAME,''<No Approver Information Available>'') as [Approver], 
    	MSP_RESOURCES_1.RES_NAME AS Submitter,
    	isnull(ruv.RBS,''<No RBS Information Available>'') as [RBS],
    	isnull(ruv.[Team Name],''<No Team Information Available>'') as [Team Name],
    	isnull(MSP_RESOURCES.RES_NAME,''<No Status Manager Information Available>'') as [Status Manager], 
    
    	case when (MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_STATE_ENUM = 0 
    		and MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_ACTION_ENUM = 0
    		AND MSP_PROJECTS.PROJ_NAME IS NOT NULL) then 1 end as [Saved but not Submitted],
    
    	case when (MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_STATE_ENUM = 3 
    		and MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_ACTION_ENUM in (1, 2)
    		AND MSP_PROJECTS.PROJ_NAME IS NOT NULL ) then 1 end as [Submitted and Processed],
    
    	case when (MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_STATE_ENUM = 1 
    		and MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_ACTION_ENUM = 0
    		AND MSP_PROJECTS.PROJ_NAME IS NOT NULL ) then 1 end as [Submitted but Not Processed],
    
    	case when (MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_STATE_ENUM = 4 
    		and MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_ACTION_ENUM in (1,2)
    		AND MSP_PROJECTS.PROJ_NAME IS NOT NULL  ) then 1 end as [Submitted and Failed to Process],
    
    	''Tx Type'' = CASE 
    	   WHEN MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_TYPE_ENUM = 0 THEN ''Task Assignment Update''
    	   WHEN MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_TYPE_ENUM = 1 THEN ''Declined Task Assignment''
    	   WHEN MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_TYPE_ENUM = 2 THEN ''Create Task Request''
    	   WHEN MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_TYPE_ENUM = 3 THEN ''Delegate Task Assignment''
    	   WHEN MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_TYPE_ENUM = 4 THEN ''Request''
    	   WHEN MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_TYPE_ENUM = 5 THEN ''Create Task Assignment Request''
    	   WHEN MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_TYPE_ENUM = 6 THEN ''Team delegation Request''
    	   WHEN MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_TYPE_ENUM = 7 THEN ''Delete Task Request''
    	ELSE ''Unknown'' END,
    	''Status Manager Action'' = CASE 
    	   WHEN MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_ACTION_ENUM = 0 THEN ''Pending''
    	   WHEN MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_ACTION_ENUM = 1 THEN ''Approved''
    	   WHEN MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_ACTION_ENUM = 2 THEN ''Rejected''
    	ELSE ''Unknown'' END,
    	MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_STATE_ENUM as ''Update Status'', 
    	MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_ACTION_ENUM as ''Update Action'', 
    	MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_TYPE_ENUM as ''Update Type'', 
    	MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_ERROR_ENUM ''Update Error'', 
    	-- MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_UPDATE_DATE, DATEDIFF(MINUTE, { fn NOW() }, 
    	--  MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_SUBMIT_DATE) AS Expr1,
    	MSP_ASSIGNMENT_TRANSACTIONS.CREATED_DATE, 
    	MSP_ASSIGNMENT_TRANSACTIONS.MOD_DATE'
    
    set @sqlquery1 = @sqlquery1 + N'
    FROM 
    MSP_PROJECTS 
    RIGHT OUTER JOIN
    MSP_ASSIGNMENTS 
    ON MSP_PROJECTS.PROJ_UID = MSP_ASSIGNMENTS.PROJ_UID 
    LEFT OUTER JOIN
    MSP_TASKS 
    ON MSP_ASSIGNMENTS.TASK_UID = MSP_TASKS.TASK_UID 
    RIGHT OUTER JOIN
    MSP_ASSIGNMENT_TRANSACTIONS 
    ON MSP_ASSIGNMENTS.ASSN_UID = MSP_ASSIGNMENT_TRANSACTIONS.ASSN_UID 
    LEFT OUTER JOIN
    MSP_RESOURCES AS MSP_RESOURCES_1 
    ON MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_SUBMITTER_RES_UID = MSP_RESOURCES_1.RES_UID 
    LEFT OUTER JOIN
    MSP_RESOURCES AS MSP_RESOURCES_2 
    ON MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_APPROVER_RES_UID = MSP_RESOURCES_2.RES_UID 
    LEFT OUTER JOIN
    MSP_RESOURCES 
    ON MSP_ASSIGNMENTS.WRES_UID_MANAGER = MSP_RESOURCES.RES_UID
    left outer join
    ' + @ReportingDB +  N'..msp_epmresource_userview ruv
    on ruv.ResourceUID = MSP_RESOURCES_1.RES_UID
    
    where
    MSP_ASSIGNMENT_TRANSACTIONS.CREATED_DATE  between  ''' 
    + cast(@RP_StartDate as nvarchar(50)) 
    + N''' and ''' + cast(@RP_FinishDate as nvarchar(50)) + N'''
    
    AND MSP_PROJECTS.PROJ_NAME IS NOT NULL
    
    and ruv.RBS in (''' + @RP_RBS + N''')'
    
    set @sqlquery1 = @sqlquery1 + N'
    
    and ruv.[Team Name] in (''' + @RP_Teams +  N''')
    
    ORDER BY Project, Task, mod_date
    '
    
    exec (@sqlquery1)
    


    Hope that helps.



    Thanks, Amit Khare |EPM Consultant| Blog: http://amitkhare82.blogspot.com http://www.linkedin.com/in/amitkhare82
    • Marked as answer by Ray Chapa Monday, January 23, 2012 11:09 PM
    Thursday, January 19, 2012 6:33 AM
  • Hi, this qry I can not get the information of administrative time, you know why?
    Thursday, June 4, 2015 5:05 PM