Duplicate rows returning with Status Report information

[Billman]

We are using CA PPM 14.2 for project management. We utilize many fields in the system for reporting, including all  Status Report fields (Status Report date, Status Report Update, Report Date, Overall Status, etc...) for some project reporting. When producing a basic project report with status report information, the system always returns multiple rows per project as Advanced Reporting is taking all instances of a status report within that project (i.e. Project ID 0001 has four status reports logged, all with different verbiage and different dates in the Report Date column. This report returns all status reports because we are using the Report Date as a field.) Is there any way from with Advanced Reporting to get a report to return only the latest status report for a project?

[Legacy User]

Hi Billman,

 

Are you using AD Hoc Reporting or are you building your report using Jasper Soft Studio.

 

 

If you are using AD HOC Reporting....

 

I did had same issue in the past and I had the issue that table with the information about the report order (the dwh_inv_status_report_latest_v ) was not existing  in our datawarehouse... causing also out of the box report problem.... anyway issue was resolved and OOB report are now working...  but CA did not include it inside the Domain for easy Ad Hoc Reporting...or at least not in our environment. 

 

There might be another workaround that I am not aware, but I think you will need to add the table dwh_inv_status_report_latest_v to the project Management domain or a custom domain you will create and perform a left outer join with dwh_inv_project table on (investment_key)). Then you will need to add the filter "Report_Order = 1" in your Ad Hoc report filter.

 

 

If you are using JasperSoft Studio and building you Query:

 

If you use PPM (live) Database... you will need to use cop_prj_statusrpt_latest_v table and use report_order field.... in Datwarehouse I never used it but it look like there is also a dwh_inv_status_report_latest_v table that you can use to ensure you take the latest report

 

as example your query could look like this...

Select  i.id as investment_id,

            i.code as prj_number,

            i.name as Investment_Name,

           sr.cop_report_update as Report_update,

           sr.cop_key_accomplish as Key_accomplish,

           sr.cop_upcoming_act as upcoming_act,

.... bla bla bla (put all the fields you need)

from inv_investments i

LEFT  OUTER JOIN cop_prj_statusrpt_latest_v lsr ON  i.id = lsr.investment_id AND lsr.report_order = 1

LEFT  OUTER JOIN odf_cop_prj_statusrpt_v2 sr ON lsr.report_id = sr.odf_pk and sr.cop_report_status = 'FINAL'

where i.id = ........

 

Hope this help

 

Eric

[suhail-sayed]

Just to add to the above SQL Query solution, try adding a UNIQUE or DISTINCT function in the SELECT clause. This may help reduce the number of results, which are either unique or distinct.

 

Suhail.