none
How can we create reports which shows working and non working days of resources RRS feed

  • Question

  • Hi All,

    I am trying to create a report in excel which should show the working and non working days for the resources assigned in projects in project server 2013.

    Can we create such reports in project server 2013? Where database stores the non working time of resources.

    What would be the SQL query for the same?

    Thanks in advance.

    Abbas Khan

    Monday, January 9, 2017 9:01 AM

Answers

  • Hello,

    This should give you what you need:

    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()+5

    Update the TimeByDay filter as needed.

    Paul


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

    Monday, January 9, 2017 9:15 AM
    Moderator

All replies

  • Hello,

    This should give you what you need:

    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()+5

    Update the TimeByDay filter as needed.

    Paul


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

    Monday, January 9, 2017 9:15 AM
    Moderator
  • Hi PWMather,

    Thanks for quick reply.

    Just one more help, how can I exclude Saturday and Sunday in the query.

    Monday, January 9, 2017 10:51 AM
  • achieved it using datepart syntax.

    DATEPART(W,[TimeByDay])NOT IN (7,1).

    Thanks for helping :)

    Monday, January 9, 2017 1:58 PM
  • Hi Paul,

    What if I need to include Project and Task name where I have assigned users on those non working days?

    How can we modify the query?



    Wednesday, January 11, 2017 7:13 AM