Oracle Analytics Cloud and Server

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

TOP N + Rest Dimensional Values in OAC

Received Response
273
Views
18
Comments
Rank 4 - Community Specialist

Hi All,

We have a specific report requirement where user want to see TOP 5 Payable Vendors with 6th element being the cumulative values of all other than the TOP 10 vendors. This is feasible is Oracle DV, Please refer to the below table, but we are looking for similar report in OAC Classic.


Thanks

Manu

Welcome!

It looks like you're new here. Sign in or register to get started.
«1

Answers

  • Can you explore Groups and Calculated Items available in classic Answers for this requirement?

    Video on Creating Groups

    Other comments welcome.

    Thanks

    Gayathri

  • edited October 2023

    Hi @Manu R-Oracle ,

    There could be several options depending on which part of the product you are utilizing.

    CASE statements are very powerful.

    As @GayathriAnand-Oracle mentioned, in Classic, you can use groups, or you can use something like this example (older, but can apply)

    http://obiee101.blogspot.com/2009/08/obiee-topn-versus-rest.html?m=1

    For the self-service side in /dv, you can see the following example

    Displaying an 'Other' category with Top / Bottom N filters

    Hope that helps, other community comments, welcomed.

  • Hi @Manu R-Oracle ,

    To achieve a similar report in Classic you can use the following calculation:

    CASE WHEN ATTRIBUTE(RANK(<Measure>) BY <Attribute>) <= 10 THEN <Attribute> ELSE 'zz-Others' END
    

    You have to replace <Measure> and <Attribute> with your Payment Amount and Supplier Name columns. The calculation will display the proper label for the top 10 supplier names, and a generic zz-Others label for all others.

  • Rank 4 - Community Specialist
    edited November 2023

    Thanks @GayathriAnand-Oracle ,@SteveF-Oracle , @Federico Venturin for response.

    Tried the Group , Items options. It does not work in this case.

    CASE statement does not help in the right aggregation in this case. It didnt work.

    Attribute() function helps in DV as shown in the 1st post, but does not working same way in Classic,hence the issue.

    @SteveF-Oracle the blog does not help completely. The output gets changed as when the column is excluded in the Pivot table. Pls rerefer the below output.

    Any inputs on this are greatly appreciated.

    Thanks

  • Hi @Manu R-Oracle ,

    There is something wrong in your Supplier Name calculated column. Could you please share the expression that you are currently using?

    As I can see from the screenshot, it has been treated as a Measure (yellow icon) , hence it will always be aggregated and you will loose the detail that you need.

  • Rank 4 - Community Specialist

    Hi @Federico Venturin ,

    That happens because of the use of RANK function.

    Please find below the Exp:

    CASE WHEN Rank( "Financials - AP Transactions"."Facts"."AP Cleared Invoice Amount (Document Currency)" ) <= 10 THEN CAST(Rank( "Financials - AP Transactions"."Facts"."AP Cleared Invoice Amount (Document Currency)" ) AS CHAR)||'-'||"Financials - AP Transactions"."Supplier"."Supplier Name" ELSE '11-Others' END


    Thanks

    Manu

  • Hi @Manu R-Oracle ,

    You have to use the ATTRIBUTE function (as in the example I shared above) to transform the output of the RANK function into an attribute at your desired level of granularity.

    This one should work as expected:

    CASE WHEN ATTRIBUTE(RANK("Financials - AP Transactions"."Facts"."AP Cleared Invoice Amount (Document Currency)") BY "Financials - AP Transactions"."Supplier"."Supplier Name") <= 10 THEN CAST(ATTRIBUTE(RANK("Financials - AP Transactions"."Facts"."AP Cleared Invoice Amount (Document Currency)") BY "Financials - AP Transactions"."Supplier"."Supplier Name") AS CHAR) || '-' || "Financials - AP Transactions"."Supplier"."Supplier Name" ELSE '11-Others' END

  • Hi @Manu R-Oracle

    Please mark @Federico Venturin responses as answered and to close topic.

    Thanks!

  • Word of caution of course being: performance. This whole logic will get executed for every single row coming back in the data stream. So running it unfiltered against huge fact sources can be interesting when it comes to response times.

  • Rank 4 - Community Specialist
    edited November 2023

    Hi @Federico Venturin ,

    The ATTRIBUTE function didnt help.

    Please see below the output.

    Besides I cant remove the 'Supplier Name' column as well.

    Could you please assist.

    @SteveF-Oracle ,

    Let us not rush to conclusion without testing.

    Please let me know if you an alternate way out here.

    Thanks

    Manu

Welcome!

It looks like you're new here. Sign in or register to get started.