Skip to Content

Top/Bottom N Ranking in WebI Report

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

Pic1.jpg

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

Pic3.jpg

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.

Pic4.jpg

Adding this object in the report.

Pic5.jpg

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

Pic6.jpg

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.

Pic7.jpg

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

Pic8.jpg

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

Pic9.jpg

Testing the Functionality

Refresh the report and enter the prompt as 3

        Pic11.jpg       

Refresh the report and enter the prompt as 7          

            Pic13.jpg

14 Comments
You must be Logged on to comment or reply to a post.