Clarity

 View Only

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

    Posted Apr 15, 2016 02:30 PM

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



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

    Broadcom Employee
    Posted Apr 15, 2016 04:40 PM

    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



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

    Posted Apr 17, 2016 01:11 AM

    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



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

    Broadcom Employee
    Posted Apr 18, 2016 10:01 AM

    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