Project Earned Value Report displays wrong data (duplicate rows)

Document ID : KB000101876
Last Modified Date : 15/06/2018
Show Technical Document Details
Issue:
When you run the Project Earned Value Report, for some projects you see multiple (duplicated rows) or duplicated values in the results.
 
Cause:
This is caused by duplicate rows in the database for this project. To prove this, run the query:
select * from prj_ev_history
where object_type = 'PROJECT'
and project_id = 5021196 ------- replace this ID with the project correct internal ID
order by period_start_date
 
You will see some periods have duplicate rows.
 
Resolution:
DE39375 Project Earned Value Report Query
- fixed in the Jaspersoft report provided with 15.4.1
This fix will ensure the report will only return one row even if duplicates exist
 
DE39970 Update Earned Value and Cost Totals job should be incompatible with itself, leading to duplicates in PRJ_EV_HISTORY
- fixed in 15.5
This fix will ensure the duplicates DO NOT happen in PRJ_EV_HISTORY as we found they sometimes happen due to the jobs running at the same time
 
Workaround:
1. Backup the table PRJ_EV_HISTORY
2. Find all duplicates with query:

SELECT OBJECT_ID, OBJECT_TYPE, PERIOD_NUMBER, count(1) from PRJ_EV_HISTORY group by OBJECT_ID, OBJECT_TYPE, PERIOD_NUMBER having count(1) > 1

3. Delete the duplicates from PRJ_EV_HISTORY (contact Support if need assistance)
4. Run the Update Earned Value and Cost Totals job for this project
5. Go to CA PPM UI - Administration - Reports and Jobs 
6. Browse for Update Earned Value and Cost Totals
7. Set the job to be Incompatible with itself