Top/Bottom N Ranking in WebI Report

[former_member189638]

Requirement Details

This Blog shows how we can do a dynamic ranking based on the values entered by the user in the prompt.

Creating a basic WebI report on eFasion Universe using State and Sales Revenue object. Following is the output of the report as shown below

Now if we rank the State Object based on the Sales Revenue, following will be the output. What we want here is the User to enter the value and the report should show the Top N values. For e.g. User enters 5 then report should display Top 5 States. User enters 3 then report should display Top 3 States

Editing the Universe and Report

For this, first we will create a Dimension object of type Number with the Prompt function in the Universe as shown below.

Adding this object in the report.

When we execute the report, it will prompt for the Top N value. We will enter 5 here.

After executing the report, we will create a Measure variable with the formula to check the Rank is less than or equal to value entered by the User. We will use this Measure in the report table instead of Sales Revenue Object. Sort it by Descending Order.

After that select the Top N Sales Revenue Column and create a filter to eliminate the Null rows as shown below.

You can then see the report output as shown below. Save the report and open it in view mode to test it.

Testing the Functionality

Refresh the report and enter the prompt as 3

               

Refresh the report and enter the prompt as 7