Clarity

  • Private Community
 View Only

  • 1.  Is there a way to only return a MAX set of values in Advanced Reporting

    Posted Mar 08, 2018 06:56 PM

    Hello CA PPM Community.  I'm on CA PPM v14.3 currently.  I'm looking to create a report in the standard (Ad Hoc) Advanced Reporting tool that will return the most recent (i.e. MAX) Status Report details, rather than the entire history of Status Reports.  Is this possible (not using Jaspersoft Studio), if so, any reference documentation on how best to accomplish?  Thanks!  Mark

     

    E.G.

    Project Name

    Project ID

    Project Manager

    Max(Status Report - Created Date)

    Max(Status Report - ID)

    Max(Status Report - Status)

    Max(Status Report - Comments)

    Project ETC

    Project Actuals



  • 2.  Re: Is there a way to only return a MAX set of values in Advanced Reporting

    Broadcom Employee
    Posted Mar 09, 2018 12:39 AM

    Yes you need to create a report and the underlying SQL to get only Max record 



  • 3.  Re: Is there a way to only return a MAX set of values in Advanced Reporting

    Posted Mar 09, 2018 09:53 AM
      |   view attached

    Thank you for the quick response Suman.  It's my understanding that Reports are created from Ad Hoc Views.  When I go to Ad Hoc View, I don't see a way to set this up, and per your comment, I don't see a way to EDIT SQL (I only have Read Only).  Can you elaborate, and/or provide a link to existing documentation?  I conducted a few searches on CA Support, and reviewed the Jaspersoft info I found, but did not see anything that provided a solution.



  • 4.  Re: Is there a way to only return a MAX set of values in Advanced Reporting

    Broadcom Employee
    Posted Mar 09, 2018 10:19 AM

    Reports can be adhoc or Jaspersoft studio. With Adhoc this is probably bit complex but Studio report can be preety easy. Have a look at our Jaspersoft docs 

     

    Advanced Reporting with Jaspersoft - CA PPM - 15.3 - CA Technologies Documentation 



  • 5.  Re: Is there a way to only return a MAX set of values in Advanced Reporting

    Posted Mar 09, 2018 11:10 AM

    Thanks again.  I don't have Jaspersoft Studio installed.  I'm on v14.3 currently (we're in the process of exploring the upgrade to v15.x).  A while back I inquired with CA Support about Jaspersoft Studio, and was provided a similar document, and was instructed to contact the Services team for assistance beyond that, which deterred me from exploring it further. 

     

    For the most part, I've been able to get Ad Hoc to work for me, but I hit a wall with the current report I'm working on (looking to only return the most recent Status Report details).  I reviewed the documentation you linked, but it doesn't cover that (or at least, I'm not finding anything), hence my query to the community.  If I could hack the SQL code, I could set that up, but that doesn't appear to be an option in Ad Hoc, so I'm hoping there's a way to do that in Ad Hoc, else I'll be making a portlet...



  • 6.  Re: Is there a way to only return a MAX set of values in Advanced Reporting

    Broadcom Employee
    Posted Mar 09, 2018 11:16 AM

    Adhoc views are written on the domain, so if you want to just just adhoc then its best to create a new domain and use derive table to write the SQL but looking at performance side Jaspersoft studio is more effective.

     

    Can you please let me know if the content in the documentation about Jaspersoft studio doesn;t give much? There are some few free training too