Skip to Content
avatar image
Former Member

TOP N & ALL OTHER in BOBJ 4.0 Dashboard Design

Hi All,

We are SAP BW using BEx Queries as the datasource via BOE in Dashboard design.

Does any one have any idea on doing Top 10 or N on customers and show All Other and plot in the column graph.

Requirement:

Show table on Top 10 or N based on "Total" Volume. Metrics the table need to shows is Volume and Growth Rate.

                                                          jan feb mar apr may jun jul aug sep oct nov dec   q1   q2    q3   q4    total

Customer N         Act Fcst Vol          100 200 100 100 50 50 50   30 30    40  50     50   400 200 110 140   850

                              GR%

                              Next Year GR%

Customer N-1        Vol

                               CY GR%

                    Next Year GR%

Customer N-2         Vol

                                GR%

                     Next Year GR%

Customer N-3         Vol

                                  GR%

                      Next Year GR%

:

:

TOP 10 Total : Vol

                         GR% (% shouldnt add up)

        Next Year GR% (% shouldnt add up)

Graph 1 : N customer & All Other in X axis and Volume on Y Axis

Click on each customer and show graph on current growth rate and next year growth rate by month in x axis and % in y axis.

Please let me know if it can be done in BOBJ 4.0 Dashboard design.

Thanks

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • avatar image
    Former Member
    Aug 22, 2013 at 04:17 AM

    HI Sini,

    There is limitation on TopN condition from BEx side.

    To have TopN condition in place, you need to apply the logic on reporting side.

    You may create a WEBI report and apply TopN condition on it and then use that report in Dashboards Design using BIWS or LiveOffice data connection.

    Regards,

    Tejas

    Add comment
    10|10000 characters needed characters exceeded

  • Aug 22, 2013 at 10:29 AM

    Make a table with your Top 10 requirements.

    Now click sum of key figure, which adds one new row with sum.

    Clear Contents from this row.

    Now in first column add 11 and in another column add Other All.

    Now in another column =NoFilter(Key Figure which you want to display)  - Sum(Key Figure which you want to display)

    Thanks.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Aug 22, 2013 at 01:32 PM

    Hii Sini ,

    Top N Condition applied in Bex Query is not supported in Dashboard Design hence to overcome this problem we need to apply a trick as mentioned below .

    Step 1 : Ask the BW Consultant to Remove TOP N Condition based on a particular KF from Bex Query .

    Step 2 : Bex Report should be sorted based on KF in Descending Order .

    Step 3 : Bind just 10 Cell in Excel Range in Dashboard Design .

    Hope using above logic you will get Top N Report in Dashboard or there is a Pre Query Option in BW

    for BW Consultant has to look upon.

    Regards

    Jeetan Jagtap

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi Agata,

                 

      Go to the Display option in Bex Query Designer and set the following properties:

      Sort Characteristic"--> Select the characteristic and

      "Sort by" --> Select "Text" and

      "Sort Direction" --> Select  "Ascending/Descending" as per report requirement .

      Regards

      Jeetan

  • avatar image
    Former Member
    Aug 27, 2013 at 06:49 AM

    Hi Sini,

    In order to achieve the below requirement

    I need Top N list

    - Top N Sum (Note there is % too which is not a sum and it needs to do row by calculation)

    - All Other Sum & %

    - Grand Total.

    you need to create a variable in webi report using the formula

    Rank(measure;[ranking_dims][;Top|Bottom][;(reset_dims)])

    in your case take a variable rank=rank([vol];[customer])

    for getting remaining others after top 5 or 10 you need to create a variable in webi

    as customer = if(rank>5)then "others" else [customer]

    now you add this variable in the table and hide the existing [customer]dim

    so that you will get the required Top 5 customers and Others -Total and Grand Total in your report....

    Let me know the result once after trying the above stpes

    Regards

    Subbu

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi Sini,

      you can get the sum of all others using the below formula

      =NoFilter(Sum([Measure])Where ([rank test]>5 ))

      Since Others are having rank more than "5" 

      Regards

      Subbu

  • avatar image
    Former Member
    Aug 28, 2013 at 05:36 PM

    Hi Sini,

    Did you get the solution..?

    Regards

    Subbarao

    Add comment
    10|10000 characters needed characters exceeded