Prompt Value based Ranking / Top N Value Report in BO

This is to discuss about, how a Rank / Top N Country Sales report can be developed based on user Prompt values. Report should display the RANK / Top N country Sales based on the User inputs. If the user selects 10 as input report should display Top 10 Country in the sales wise. . If the user selects 100 as input report should display Top 100 Country in the sales wise, same as whatever the number they want to see in the report.


  • User can only see maximum of 1000 top values (using RANK) in Desktop Intelligence Reports and this is a limitation in the Deski Reports.
  • There are no limitations in Webi reports to show top values based on RANK.

Routes to achieve the Top N Sales Report

1.    Create a dummy Conditional Prompt object in universe designer to enter the Top N Numbers (This object is used to get the user input at the Query level there won’t be any calculation in the Query).

Prompt Value Based ranking1

2.    Create the Top N Sales report by adding the required objects in to the Query panel along with Top N Country Conditional object.

Prompt Value Based ranking2

3.    Create User Response Variable to get the Top N input from Prompt and make that as Number.

Top N Country Num
=UserResponse(“RANK” ,”Enter the Number for Top N Countries (e.g. 50)”)

4.    Create Product Sales Rank based on Country.

Product Sales Rank
=Rank(<Country> , <Product Sales Amount>)

5.    Create Top N Country Rank variable to match the input prompt values.

Top N Country Rank
= If <Product Sales Rank><=<Top N Country Num> Then <Product Sales Amount> In (<Country>)

6.    Now apply the Ranking on top of Country object in the report. Select the Country cell in the report and apply Ranking.

Prompt Value Based ranking3

7.    Select Top Check Box and put value as 1000 (this is the maximum allowed Ranking in Deski Report) and then select the drop down variable as “Top Country Rank”.

Note: If it is in Webi report, there is no limitation for Top N Ranking.

Prompt Value Based ranking4

8.    Report has refreshed Input as Top 10 and below is the result.

Prompt Value Based ranking5

Prompt Value Based ranking6

If the report refreshed Top N Value as 15, your report will show only Top 15 Country based on Product Sales Amount.

Prompt Value Based ranking7

This will help you to see the Top Country sales report dynamically and the same can be applied for lowest sales performed in Country.

This method would be useful when you create report based on Section Detail or Breaks. For example, when you create a report based on Section Detail, each section your report should show the Top N country sales details based on report Input.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.