Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
Deactivating an Enterprise Resource - Detect remaining work using a SQL query RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi All

    Currently, I´m reading the Implementing and Administering M PS 2010 book (Chefetz), and on the page 398 mention the following, "Before you deactivate any resource, you should confirm with your project manager that the resource has no remaining work value greater than 0 hours in any project an the portfolio.", so I´m trying to find out how to detect this remaining using a SQL query in the reporting database... Can anyone help me with the sql query? I'm trying to execute some kind of validations before deactivate an Enterprise Resource...

    Thanks for your help.

    Javier Perez

    Wednesday, January 2, 2013 4:51 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Javier,

    You can use  [MSP_EpmResource_UserView], [MSP_EpmAssignment_UserView] and [MSP_EpmTask_UserView] Views from reporting database. You can guess the relationship between these views easily. 

    HTH

    Thanks, Kashif

    Wednesday, January 2, 2013 5:30 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Javier,

    Kashif is correct, here a sample:

    SELECT    dbo.MSP_EpmProject_UserView.ProjectName
        , dbo.MSP_EpmTask_UserView.TaskName
        , dbo.MSP_EpmResource_UserView.ResourceName
        , dbo.MSP_EpmAssignment_UserView.AssignmentRemainingWork
FROM         dbo.MSP_EpmAssignment_UserView INNER JOIN
                      dbo.MSP_EpmTask_UserView 
                      ON dbo.MSP_EpmAssignment_UserView.TaskUID = dbo.MSP_EpmTask_UserView.TaskUID 
                      INNER JOIN
                      dbo.MSP_EpmResource_UserView 
                      ON dbo.MSP_EpmAssignment_UserView.ResourceUID = dbo.MSP_EpmResource_UserView.ResourceUID 
                      INNER JOIN
                      dbo.MSP_EpmProject_UserView 
                      ON dbo.MSP_EpmTask_UserView.ProjectUID = dbo.MSP_EpmProject_UserView.ProjectUID
WHERE     (dbo.MSP_EpmAssignment_UserView.AssignmentRemainingWork > 0) 
      AND (dbo.MSP_EpmResource_UserView.ResourceName LIKE @ParmRes + N'%')
ORDER BY dbo.MSP_EpmProject_UserView.ProjectName, dbo.MSP_EpmTask_UserView.TaskName
    Regards
    Barbara
    Wednesday, January 2, 2013 5:50 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Javier,

    You can use  [MSP_EpmResource_UserView], [MSP_EpmAssignment_UserView] and [MSP_EpmTask_UserView] Views from reporting database. You can guess the relationship between these views easily. 

    HTH

    Thanks, Kashif

    Wednesday, January 2, 2013 5:30 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Javier,

    Kashif is correct, here a sample:

    SELECT    dbo.MSP_EpmProject_UserView.ProjectName
        , dbo.MSP_EpmTask_UserView.TaskName
        , dbo.MSP_EpmResource_UserView.ResourceName
        , dbo.MSP_EpmAssignment_UserView.AssignmentRemainingWork
FROM         dbo.MSP_EpmAssignment_UserView INNER JOIN
                      dbo.MSP_EpmTask_UserView 
                      ON dbo.MSP_EpmAssignment_UserView.TaskUID = dbo.MSP_EpmTask_UserView.TaskUID 
                      INNER JOIN
                      dbo.MSP_EpmResource_UserView 
                      ON dbo.MSP_EpmAssignment_UserView.ResourceUID = dbo.MSP_EpmResource_UserView.ResourceUID 
                      INNER JOIN
                      dbo.MSP_EpmProject_UserView 
                      ON dbo.MSP_EpmTask_UserView.ProjectUID = dbo.MSP_EpmProject_UserView.ProjectUID
WHERE     (dbo.MSP_EpmAssignment_UserView.AssignmentRemainingWork > 0) 
      AND (dbo.MSP_EpmResource_UserView.ResourceName LIKE @ParmRes + N'%')
ORDER BY dbo.MSP_EpmProject_UserView.ProjectName, dbo.MSP_EpmTask_UserView.TaskName
    Regards
    Barbara
    Wednesday, January 2, 2013 5:50 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thanks for your help Kashif.

    Javier Perez

    Thursday, January 3, 2013 3:56 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thanks for your help Barbara.

    Javier Perez

    Thursday, January 3, 2013 3:56 PM