Skip to Content

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

To report this post you need to login first.

14 Comments

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

    1. 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?

      (0) 
  1. Former Member

    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.

    (0) 
  2. Former Member

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

    (0) 
    1. Former Member

      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

      (0) 
      1. Former Member

        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.

        (0) 
  3. 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

    (0) 

Leave a Reply