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
Can you explore Groups and Calculated Items available in classic Answers for this requirement?
Other comments welcome.
Thanks
Gayathri
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.
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.
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
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.
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.
Let us not rush to conclusion without testing.
Please let me know if you an alternate way out here.
Thanks
Manu
![[Deleted User]](https://us.v-cdn.net/6037859/uploads/defaultavatar/nT2GKTLRQJ3P1.jpg)