Query to pull ETC Cost for Project

[Lowell Wetzel]

I am looking to get a query to pull the ETC Cost for a project, but I can't find an example query.

Does anyone have a sample query for this?

[JP_Thompson]

Hi,

How are you extracting the data from the backend?

[Kathryn Ellis]

There is an easy way to get this information from the new user experience.

1. Add the 'Assignments' Module to your project Blueprint 
2. In the Module, View Options, select the periods and the Per-Period Metric 'ETC Cost' 
3. Now you see all the costs for the assignments on the project 
4. You can export this grid into CSV format if desired 

[Lowell Wetzel]

Hi JP_Thompson,
I am using Toad to get to an Oracle DB with the NIKU Schema.

The ETC Cost field doesn't seem to be accurate when you match it to the project screen.

[JP_Thompson]

Hi,

Okay, couple more questions:

1. are you using the Rate Matrix to calculate rate * etc hour for cost?
2. are you tying in wbs and resource to your query?
3. What slice status are you using (daily or monthly)?

I have a few examples I could provide, answering the above will help me provide the right example.

Regards

Jonathan.

[Liz Williamson]

Hi Lowell,

I would use the prassignment.etccost_sum field

Query would be something like
SELECT
i.code,
sum(a.ETCCOST_SUM)
FROM SRM_RESOURCES r
INNER JOIN PRASSIGNMENT a
ON a.PRRESOURCEID = r.ID
INNER JOIN PRTASK t
ON a.PRTASKID = t.PRID
INNER JOIN INV_INVESTMENTS i
ON t.PRPROJECTID = i.ID
group by i.code


Make sure that Update Cost Totals has run so all the costs get updated.