none
Resource Availability - Assignment Work By Resource RRS feed

  • Question

  • Hi Gys. I hope someone can help me.

    I´m creating a SSRS project and a need to know in which table and what is the name of this field "Availability" at database level or the data source of the complete report.

    Friday, May 3, 2019 5:49 PM

Answers

  • Hi,

    there is no "Availability". You have to calculate the availability on your own be using "Capacity" from MSP_EpmResource_UserView and Work from MSP_EpmAssignment_UserView.

    Regards
    Barbara

    Sunday, May 5, 2019 12:11 PM
    Moderator

All replies

  • Hi,

    there is no "Availability". You have to calculate the availability on your own be using "Capacity" from MSP_EpmResource_UserView and Work from MSP_EpmAssignment_UserView.

    Regards
    Barbara

    Sunday, May 5, 2019 12:11 PM
    Moderator
  • Thank you very much.

    You're advice put me on the right way.This is the query I needed, it's very simple but fulfills purpose.

    SELECT
            D.ResourceName, C.ProjectName, B.AssignmentWork,(SELECT Capacity FROM MSP_EpmResourceByDay_UserView WHERE TimeByDay = B.TimeByDay AND ResourceUID = A.ResourceUID) Capacity,
            B.TimeByDay     
    FROM
            MSP_EpmAssignment_UserView A
    JOIN
            MSP_EpmAssignmentByDay_UserView B
    ON
            A.ProjectUID = B.ProjectUID AND
            A.AssignmentUID = B.AssignmentUID AND
            A.TaskUID = B.TaskUID
    JOIN
            MSP_EpmProject_UserView C
    ON
            B.ProjectUID = C.ProjectUID
    JOIN
            MSP_EpmResource_UserView D
    ON
            A.ResourceUID = D.ResourceUID
    WHERE
             A.ResourceUID = @ResourceID AND
             B.TimeByDay  BEETWEN @StartDate AND  @FinishDate
    ORDER BY  A.ResourceUID,B.ProjectUID, B.AssignmentUID, B.TaskUID, B.TimeByDate

    We can modify the query to summarize by week, month or year, even apply pivot tables.

    Tuesday, May 7, 2019 9:23 PM