Skip to Content
Author's profile photo William MARCY

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.

/wp-content/uploads/2013/12/01_340554.png

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.

/wp-content/uploads/2013/12/02_340555.png

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.

/wp-content/uploads/2013/12/03_340571.png/wp-content/uploads/2013/12/04_340572.png

/wp-content/uploads/2013/12/05_340573.png/wp-content/uploads/2013/12/06_340574.png

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.

/wp-content/uploads/2013/12/07_340575.png/wp-content/uploads/2013/12/08_340576.png

/wp-content/uploads/2013/12/09_340577.png/wp-content/uploads/2013/12/10_340578.png/wp-content/uploads/2013/12/11_340579.png

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 :

/wp-content/uploads/2013/12/12_340580.png

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.

/wp-content/uploads/2013/12/13_340581.png

More Webi tricks on Webi Tricks Summary : http://scn.sap.com/docs/DOC-50171

Assigned Tags

      9 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo William MARCY
      William MARCY
      Blog Post Author

      Added to Webi 4.x tricks : summary for a better visibility.

      William

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Neil Mitchell-Goodson
      Neil Mitchell-Goodson

      Here's another method. Using good old E-Fashion to demonstrate.

      I'm using 2006 Sales data per Store to demonstrate:

      Efashion1.JPG

      Sort the data by the Sales Revenue column in descending order.

      Create the following variables:

      mRank (must be a dimension):

      =0

      Ranking:

      =Rank([Sales revenue];([Store name]);Top)

      Filter:

      =If [Ranking]<=[mRank] Then 1 Else 0

      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.

      Efashion2.JPG

      It's quite basic, but the advantage is it works in 3.x and doesn't require building multiple tables.

      HTH

      NMG

      Author's profile photo Former Member
      Former Member

      Good and faster method. Thanks for sharing.

      Regards,

      Sippy

      Author's profile photo Former Member
      Former Member

      Hi,

      It is good Trick..............

      If the report have three dimentions and one measure then how to find out top three values....

      Regards,

      Venkat

      Author's profile photo Former Member
      Former Member

      Thanks for sharing.

      Author's profile photo Moumita Maity
      Moumita Maity

      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 😕

      Author's profile photo Former Member
      Former Member

      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?