TOP N + Rest Dimensional Values in OAC

Federico Venturin - Oracle

Hi @Manu R-Oracle ,

Can you please share all column expressions used in the analysis?

As an alternative, you can combine results based on Union All set operation (e.g. one criteria to get the top 10 products with related metrics, and another criteria to get all other products not in the top 10)

Manu R-Oracle

Hi @Federico Venturin ,

There are only 4 columns in it. Attached are the column Expressions.

Union didn't help in this case as there is an which shows % for each of the records to the total.

Any further help appreciated.

Columns.txt

Uploaded Oct 31, 2023735 B

Thanks

Manu

Federico Venturin - Oracle

Hi @Manu R-Oracle ,

Please use the following columns in your analysis:

Supplier Name: "Supplier"."Supplier Name"

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

Payment Amount: "Financials - AP Transactions"."Facts"."AP Cleared Invoice Amount (Document Currency)"

Do not concatenate the RANK of each supplier with its name, otherwise the analysis will display the wrong number of rows. If you need to put the Others row at the end, then sort your rows based on Payment Amount first, then based on the following column:

Rank Supplier: CASE WHEN (CASE WHEN ATTRIBUTE(RANK("Financials - AP Transactions"."Facts"."AP Cleared Invoice Amount (Document Currency)") BY "Supplier"."Supplier Name") <= 10 THEN "Supplier"."Supplier Name" ELSE 'Others' END) = 'Others' THEN 2 ELSE 1 END

Sathyaprakash M

Hi @Manu R-Oracle

Please use the below column formulas:

Solution 1:

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

Payment Amount: "Financials - AP Transactions"."Facts"."AP Cleared Invoice Amount (Document Currency)"

Rank Supplier: RANK("Financials - AP Transactions"."Facts"."AP Cleared Invoice Amount (Document Currency)"

If you use the above columns, you will get 11 rows as Others(Rank 1) will be in the first row, and the rest of the values will come after that.

Solution 2:

Create an union report:

In this case, you need 2 reports.

1st report: Create a dummy report with the top 10 suppliers.

On the 2nd report(1st Union), add the required columns and filter the rank column <=10 and in the 2nd union, you can add the required column and edit the supplier name formula to 'Others' and rank column to 11 and filter the Supplier Name not in (top 10 suppliers from the 1st report).

Hope the above solution works :)

Manu R-Oracle

Hi @Federico Venturin , @SteveF-Oracle ,

Can you please use the demo instance below apply the fix on a sample report created.

https://oacfieldwest-idnym1tkby0n-ia.analytics.ocp.oraclecloud.com/ui/analytics/saw.dll?PortalGo&Action=prompt&path=%2Fshared%2F2.Standard%20OA%20Samples%2FSR%20TOPN

It will be easier if you actually check the issue and see if the fix works with an active instance.

Please save it with a different version.

Regards,

Manu

Federico Venturin - Oracle

Hi @Manu R-Oracle ,

I'll be happy to help, but I can't access that instance with my account

GayathriAnand-Oracle

@Manu R-Oracle Please note that this is a public forum, so please do not share CI/PI info.

Since you are oracle employee, you can always use our internal slack channels for further assistance OR you can create SR with Support so we can assist you further.

Thanks

Gayathri

Manu R-Oracle

Hi @Federico Venturin ,

I have informed the Oracle Support executive about ur access. You might be contacted by them soon.

@GayathriAnand-Oracle , Thanks for suggestion but those all options were tried out earlier. Didnt help much, hence reaching on community group for wider reach. Besides some of the inputs on this group are from Product team as well, hence more chances of a fix/workaround.

This is a fairly straightforward reporting request from the customer, OAC should have a way-out to handle this.

Let us see if we and figure that out collectively.

Thanks

Manu