Ad-hoc report: Limit result-set by returning only the rows within a group with the latest update date

[Anon Anon]

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:

CURRENT RESULTS

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)

Location              Status    Last Updated Date

Tuscon                 Closed   4/12/2016

Xavier                   Pend      12/1/2015

 

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).

[Nika Hadzhikidi]

Hi Antonio,

 

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

[Anon Anon]

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:

Location              Status    Last Updated Date

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.

Location              Status    Last Updated Date

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

[Nika Hadzhikidi]

Hi Antonio,

 

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