Ad-hoc report: Limit result-set by returning only the rows within a group with the latest update date. I have access to ad-hoc reporting and am very familiar with SQL/Sub queries etc, however I cannot within PPM figure out how to achieve the following through ad-hoc reporting:
Location Status Last Updated Date
Tuscon Active 3/9/2016
Tuscon Closed 4/12/2016
Tuscon Est 2/20/2016
Xavier Active 9/5/2015
Xavier Pend 12/1/2015
NEEDED RESULTS (only return the latest updated date result)
Does this require a combination of calculated fields and filters? I know how to build a sub-query outside of PPM but need to accomplish this within PPM ad-hoc reporting (I am not an admin for the system, so my access is limited to this).
Please try using the Filter feature on the right, you can add a filter on "is after" and include a relative date or a specific date after which you need to see the results. See an example below:
For you it could help saying Months -5 or specify everything after 12/1/2015
Hope this helps -Nika
Hi Nika, unfortunately the approach you propose still produces multiple row results. If I say everything after 12/1/2015 or MONTHS - 5, the results would be:
Tuscon Active 3/9/2016 <<not needed
Tuscon Closed 4/12/2016 << need only the latest
Tuscon Est 2/20/2016 <<not needed
What is needed is only the results where Last Updated Date is MAX date where Location is the same and Last Update Date is unique. Selecting an arbitrary time period or window will not work as the results go back for several years.
Tuscon Closed 4/12/2016 << latest date of 4 results
Xavier Pend 12/1/2015 << latest date of 2 results
Yellowstone New 2/24/2016 <<latest date of 11 results
Zambie Closed 10/22/2014 <<latest date of 23 results
This was just an example on how you can use the Filter feature to restrict the results you are getting. If you don't see a way to get the exact results you want with the Filter, then I'd recommend you to use a Studio report based on a SQL query. This will allow you to select the results you want with DISTINCT and MAX functions.
Kind Regards -Nika