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

OBIEE 11.1.1- Direct Sql Total column

Received Response
1
Views
6
Comments
Morel Cornnery
Morel Cornnery Rank 3 - Community Apprentice
edited Aug 13, 2024 6:09PM in Oracle Analytics Cloud and Server

Hi All ,

I want to know the way to add total column from an analysis which has been create from Direct sql . Indeed in my report i have (Distinct user, number of transaction,total revenue) .

I want to display at the bottom line only total of the column (number of transaction,total revenue) and don't display the column total of distinct .

Thanks to adivse how to do it ?

Best Regards,

Morel

Answers

  • cesar.advincula.o
    cesar.advincula.o Rank 6 - Analytics Lead

    Use analytics functions in oracle database make the query.

  • asim cholas
    asim cholas Rank 6 - Analytics Lead

    Use combine similar report and add the same criteria there and use the second criteria for grand total.

    For example try like below

    first criteria

    country  userscount amount2 amount3

    second criteria

    "Grand Total"  0  amount2  amount3

  • Morel Cornnery
    Morel Cornnery Rank 3 - Community Apprentice

    Thank you !

    But I've just found how to do it without analytics, you have to change aggregation rules from criteria mode, to put none for the column for which you don't need to have total   .

    Regards ,

    Morel

  • Morel Cornnery
    Morel Cornnery Rank 3 - Community Apprentice

    Hi Asim ,

    am interested by your idea which consist to combine similar report , indeed i want to do the total at row level as am able to do it at column level now from OBIEE . But I looked for OBIEE interface , but am not able use combine report so my question are .

    1. This is an OBIEE feature (am novice with OBIEE) ?
    2. Can we use this feature when we are using sql direct report ?
    3. If 2. is yes can you elaborate moreover about how can i combine similar report ?

    Thank you for your support .

    asim cholas a écrit:Use combine similar report and add the same criteria there and use the second criteria for grand total.For example try like belowfirst criteriacountry userscount amount2 amount3second criteria"Grand Total" 0 amount2 amount3
  • asim cholas
    asim cholas Rank 6 - Analytics Lead

    if you remove aggregation rule, it will not only remove grand total it will remove the aggregation of result set. combine similar request is an obiee feature. It is nothing but UNION,UNION ALL, INTERSECT etc. If you are  using DDR u can modify the query with union all

    1.jpg

  • Morel Cornnery
    Morel Cornnery Rank 3 - Community Apprentice

    Thank you Asim !