Clarity

Expand all | Collapse all

Query for Project Estimated Labor (in hours & dollars)

  • 1.  Query for Project Estimated Labor (in hours & dollars)

    Posted 05-10-2016 10:09 AM

    I'm looking for a query that sums the estimated labor for all resources on a project (equivalent of the OOTB 'ETC Sum for Labor Resources' field in Clarity). It would also be great if someone could please point me in the right direction to convert this to a dollar amount (by multiplying into the rate table for the corresponding resources). I'm not sure what tables I need to look at (briefly skimmed the tech ref too, did not find details of where to get this info).



  • 2.  Re: Query for Project Estimated Labor (in hours & dollars)

    Posted 05-10-2016 12:17 PM

    Hi Dev,

     

    The easy way to find out Resource ETC is , go to a project in CA PPM and team tab and you will see ETC based on resources, do a trace and you will be able to see the query. If you need at assignment level then go to the task tab and then to assignments, here you can get assignment level ETC for particular project.

     

    Once get the query for one project then you can change the where clause to accommodate all the projects/task/team etc.

     

    Hope this helps

     

    Regards

    Suman Pramanik



  • 3.  Re: Query for Project Estimated Labor (in hours & dollars)

    Posted 05-10-2016 01:54 PM

    Is there a way to trace a hosted Clarity platform? I do not have access to the host, only the database itself and the application.



  • 4.  Re: Query for Project Estimated Labor (in hours & dollars)

    Posted 05-10-2016 02:19 PM

    The trace can be done by UI via security.logs page unless trace is explicitly disabled in properties.xml. This video can help you CA Clarity - SQL Trace Activation - YouTube

     

    We do have a technical document also Using Action Trace

     

    Regards

    Suman Pramanik



  • 5.  Re: Query for Project Estimated Labor (in hours & dollars)

    Posted 05-10-2016 04:09 PM

    There is a query in

    Resource weekly ETC query

    from which you can work furhter.



  • 6.  Re: Query for Project Estimated Labor (in hours & dollars)

    Posted 05-12-2016 10:38 AM

    Explore the nbi_proj_res_rates_and_costs or ppa_matrixvalues tables for the rates per user which may come in handy for finding the dollar amount. The nbi_project_current_facts table has the ETC Labor in hours per project.