View Only
  • 1.  SQL in Adhoc Query

    Posted Jan 25, 2018 01:32 AM

    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?

  • 2.  Re: SQL in Adhoc Query

    Posted Jan 25, 2018 01:57 AM


    You can think of writing the custom domain, write your logic in a custom domain, build an ad-hoc view based on the custom domain.




  • 3.  Re: SQL in Adhoc Query

    Posted Jan 25, 2018 05:25 PM

    Hi Gaurav,

    Thanks, yes, that occurred to me.

    I don't believe that it's possible to build a Domain on an existing Domain, and I'm not certain if it is possible to replicate an entire Domain, so I believe that this would involve building it from scratch unfortunately.

    I have it as an outstanding task to get the OData connection configured, so other toolsets unfortunately aren't available until that happens.



  • 4.  Re: SQL in Adhoc Query
    Best Answer

    Broadcom Employee
    Posted Jan 29, 2018 02:28 AM

    you can actually make a copy of the existing domain and build on top of it. 

  • 5.  Re: SQL in Adhoc Query

    Posted Jan 29, 2018 02:11 PM

    Thanks Suman. I was not aware of having that capability. Some of the features are context- and mouse click-sensitive, so I hadn't come across that as yet. I will investgate further.

  • 6.  Re: SQL in Adhoc Query

    Broadcom Employee
    Posted Jan 29, 2018 11:35 PM

    Have a look at section Migrate domains, you can export the domain and you will have the schema.xml and you can modify the XML and then import as new domain 


    Ad Hoc Views, Reports, and Custom Report Development - CA PPM - 15.2 - CA Technologies Documentation