How to find out resource's working and non working days in a month RRS feed

  • Question

  • Hi 

    I am working with project server 2010.

    For one of the requirements I will have to show resources allocation for each month in multiple projects in percentage (%). Allocation percentage should be calculated based on the resource planned work for each month (obtain from Task start and finish dates and get the result for desired duration) and divide it by No: of working days for the month (based on resource calendar).

    I would like to show this data in a SSRS report. But stuck at the logic needed to calculate the Allocation percentage for a month.

    Also any idea where the Enterprise calendar data is stored in reporting database??

    Please suggest.

    Sunday, July 28, 2013 7:50 PM

All replies

  • Hello,

    The two views in the PWA Reporting database that contain the key data you need are:

    MSP_EpmResourceByDay_UserView - for capacity
    MSP_EpmAssignmentByDay_UserView - for work


    Paul Mather | Twitter | | CPS

    Sunday, July 28, 2013 9:04 PM
  • Hi,

    Actually I was digging into these two views in the reporting database.

    1. [MSP_EpmAssignment_UserView]

    2. MSP_EpmResourceByDay_UserView - for capacity

    But when calculating work (assignment work) from [MSP_EpmAssignment_UserView] I am confused.


    Task1  from Project1 starts from JAN 25'2013 and Finishes on AUG 25'2013 and the assignment work is xxx (hours)

    Now how would I get the work (assignment work) for JAN, FEB,MAR....AUG separately. How will I get the breakup for each month here.

    Please suggest.

    Sunday, July 28, 2013 9:32 PM
  • Sudhir,

    You will have to use TimebyDay column for that. It should be aviulable in both of the tables that Paul mentioned.

    Prasanna Adavi,PMP,MCTS,MCITP,MCT

    Monday, July 29, 2013 12:05 AM
  • Ok Prasanna will check and try to use the TimeByDay column. 

    Also not sure why I am not able to see the base capacity and capacity of all resources in MSP_EpmResourceByDay_UserView from APR 2012 onwards. Though the resource calendar is associated with the resource profiles.

    I believe that base capacity displays the resource's day by day hours as per the resource/project calendar. But still it does not show up the base capacity values from APR 2012 for all resources. Do we hae to make any change from the front end so that the capacity values are applied to the resource profiles?

    Any idea?

    Monday, July 29, 2013 7:52 AM
  • Hello,

    The capacity data is built using a job, the settings can be found on the Additional Server Settings page in PWA (PWA > Server Settings > Additional Server Settings). You will see Resource Capacity Settings. Check the months behind and months ahead values. Also check the queue history to see if the job is running successfully. If it isnt running, change the jobs scheduled time.


    Paul Mather | Twitter | | CPS

    Monday, July 29, 2013 7:58 AM
  • Thanks Paul and Prasanna.

    The resource capacity job which is scheduled at 1 AM in the PWA is not running as I see that nothing appears in the Queue job history (after selecting all job status and all job types). Only Queue clean up job runs at 12 AM.

    Any other way of pushing the resource capacity data to reporting DB?

    Appreciate your help.

    Monday, July 29, 2013 8:35 AM
  • Hello,

    Change the scheduled time to say 02:00 and see if the job runs tonight. Or as a test change it to run 5 minutes from now and see if that jobs is successful in the queue.


    Paul Mather | Twitter | | CPS

    Monday, July 29, 2013 8:37 AM
  • I did that Paul, nothing came up in the Queue. Anyway will be testing in our test environment and see if it works there.

    Will update you

    Monday, July 29, 2013 8:56 AM
  • Yes after rescheduling  the resource capacity job successfully completed. Now I can start building the sql query for my requirement.

    Anyone who has worked on similar requirement, pls do share the sql query.

    Will update my sql query soon.


    Monday, July 29, 2013 7:29 PM
  • Also, you want to use the Combined Work column, if you are using Resource Plans. Otherwise, you have to have this complex decision clause to determine what is controlling the work allocation.

    Treb Gatte | @tgatte |

    Monday, July 29, 2013 7:41 PM
  • Thanks Treb.

    I found out a sql query online which was on the similar lines that I was looking for:

    I am just trying now to modify it further to suit my requirement.

    Tuesday, July 30, 2013 8:40 AM