Oracle Analytics and AI

TOP N + Rest Dimensional Values in OAC

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

Find more posts tagged with

Accepted answers

All comments

GayathriAnand-Oracle

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

Video on Creating Groups

Other comments welcome.

Thanks

Gayathri

SteveF-Oracle

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

https://blogs.oracle.com/analytics/post/explore-data-with-filters-part-3

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

Hope that helps, other community comments, welcomed.

Federico Venturin - Oracle

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.

Manu R-Oracle

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

Federico Venturin - Oracle

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.

Manu R-Oracle

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

Federico Venturin - Oracle

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

SteveF-Oracle

Hi @Manu R-Oracle

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

Thanks!

[Deleted User]

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.

Manu R-Oracle

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