top 10 and rest others in union report — Oracle Analytics

Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

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

top 10 and rest others in union report

Received Response
21
Views
3
Comments
3193829
3193829 Rank 5 - Community Champion
edited Aug 13, 2024 6:13PM in Oracle Analytics Cloud and Server

Hi,

I have a requirement to have Top 10 and rest others in a union Report with their totals(Top 10 total, grand total). I have used the rank on metrics to have the top 10 and rest others (eg case when rank(sales)<=10 then "Customer.Cname" else "Others" end). But i cannot put subtotal on a metric in pivot table. Selection steps is not enabled in the union report.

Is there any way to acheive the top 10 and rest others in a union report ?

Thanks

Kalpana

Answers

  • Adrian Ward
    Adrian Ward Rank 3 - Community Apprentice

    My Advice is to try to create this using Advanced Logical SQL.  The trick in the 2nd query will be to remove those items that get into the top 10 in the first query.  This could also be achieved by using a nested union, where the 2nd row is a union minus.

  • 3193829
    3193829 Rank 5 - Community Champion

    Hi Adrian,

    In my union report, the first report only has the top 10 and rest others in first report and the second one. can you please let me know in detail of achieving this please.

    Thanks,

    Kalpana

  • Adrian Ward
    Adrian Ward Rank 3 - Community Apprentice

    In the advanced SQL you can use standard SQL Syntax for out er joins, e.g. you can use LEFT OUTER JOIN

    (Sorry it took so long to reply!)