none
Resource Hours Report RRS feed

  • Question

  • Hi

    Can you write a SQL query, which just returns a column with resource names, and a column with total costs for that resource (e.g. total cost on system)

    I try but get resource name mentioned several times, for each task have hours on.

    Thursday, March 21, 2013 10:18 PM

Answers

All replies

  • Hi there,

    Try this as a starter:

    select		R.ResourceName
    		,	SUM(R.ResourceStandardRate) + SUM(R.ResourceOvertimeRate) as [Total Cost]
    from		MSP_EpmResource_UserView R
    INNER JOIN	MSP_EpmAssignment_UserView A
    ON			A.ResourceUID = R.ResourceUID
    Group by	R.ResourceName

    Run against the PWA Reporting database.

    Thanks

    Paul


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

    Thursday, March 21, 2013 11:00 PM
    Moderator
  • Hi sorry I meant how much the resource has actually cost - so almost like a Resource Actual Cost field. So for example; COLUMN 1 (RES NAME) COLUMN 2 (ACTUAL COST) e.g. from that, Tim Smith £50,550 | Jenny Collins £23,800

    Column three could be actual work to be honest. I thought if I could get one or the other I can work it out. Hope that helps, any other idea?

    • Edited by Jumpy99 Friday, March 22, 2013 12:32 AM
    Friday, March 22, 2013 12:31 AM
  • What about:

    select		R.ResourceName
    		,	SUM(A.AssignmentActualCost) + SUM(A.AssignmentActualOvertimeCost) as [Total Cost]
    from		MSP_EpmResource_UserView R
    INNER JOIN	MSP_EpmAssignment_UserView A
    ON			A.ResourceUID = R.ResourceUID
    Group by	R.ResourceName
    

    Paul

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

    Friday, March 22, 2013 8:35 AM
    Moderator