Skip to Content
Author's profile photo Rakesh Pattani

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

Assigned Tags

      14 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Neil Mitchell-Goodson
      Neil Mitchell-Goodson

      Why bother with a Universe prompt when you can much more easily use an Input control (assuming XI 3+)?

      Author's profile photo siva kumar
      siva kumar

      After seeing your comment I have tried to achieve the same using Input Control but some I am unable to do it... can you guide me?

      Author's profile photo Neil Mitchell-Goodson
      Neil Mitchell-Goodson
      Author's profile photo siva kumar
      siva kumar

      Thanks Neil... that was a helpful post.

      Author's profile photo Former Member
      Former Member

      very useful. thanks

      Author's profile photo kalyan Tunugunta
      kalyan Tunugunta

      hi rakesh,

      thanks for providing useful stuff, but i messes up when creating a prompt at universe level for object topn.i created a prompt syntax as
      @prompt('enter the top n:','N', ,mono,free,not_persistent, ,) after parsing it shows an error "parse failed:invalid definition (unv0023).error parsing default values parameter (7th parameter).any of you help me out to execute this scenario with out error.

      Author's profile photo Shankar Choudary
      Shankar Choudary

      Hi Rakesh,

      Very helpful post for the beginners, When we are creating prompt @ universe level, getting the error.

      I created a prompt syntax as -->@prompt('enter the top n:','N', ,mono,free,not_persistent, ,) after parsing it shows an error "parse failed:invalid definition (unv0023).error parsing default values parameter (7th parameter)


      Can you please provide correct syntax for executing the above..

      Author's profile photo Rakesh Pattani
      Rakesh Pattani
      Blog Post Author

      Even though it parses with the error, when you run the report it won't throw any error

      Author's profile photo Neil Mitchell-Goodson
      Neil Mitchell-Goodson

      Drop the last comma:

      @Prompt('enter the top n:','N',,mono,free,not_persistent)

      HTH

      NMG

      Author's profile photo Shankar Choudary
      Shankar Choudary

      Hi Neil,

      When I parse the above the given statement I am getting different error now

      "Parse Failed: Exception: DBD, [Microsoft][ODBC Microsoft Access Driver] Syntax error in From clause.State:42000

      Author's profile photo Neil Mitchell-Goodson
      Neil Mitchell-Goodson

      That's because your prompt isn't referencing an object, e.g. there is no "FROM" part of the SQL query.

      If you export the universe and then test the prompt with another object from a table, it should work.

      Author's profile photo Shankar Choudary
      Shankar Choudary

      Hi Neil,

      Its works perfectly, Thanks for the info.

      Author's profile photo kalyan Tunugunta
      kalyan Tunugunta

      HI Ravi Shankar,


      Please try with below syntax.


      @prompt('enter the top n:','N',,mono,free,not_persistent,,)



      Thanks

      Kalyan

      Author's profile photo Former Member
      Former Member

      Hi Rakesh,

      Thanks it is very useful. Can you explain what is the formula to be used for bottom n number of records. Can you explain as in detail like top.

      Pic7.jpg