Oracle Transactional Business Intelligence

Home› Oracle Analytics› Oracle Analytics Forums› More Solutions› Oracle Transactional Business Intelligence

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Selecting the latest Order revision

373
Views
10
Comments
Mr H
Mr H Rank 3 - Community Apprentice
in Oracle Transactional Business Intelligence

Summary

Need to retrieve the latest Orders revisions

Content

Hi,

  While creating an Analysis to retrieve the Sales Orders details we need to show only the latest revision of the Order and ignore the old ones.

Is there a function to be used or filter logic to achieve that?

Below a sample of the analysis created and we need to show only the records for Revision 4.0

 

The Subject Areas used are:
Order Management - Order Headers Real Time
Order Management - Order Lines Real Time

BR
Hany

 

Version

19d

Annotation 2020-02-04 231613.png

Tagged:

Comments

  • ravikiran goda-Oracle
    ravikiran goda-Oracle Rank 5 - Community Champion

    Hi Hany,

    As per the screenshot how to identify which is the latest record ,because the Order line column is also populated with 2 values 1.0,2.0

     

    Regards,

    Ravi

  • Mr H
    Mr H Rank 3 - Community Apprentice

    Hi Ravi, We need to set the Revision column = max(Revision) for that Order.
    So for each Order we need to display only the records linked to the most recent Revision. For the example above it should be revision 4 or other Orders it could be 1 or 3 depending on the number of revisions.

    Can we do that? How?

    BR
    Hany

  • Mr H
    Mr H Rank 3 - Community Apprentice

    Hi Ravi. Did you have the chance to check?

  • Hamish Vickers
    Hamish Vickers Rank 2 - Community Beginner

    Hi,

    Have you tried using one of the aggregate functions such as "MAX" ?
    As you only want the data for the most recent, aka, the highest or MAX revision number?

    Hamish

  • IIGC
    IIGC Rank 2 - Community Beginner

    Where you able to find a solution for this?

  • Mr H
    Mr H Rank 3 - Community Apprentice

    Hi IIGC, unfortunately not yet.

  • Sahithi Kolasani-Oracle
    Sahithi Kolasani-Oracle Rank 5 - Community Champion

    Hi Mr H

    Please create a hidden column called Rank and apply logic given below. Please change the column names as per your analysis.

    Rank column logic

    rank("- Inventory"."Item Revision Control" desc by "Orchestration Order Lines Details"."Order Number" )

    After that, please apply a filter on the Rank = 1

    With this solution, you should be able to get the latest release of a given order.


    Attaching the xml here with. Please create a new analysis-> select the subject area and go to advanced tab.


    Select all the content of the xml and paste the content given in the attachment. Click on apply.

    Please check if this helps.

    Thank you!

  • Andrew_Schmitz
    Andrew_Schmitz Rank 5 - Community Champion

    I think we have a shared filter we use on most of our reports on Orders data.

  • User_259TP
    User_259TP Rank 1 - Community Starter

    Creating the rank column and then a filter to select rank = 1 worked perfectly for my application! I was trying to select the latest revision for each part number in a report. Thank you so much!

  • User_259TP
    User_259TP Rank 1 - Community Starter

    Now if I could just figure out how to show my display name in the comments. It defaults to my user name.