Top/Bottom N Ranking in WebI Report
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
Why bother with a Universe prompt when you can much more easily use an Input control (assuming XI 3+)?
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?
Consult this article:
Top and Bottom N ranks in a single table with one Input control in webi
Thanks Neil... that was a helpful post.
very useful. thanks
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.
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..
Even though it parses with the error, when you run the report it won't throw any error
Drop the last comma:
@Prompt('enter the top n:','N',,mono,free,not_persistent)
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
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.
Its works perfectly, Thanks for the info.
HI Ravi Shankar,
Please try with below syntax.
@prompt('enter the top n:','N',,mono,free,not_persistent,,)
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.