Oracle Analytics Cloud and Server

Products Banner

TOP N + Rest Dimensional Values in OAC

Received Response
225
Views
18
Comments

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

Answers

  • 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
    SteveF-Oracle ✭✭✭✭✭
    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.

  • Manu R-Oracle
    Manu R-Oracle ✭✭✭
    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.

  • 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

  • SteveF-Oracle
    SteveF-Oracle ✭✭✭✭✭

    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.

  • Manu R-Oracle
    Manu R-Oracle ✭✭✭
    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

  • 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)

  • 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.

    Thanks

    Manu

  • 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
    edited October 2023

    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 :)

  • 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

  • Hi @Manu R-Oracle ,

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

  • @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

  • 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