Webi tricks : Interactive selecting and viewing TOPn data
Hi everyone,
In this trick I will show you how to dynamically display data tables with differents ranks in Read mode without entering in Design mode.
Application : Web Intelligence 4.1 SP2
Use Case : I want to dynamically see on the same report, all my datas and add a ranking with different TOPn without editing the document in Read mode.
Create a report and add a table
Insert your first table with all data displayed.
Create your TOPn selection variable
In order to set up dynamic visibility, I’m going ti use an Input Control with a variable. This variable will allow user to choose the level of ranking of tables displayed on the report.
Create your TOPn Input Control
These steps show how to create your input control with custom values corresponding to the number of tables you want to display on your report. I’ve chosen a Radio button but you can choose other control. The important stemp is to configure custom values. There’s no dependancy for this input control.
Create your TOPn tables and ranking
Now, you can duplicate your first table and change format to make difference between each rank value. To make it real, you can use relative position to ensure the relative position of tables whatever the data displayed.
Hide/Show table dynamically
All is ready to hide tables according to the selection of your input control previously created. Right clic on a table, go to “Format” and enable “Hide when following formula is true” for each table sucha as below :
For example, my table named “Table_TOP5” will be displayed only when my variable “TOP” will obtain value “TOP5” with my input control. Other case will hide my block.
Repeat this step for each block and you’re done !
Conclusion
When opening my document, I can choose which kind of ranking I want to read and export to answer quickly to a business issue. I don’t to ask someone to modify the document in order to change ranking values.
More Webi tricks on Webi Tricks Summary : http://scn.sap.com/docs/DOC-50171
Added to Webi 4.x tricks : summary for a better visibility.
William
Hi william
Its very Help ful , and impressive , Its good idea to collaborate all those links in a single blog , hope it will be very helpful for all. looking forward for more links in your blog 🙂
Thanks & Regards
Sunil
Hi William,
When user uses the custom values input control in View Mode, got
Error message:
Your security profile does not include permission to use the formula language.
For developer role it won't have this issue. The suggestion I found is to
Contact your BusinessObjects administrator to request the ability to use the
formula language and create variables in Web Intelligence documents.
In CMC application\Web Intelligence\User Security\'Reporting - Create formulas and variable' is enable for everyone. This is BO 4.1 environment.
Any idea how to solve this issue?
Thanks.
Bruce
Here's another method. Using good old E-Fashion to demonstrate.
I'm using 2006 Sales data per Store to demonstrate:
Sort the data by the Sales Revenue column in descending order.
Create the following variables:
mRank (must be a dimension):
Ranking:
Filter:
Create an input control for mRank - use a slider or entry field, doesn't matter. Then, select your report block and apply the Filter object to it as a report filter, and set it to Equal 1.
Finally, enter a value into your mRank input control. You should see your report block filter accordingly to show only the selected number of entries. If you entered "5" into your Input Control, you should only see 5 entries in the block.
It's quite basic, but the advantage is it works in 3.x and doesn't require building multiple tables.
HTH
NMG
Good and faster method. Thanks for sharing.
Regards,
Sippy
Hi,
It is good Trick..............
If the report have three dimentions and one measure then how to find out top three values....
Regards,
Venkat
Thanks for sharing.
Hi All
This document is impressive,But I do have a doubt what is we have huge volume of data, and we want to display Top 100 or Top 50?? Any other solution, coz I got million records loaded and i got to display that.I mean, any value from Top5 to Top50 😕
Dears,
Thanks a lot for the this amazing trick.
I have a small question. If we assumed that we have a table of 1000 rows and 20 columns and we created 5 duplicate tables. The difference between each table is a value in the prompt. If we applied the same method mentioned above, will that affect the performance of the report?
In other words, will all the 5 tables be loaded and then 4 will be hidden or only one table will be loaded?