Skip to Content

Consider a scenario where multiple measures are shown in a single report table. Now the requirement is to rank the table based on one of the measure as selected by user. I will use e-fashion universe to show how we can achieve this.

 

In e-fashion universe we have 2 measures one is Revenue and other is Number of Guest. We want a report with Country, Resort, Revenue and Number of Guest.

 Country-Resort By Number of Guests, Revenue

 

Now based on user selection the above table should Rank either on Number of Guests or on Revenue.

 

To achieve it we need to modify e-fashoin universe. We will need one custom prompt which will ask user to select either  Number of Guests or Revenue for Ranking. Below steps are required for it: 

 

1. Create one dimension object name it as Rank By. This object is buiuld only to prompt custom Lov.

Rank By - Object

 

2. Go to second tab Properties and Click on Edit. It will open the query panel for the object.

Rank-Lov

 

3. Then click on SQL button and modify the SQL as follows

Rank By - Custom Sql

Check the property Do not generate Sql before running. It is to get the custom list of values(lov) for user prompt.

 

4. Now create a prompt as mentioned below. This prompt will be used in report for selecting one of the two measures for ranking.

image

 This finishes the change in Universe.

 

In our Web Intelligence report below steps need to perform:

1. Create the Web Intelligence document with objects Country, Resort, Revenue and Number of Guest as shown below:

Webi Report

 

2. Now in the document create a variable which will be used for ranking based on user response for Rank By prompt. It is sorting Resort based on either Revenue or Number of Guests as shown below

Rank By Variable

 

3. Edit the query to include the Rank By prompt in it:

 Rank By Prompt in Query

This complete the build of the report.

 

On refreshing the report, the Rank By prompt will ask user to specify the measure on which ranking is required.

User Selection - No. of Guests

 

Say as shown above if user selects Rank By as Number of Guests, it will sort the table based on Number of Guests

image

 

Refresh the report again and select Revenue as Rank By as shown below:

image

 

This will result in table being Ranked by Revenue:

image

 

So using custom lovs we can Rank the table dynamically. As per user response the tables get ranked accordingly.

To report this post you need to login first.

2 Comments

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

  1. Bilal Kdouh

    Hi Nirmal, i’m not able to see the pictures of your blog, i have to implement your solution urgently on one of our reports, please can you provide me the pictures by email? basically what i need is the custom sql that you have used for your rank dimension, even it will be helpful if you drop it here by comment.

    Thanks

    (0) 

Leave a Reply