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.

Note:

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

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

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.

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.

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

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

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.