Creating a User Friendly Reverse Filter

In Web Intelligence, when we add a report filter to the Report Filter Toolbar, we can select values from the Combo Box, and the entire report is then filtered for that value. This tip does just the opposite. That’s why I call it a reverse filter. When you select a value from the Combo Box, the report should be filtered for all values except the selected value. This tip requires the following tools:

  • Two data providers
    • One for the full report, called “Main”
    • One for the Combo Box, we’ll call it “Location” for this example
  • A Flag variable
  • A block filter

OK, so let’s get started. For this example, we’ll use eFashion. The idea is that, when we select a City from the combo box, that City’s data is removed from the report. We’ll start by creating the two queries. The first query will include everything we need for this report:

Name this query “Main”. The second query should include only the City object, and no query filters. Call this query “Location”. After running the two queries, they will automatically be merged on City. Un-merge them. We need to keep them completely separate.

Next, create a flag variable called “City Flag” with the following formula:

=If(DrillFilters()=[Main].[City];1;0)
Next, turn on the Report Filter toolbar.
Drag the City object, from the Location query, into the Report Filter toolbar.
Finally, place a block filter on the table, where City Flag is equal to zero.
Save your document, then try it out. Select a city from the Combo Box in the Report Filter toolbar, and notice that the selected city is removed form the table, and all others remain. You’ve now created a Reverse Filter.
Advertisements

5 Responses to Creating a User Friendly Reverse Filter

  1. Nix says:

    I can see this is in XI3.1, I’ve tried replicating in XIr2 but I don’t think it as the Show/Hide Report Filter Toolbar and also can’t work out how to apply the block filter.

    Regardless of this, what happens if you have more than one drill filter, won’t it break the reverse filter since DrillFilters() will give you multiple values ?

    • Nix, I originally developed this trick in XIR2, although I had to tweak it a bit in XI 3.x. In XIR2, the report had to be in Drill mode to get the toolbar. But, in order to keep the blocks in the report from being drillable, the objects in the blocks that were drillable, had to be wrapped in the NoDrillFilter() function. This function went away in XI 3.x, and was replaced by a second argument for the NoFilter() function:

      NoFilter([object];Drill)

      I’ve also used this technique with multiple filters. Yes, it results in multiple values being removed. It can work quite nicely.

      • Nix says:

        Someone at work recently showed me 2 ways to get rid of drillable objects in XIr2.

        1. Create a dummy custom hierarchy in the universe, this turns of default hierarchies for all objects.
        2. Create a variable that is equal to the object that you want to remove drill for e.g in the above if we take Lines for example we can create a new variable Lines_No_Drill and it can have a value of =[Lines] then in the report we drop the Lines_No_Drill variable into the report in place of [Lines] object and the drill capability is no longer present.

  2. Nix says:

    I worked it out for XIr2.

    Added a second drill filter on Lines and it did break the reverse filter.

    However by changing the City Flag code to =If(Match(DrillFilters();[Main].[City]+"*");1;0) it worked again. i.e. regardless of what line was selected the reverse filter on City still worked.

  3. Jansi says:

    I remember you talking about this in your ‘Web-I tips and tricks’. 🙂

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: