Hi, I'm trying to create a project report listing that reports only on the latest Status Report Update. Could you tell me if this is possible or not? The report I created duplicates rows depending on how many status reports have been submitted. Thanks
We use a subquery to limit to the latest status report. The subquery looks at the report date to determine which report is the latest. One issue we've found is if someone creates two status reports in the same day for the same project and uses the same report date on both you will get a query error. This can easily be fixed by just changing the report date on one of the records to make it different. You could decide to use the create date instead but it's possible that the create date may not indicate which report is INTENDED to be the latest...just which one was actually created last. At any rate, here is an example:
SELECT INV.ID, SR.COP_REPORT_DATE
FROM INV_INVESTMENTS INV INNER JOIN
ODF_CA_COP_PRJ_STATUSRPT SR ON INV.ID = SR.ODF_PARENT_ID INNER JOIN
(SELECT ODF_PARENT_ID, MAX(cop_REPORT_DATE) AS MAXOFPRFINISH
GROUP BY ODF_PARENT_ID) SRMAX ON SR.COP_REPORT_DATE = SRMAX.MAXOFPRFINISH AND SR.ODF_PARENT_ID = SRMAX.ODF_PARENT_ID
^ @amy ; if your MAX was on something that wasn't able to be duplicated (like the ID column) then you would not get your "2 reports on the same date" problem - would need to change your join condition as well of course.
Yes! I originally tried report ID. However I don't find that the report ID number increments as expected...i.e. I could have a report ID number that is LOWER on a more recent report than on an older one. Create date is a possibility, as I mentioned, but the potential problem there is that someone could create their "September" status report FIRST then realize they need to go back and create the "August" report they missed. In that case using create date would return the August report instead of September's. Thankfully we don't have the problem too often and it's easy enough to locate and resolve.
Thanks for this. Sorry am a bit new so don't know where to put this query. I have only done light training on creating ad hoc views and reports using the GUI offered in the "Advanced Reporting" Section. If you are able to provide me with more instructions, that would be appreciated
Natalie - you'll need to be able to run queries against your database, test them, then convert those queries to NSQL format for use behind Clarity portlets.
If you do not have a tool to use for testing SQL queries you could look into the online query tool provided free by IT ROI.