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.
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.
2. Go to second tab Properties and Click on Edit. It will open the query panel for the object.
3. Then click on SQL button and modify the SQL as follows
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.
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:
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
3. Edit the query to include the Rank By prompt in it:
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.
Say as shown above if user selects Rank By as Number of Guests, it will sort the table based on Number of Guests
Refresh the report again and select Revenue as Rank By as shown below:
This will result in table being Ranked by Revenue:
So using custom lovs we can Rank the table dynamically. As per user response the tables get ranked accordingly.
2 Comments
