Web Intelligence – Input Control that affects all tabs
August 13, 2013 by: David LaiOne 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 and EVERY 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.
In this article I’ll explain 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.
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.
Now every time you make an input control selection, the 2 report tabs will filter on your selection appropriately.
You can download the example WEBI document (BI4 SP6) HERE
Hi,
Thanks for Posting this valuble info. i used to do same in BO 4.0 sp5 but it did not work. what you have explained in the blog is only for BO 4.0 SP 6? Can you help me to get this in SP 5.
Thank you for taking the time to write this up. I’d like to know if you have actually had it working? I have followed the steps below and it does not seem to work. Additionally, I had a look at the attached file and it does not work as described either. Or perhaps it works intermitently.
The issue that I have found as follows:
The primary table (Report 1) seems to work as expected after input control selection, and the filters seem to be working correctly (ie your case statements give the correct result) but the Business Objects filter (not the variable) on the secondary table when set to 1 or true rather, doesn’t seem to automatically refresh… It seem buggy to me, I can select a value from the Input control and skip to report 2, delete and re add the filter and (sometimes) it seems to filter correctly.. Any ideas?
Im on BO 4.0, SP07, Netezza Database
Thanks for putting it out there none the less!
Thomas
Hi Thomas,
Yes I use this method at one of my clients who are on BO 4 SP5.
Just make sure to re-check your substring formula to make sure everything is correct.
Hi,
I tried the above method.But the problem is that when I refresh the first tab(Report) then only the parameter selected in the input contol on the first tab is reflected on the second tab(Report).
Is there any method to overcome it.
Any help will be appreciated.
Hi – Thanks for this blog. I tried it but it is not refreshing the data in other tabs as soon as the value of input control is changed unless we go to each tab and change a value of filter. This is a kind of workaround but it is not of much use as it is not refreshing the tables on each tab on user selection. Do you know how to achieve it?
Thanks
Hi Rajat,
What version of BOBJ are you using?
When I wrote the article I was at BOBJ 4.0 SP4
Thanks
David
Hi David,
it works. Thanks a lot.
the filters on the others tab update well but don’t apply. i must open the filter to apply it.
How can i do to auto appli filter?
sorry for my english.
TIA
Hi David:
Nice post…, I tried to test this solution at my BOXI 3.1 SP3. But I have some problem: just work if I refresh the report.
So, I select one option at first page, but to apply all the others pages, I must refresh the report.
Any idea?
Thanks a lot
The sample report you have attached is also not working with BO 4.1 SP1. Maybe this example works with some versions and not other.
But thank you for the great effort.
Hi Sohail,
It should work. Maybe you missed something on one of the steps.
Thanks
David
This works pretty well! The only issue I’m seeing (we are on 4.1 sp2) is when I go back to “select all” – the table on the 2nd tab is not updating. If I change from one “choice” in the input control to another “choice” then those changes are reflected in the table on the 2nd tab. Any thoughts how to refresh the 2nd tab when selected “Select All” from the Input Control??
HI David,
Thanks for the post. Your solution worked for me.
Thanks, this was very helpful
Hi did Jackie Dunn’ question get resolved? I’m having the same issue. It doesn’t work when I select All Values.
thanks
“This works pretty well! The only issue I’m seeing (we are on 4.1 sp2) is when I go back to “select all” – the table on the 2nd tab is not updating. If I change from one “choice” in the input control to another “choice” then those changes are reflected in the table on the 2nd tab. Any thoughts how to refresh the 2nd tab when selected “Select All” from the Input Control??”
Hi David,
With BI 4.1 SP06, there is a global input control specification. With this patch input controls can apply to all the tabs in document.
http://scn.sap.com/docs/DOC-64250
Thanks for the comment. Finally they are implementing that feature so that we don’t have to use a workaround 🙂
Hi David,
On what patch they have implemented this and by what option?
Thanks
One feature being touted for XI 4.2 is cascading input controls. 🙂
This feature exists now in 4.1 sp6, but this nice workaround is still needed with element link because the input control created in this case is only tab dependant.
HI David,
Thanks for the post. Your solution worked for me.
Manohar
Seems to me it is not working when we have blank/empty value in the input control values.
Did any face this issue and any resolution please ??
I did some research and found that this variable is not working because the input control I developed has Blank/empty value and regular value
Code:
=If( Pos(ReportFilterSummary(“Summary”);”Country In List { “) > 0)
Then Substr(ReportFilterSummary(“Summary”);Pos(ReportFilterSummary(“Summary”);”Country In List { “) + Length(“Country In List { “);999)
Else “”
Hi Sandeep,
With newer versions of Web Intelligence (I think 4.1 onwards, you can now choose whether an input control affects the entire document or a report tab)
My advice would be to let users know that this feature will be available once you have upgraded to 4.1+
Thanks
David
Hi David,
Thanks for sharing this valuable information.
I Just want to know from where we can choose Input Control affects entire document or a specific report tab.
I am on BO 4.1 Sp5.
Thanks,
Sahil
In BI 4.1, when you create the input control it asks you for the scope of whether or not you want to apply the filter to the report tab or the entire document.
Hi Everyone,
I heard its also possible to navigate from one tab to another using Input Control within the same document. I am looking to navigate from a Summary Tab to Detail tab by clicking on a cell. I do not want to use document linking. Is it possible using Input control? Can someone assist with the steps for implementing that.
Thanks
Unfortunately it is not possible to navigate to another tab using input controls
Hi David,
Thanks for sharing this valuable information.
I have a requirement to hide and show the report block based on the Element Link unfortunately i promised to the user its achievable.
I have 4 Table Block.
Table 1 : Master table 1
Table 2: Child Table of Master table 1
Table 3: Master table 2
Table 4: Child Table of Master table 3
Requirements
When the user Click Table1 then Table 2 should enable.
When the user Click Table 3 then Table 2 Should Hide and Table 4 should enable .
The above one I achieved it based on the below logic. I have written this logic in Table properties (Hide when the formula is true)
Table 2 = If ((Pos(ReportFilterSummary();”Table 4″) = 0) ;”Hide” ; “Show”) =”Show”
Table 4 = If ((Pos(ReportFilterSummary();”Table 4″) = 0) ;”Hide” ; “Show”) =”Hide”
Now if the user Click Back to Table 1 then Table 2 should bring back and Table 4 should hide. But I couldn’t able to achieve this
Can you please help me.
Hello,
Anything above SAP BI 4.1 SP5 allows input controls to affect the entire document.