Clarity

  • 1.  Report on Latest Status Report Update from Status Reports

    Posted Oct 28, 2015 01:10 AM

    Hi, I'm trying to create a project report listing that reports only on the latest Status Report Update. Could you tell me if this is possible or not? The report I created duplicates rows depending on how many status reports have been submitted. Thanks



  • 2.  Re: Report on Latest Status Report Update from Status Reports

    Posted Oct 28, 2015 09:42 AM

    We use a subquery to limit to the latest status report. The subquery looks at the report date to determine which report is the latest. One issue we've found is if someone creates two status reports in the same day for the same project and uses the same report date on both you will get a query error. This can easily be fixed by just changing the report date on one of the records to make it different. You could decide to use the create date instead but it's possible that the create date may not indicate which report is INTENDED to be the latest...just which one was actually created last. At any rate, here is an example:

     

    SELECT INV.ID, SR.COP_REPORT_DATE

    FROM INV_INVESTMENTS INV INNER JOIN

    ODF_CA_COP_PRJ_STATUSRPT SR ON INV.ID = SR.ODF_PARENT_ID INNER JOIN

    (SELECT ODF_PARENT_ID, MAX(cop_REPORT_DATE) AS MAXOFPRFINISH

    FROM ODF_CA_COP_PRJ_STATUSRPT

    GROUP BY ODF_PARENT_ID) SRMAX ON SR.COP_REPORT_DATE = SRMAX.MAXOFPRFINISH AND SR.ODF_PARENT_ID = SRMAX.ODF_PARENT_ID



  • 3.  Re: Report on Latest Status Report Update from Status Reports

    Posted Oct 28, 2015 09:54 AM

    ^ @amy ; if your MAX was on something that wasn't able to be duplicated (like the ID column) then you would not get your "2 reports on the same date" problem - would need to change your join condition as well of course.



  • 4.  Re: Report on Latest Status Report Update from Status Reports

    Posted Oct 28, 2015 10:20 AM

    Yes! I originally tried report ID. However I don't find that the report ID number increments as expected...i.e. I could have a report ID number that is LOWER on a more recent report than on an older one. Create date is a possibility, as I mentioned, but the potential problem there is that someone could create their "September" status report FIRST then realize they need to go back and create the "August" report they missed. In that case using create date would return the August report instead of September's. Thankfully we don't have the problem too often and it's easy enough to locate and resolve.



  • 5.  Re: Report on Latest Status Report Update from Status Reports

    Posted Oct 28, 2015 09:39 PM

    Hi


    Thanks for this. Sorry am a bit new so don't know where to put this query. I have only done light training on creating ad hoc views and reports using the GUI offered in the "Advanced Reporting" Section. If you are able to provide me with more instructions, that would be appreciated

     

     

    Thank you!



  • 6.  Re: Report on Latest Status Report Update from Status Reports
    Best Answer

    Posted Nov 10, 2015 10:44 AM

    Natalie - you'll need to be able to run queries against your database, test them, then convert those queries to NSQL format for use behind Clarity portlets.

    https://wiki.ca.com/ca-ppm-saas/14-3/reference/ca-ppm-studio-development/ca-ppm-studio-nsql-queries

     

    If you do not have a tool to use for testing SQL queries you could look into the online query tool provided free by IT ROI.

    https://xogbridge.itroisolutions.com/xogbridge