Web Intelligence – Input Control that affects all tabs

August 13, 2013 by: David Lai

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 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.

summary page
image-762

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.

select filter
image-763

filter pane
image-764
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

Comments

28 Responses to “Web Intelligence – Input Control that affects all tabs”
  1. venki says:

    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.

  2. Thomas Evans says:

    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

    • David Lai says:

      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.

  3. Riddhi says:

    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.

  4. Rajat Agrawal says:

    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

  5. Philippe says:

    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

  6. turing says:

    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

  7. Sohail Ansari says:

    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.

  8. Jackie Dunn says:

    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??

  9. Deepika says:

    HI David,

    Thanks for the post. Your solution worked for me.

  10. Greg says:

    Thanks, this was very helpful

  11. Richie says:

    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??”

  12. Aaron says:

    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

    • David Lai says:

      Thanks for the comment. Finally they are implementing that feature so that we don’t have to use a workaround :)

  13. Niraj says:

    Hi David,

    On what patch they have implemented this and by what option?

    Thanks

  14. Nix says:

    One feature being touted for XI 4.2 is cascading input controls. :)

  15. Vince says:

    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.

  16. Manohar says:

    HI David,

    Thanks for the post. Your solution worked for me.

    Manohar

  17. Sndp says:

    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 “”

    • David Lai says:

      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

      • Sahil says:

        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

        • David Lai says:

          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.

  18. Sri says:

    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

    • David Lai says:

      Unfortunately it is not possible to navigate to another tab using input controls

      • Vsenni says:

        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.

Leave a Reply


four + = 9