AnsweredAssumed Answered

SQL in Adhoc Query

Question asked by Will_Lloyd on Jan 25, 2018
Latest reply on Jan 30, 2018 by Suman Pramanik

The OOTB 'Project Management' domain is a good place to start to extract several fields from the PM schema, but for each Project, there may be zero, one or many Status Reports.

I was wondering if there is a way within the Advanced Reporting Adhoc Query tool to show only the latest, Final Status Report for each Project. I experimented a bit with creating a 'Custom Filter Expression' and a 'Calculated Field' but these don't seem to be the right tool for the job.

I had to resort to exporting the entire result set out to Excel, then writing SQL to perform an INNER JOIN on the Project ID and MAX(Report Date) to link back to the original table to get only a single Status Report's contents in a similar way that the Project Storyboard report displays it. I picked through the construction of the OOTB Project Storyboard report in the Jaspersoft Report Studio, and unpacking the logic that went into the construction of that report seemed like an equally challenging task.

Any thoughts or suggestions?