How to pass value of Input control to other tabs

Courtesy of David Lai:

Selected States:

=If( Pos(ReportFilterSummary(“Summary”);”State In List { “) > 0)
Then Substr(ReportFilterSummary(“Summary”);Pos(ReportFilterSummary(“Summary”);”State In List { “) + Length(“State In List { “);999)
Else “”

=If(Match([Selected States];[State]+”*”) Or Match([Selected States];”*, “+[State]+”*”) Or [Selected States]=””) Then 1 Else 0

One of the major limitations in Web Intelligence is the ability to use 1 input control to control filter selection for “All” tabs.

For example, if I had 5 tabs and wanted to have a country input control that is applied to all 5 tabs, I would need to create a separate input control for each tab.

In addition, if users want to filter on a particular country during runtime, they must REMEMBER to click through each andEVERY tab which is a huge hassle!

I’m not sure why SAP hasn’t provided an option to apply an input control to the whole document, but for now there’s a workaround that we can use.

Heres how to apply the workaround on a multiple selection input control.

In our example we will create a simple report that contains 3 tabs and 1 input control. We will filter on the country dimension with our input control.

Step 1

Since we can’t have the same input control repeat on multiple tabs, what I usually do is create a Summary tab that acts as a cover page as well as the control center for input controls.

Image link

Step 2

Create a variable that grabs the items the user has selected. To do this we do a simple substring using the appropriate positioning. The first part of the “IF” statement tells us that the user has selected items from the input control, therefore we do a substring. Otherwise “” means that the user wants to see everything.

=If( Pos(ReportFilterSummary(“Summary”);”Country In List { “) > 0)
Then Substr(ReportFilterSummary(“Summary”);Pos(ReportFilterSummary(“Summary”);”Country In List { “) + Length(“Country In List { “);999)
Else “”

Step 3

Create a filter that will filter the report elements based on what input control items are selected.

Every row whose country is in the selected variable will be marked as 1.

=If(Match([Selected Countries];[Country]+”*”) Or Match([Selected Countries];”*, “+[Country]+”*”) Or [Selected Countries]=””) Then 1 Else 0

You may be wondering why we have the 2 match statements in the beginning of the If statement. The reason is that the first match takes into account when an item is in the front of the list and the second match takes into when an item is not in the front.

One might ask why can’t we just have 1 match statement that says Match([Selected Countries]; “*”+[Country]+”*”). The reason is that words that have subset words will show up when they shouldn’t

For example “Democratic Republic of Congo” and “Republic of Congo” are 2 countries where the words in “Republic of Congo” are a subset of “Democratic Republic of Congo”. If we were to select “Republic of Congo” in our input control, it would also match “Democratic Republic of Congo” as a match because the * in front of [Country] would include the “Democratic” portion.

Step 4

On each of the report tabs, click on the analysis tab and then on the filter icon.

Drag the filter object on the panel and filter on values that are 1.

Image link

Image linkNow every time you make an input control selection, the 2 report tabs will filter on your selection appropriately.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.