1 Reply Latest reply on Jun 29, 2016 3:28 PM by Ebin

    To fetch latest record in BI report

    Saumya Gupta - Infosys

      Hi All,

       

      I have a requirement to display latest meeting status of an employee. But in the report I am getting 2 records for an employee, the meeting date is same but the Last Update Date is different.

      I found some info on MOSC where they said we can use the filter as LAST_UPDATE_DATE = MAX(LAST_UPDATE_DATE BY EMP_ID), but it didn't work.

      I also tried with Meeting_Id instead of Last_Update_Date and also tried RANK() & TOPN().

      In the report, the fields are coming from 3 Subject Areas, so when I use RANK(), TOPN() or the filter above, we are getting ODBC errors and when used in a simple report, the output doesn't change.

      Any pointers on this will help a lot.


      For ex:

      Emp     Meeting Id     Meeting Date     Last Update Date     DATE_FROM     DATE_TO     Performance

      ABC     100002          15-06-2016          15-06-2016               15-06-2016        31-12-4712     Above Expectations

      ABC     100003          15-06-2016          24-06-2016               15-06-2016        31-12-4712     Met Expectations


      Expected Output:

      Emp     Meeting Id     Meeting Date     Last Update Date     DATE_FROM     DATE_TO     Performance

      ABC     100003          15-06-2016          24-06-2016               15-06-2016        31-12-4712     Met Expectations


      Thanks,

      Divya

        • 1. Re: To fetch latest record in BI report
          Ebin

          Hi Divya,

           

          Are you trying to display the last updated record? Last updated record may not be always equal to the last created record if your design allows updates to existing records.

           

          If data datatype of your date column is timestamp, then you display the latest record using the RANK function.

           

          RANK(LAST_UPDATE_DATE BY EMP_ID)

          Apply filter on top of Rank to find the last updated record.

          1 person found this helpful