Top-Bottom ranking using input controls in read-only access
Web Intelligence input controls have been introduced in SAP Business Objects Web Intelligence 3.1.
The document goal is to help end users to use input controls to change report behavior, design, appearance, etc. using some Web Intelligence functionalities that are only available for report creators.
This document is one of different documents to be published soon.
Top/Bottom ranking using input controls in read-only access
As an end user, I would like to be able to change the “n” value in a top/bottom ranking.
If I only have “read” rights I am not able to change and I need to ask the designer to do it for me and to republish the document.
This seems a bit painful to me because this is a very convenient feature that I would like to use without relying to anyone else.
To solve this issue, we will create input controls to dynamically change the “n” value for the top ranking and for the bottom ranking.
Then I used 2 techniques to apply the change of the “n” value in the block.
We have to create 3 variables:
- Rank: is a numeric measure and is used by the input control.
- Top OK: is a dimension that references “Rank” and “Top rank” variables and is used in the block filter.
Formula: =If ([Top Rank] <= [Rank]) Then “OK”
- Bottom OK: is a dimension that references “Rank” and “Bottom rank” variables and is used in the block filter.
Formula: =If ([Bottom Rank] <= [Rank]) Then “OK”
The block filter is used to only display rows where “Top OK” and “Bottom OK” are equal to “OK”.
Then a title is added to each block display the number of selected values for Top and Bottom.
- =”Top ” + [Rank] + ” Products”
- =”Bottom ” + [Rank] + ” Products”
Now, we have to create the input control based on “Rank” variable.
Then, I can change the value of Top and Bottom simultaneously.
We have to create 2 variables that will be used by the input controls:
- Top rank: =Rank([Order Quantity];[Product];Top;[Calendar Year])
- Bottom rank: =Rank([Order Quantity];[Product];Bottom;[Calendar Year])
Then I added a row to each block heard to display the number of selected values for Top and Bottom.
- =”Top ” + Max([Top Rank]) + ” Products”
- =”Bottom ” + Max([Bottom Rank]) + ” Products”
Now, we have to create one input control for the “Top n” block and the “Bottom n” block:
Then, I can change the value of Top and Bottom separately.
You can download the document I used to illustrate my purpose.
You can also view the video posted on Youtube: https://youtu.be/0Jg0Twf-RsQ
Thank you. Great guide
Your solution Excellent for 'count' type of RANK...
I want to do the same thing but with cumulative percentage rank
I would like that the end user will be ble to change the “n” value in a tcumulative percentage ranking.
If he will chooce 0.8(80%) the table will be filter and show only he top stores that cumulatively contribute 75% of the revenue
Now If the end user select .75 (75%) the table will show only the top stores that cumulatively contribute 75% of the revenue . and so on...
Can you help with that need?
Great Webi trick !
You've been added to Webi 4.x tricks : summary for a better visibility. Keep posting !