none
How can we create reports which shows working and non working days of resources along with Project and Task assigned on that particular day RRS feed

  • Question

  • Hi All,

    With the help of Paul I am writing a SQL query to develop a report which should

    • when my resources are on vacation
    • On the vacation day what are the assigned Tasks to those resources
    • In what all Projects they are assigned during the vacation

    I am able to get the first point with the help from Paul with the below query

    select R.ResourceName , RBD.Capacity , RBD.TimeByDay from MSP_EpmResource_UserView R INNER JOIN MSP_EpmResourceByDay_UserView RBD ON R.ResourceUID = RBD.ResourceUID where TimeByDay Between GetDate() and Getdate()+25

    How to join this query with Project and Tasks?

    I have tried doing but getting irrelevant data. This is the query which I used.

    select R.ResourceName
    , RBD.Capacity
    , RBD.TimeByDay, Proj.ProjectName, Task.TaskName
    from MSP_EpmResource_UserView R
    INNER JOIN MSP_EpmResourceByDay_UserView RBD
    ON R.ResourceUID = RBD.ResourceUID
    INNER JOIN MSP_EpmAssignmentByDay_UserView ABD
    ON ABD.TimeByDay =RBD.TimeByDay
    Inner join  MSP_EpmProject_UserView Proj
    ON          proj.ProjectUID = ABD.ProjectUID
    Inner join  MSP_EpmTask_UserView Task
    ON Task.TaskUID = ABD.TaskUID

    where RBD.TimeByDay Between GETDATE() and Getdate()+365 and DATEPART(W,RBD.[TimeByDay])NOT IN (7,1) and Capacity = 0

    Kindly suggest your inputs to achieve the desired result

    Thanks in advance.

    Abbas Khan


    Thursday, January 12, 2017 9:21 AM