Clarity

Hi, I'm new to reporting and was wondering if it was possible to create a table of data and report only on the latest status report update.

For Example:

Investment ID

Investment Name

Status Report Date

Status Report Update

where status report date and update are the latest only

Thanks

Definitely possible. Quite easily accomplished I think using DWH_INV_INVESTMENT and DWH_INV_STATUS_REPORT tables. You can go through the columns in those tables, and try and write a simple query.

A sample that I just came up with:

select dii.investment_name, dii.investment_key
, sr.report_date, sr.status_report_name
from dwh_inv_status_report sr
join (
  select max(report_date) col0, investment_key
  from dwh_inv_status_report
  group by investment_key
) sr1 on sr1.col0 = sr.report_date and sr1.investment_key = sr.investment_key
join dwh_inv_investment dii on dii.investment_key = sr.investment_key
order by dii.investment_name

I have not tested it, so, quite possible that it might not work properly. But just to show that it is possible.

You will have access to the OOTB JasperSoft reports, which include Project Status Detail as an example.  SQL from that report:

FROM dwh_inv_investment i
INNER JOIN dwh_inv_project p ON i.investment_key = p.investment_key

LEFT OUTER JOIN dwh_inv_status_report_latest_v srl1 ON p.investment_key = srl1.investment_key
AND srl1.report_order = 1