Skip to Content

Create dynamic “Other” group in charts

One of the requirements that users may often have is the ability to group all unused dimension values in a new group named “other” or anything else.

The “Other” group is used to show in a chart all unused values beside the values that we want to be visible in the chart like the below screenshot.

Dynamic 'other' group in charts - 1.png

It’s currently possible to create such group by:

  • Creating a new variable and add the dimension values in this new variable manually using “if then else” function
  • Using the “group” feature in Web Intelligence:
    Dynamic 'other' group in charts - 2.png

The main limitation if this feature is that the “Other” group is static. So it’s valid for a given purpose or a given user but not valid for all situations.

The objective of this document is to show how we can create dynamic “Other” group in a Web Intelligence report using input control.

We need to create a table only containing the dimension we want to be used in the chart: this table is always hidden. It will be used by an input control to filter the dimension values.

The we create the input control based on the dimension we want to be used in the chart and liked to the previous created table.

The input control is multi-selection (check box or list box).

Dynamic 'other' group in charts - 3.png

Now we have to create 2 variables.

1. The first variable named “Current Report Filter” contains the following formula:
=Replace(Replace(Replace(Replace(Replace(ReportFilterSummary(ReportName());Char(13);””);Char(10);””); “{ “; “{“);” }”; “}”); “, “; “,”)

The formula takes the data from the ReportFilterSummary(ReportName()) function:

  • ReportFilterSummary()
  • ReportName()
  • ReportFilterSummary(ReportName()) contains all the filters for the current .

Here is an example of ReportFilterSummary(ReportName()) formula:

*** Filter on Report Waterfall  ***

Filter on Block Filter Country:
Country In List { South Africa, Argentina, Switzerland, Germany, Russia, Italy, China, Denmark, Indonesia, United Kingdom }

Then we remove all line feeds, and remove redundant spaces.

Here is an example of the formula after the cleansing:

*** Filter on Report Waterfall  ***         Filter on Block Filter Country:               Country In List {South Africa,Argentina,Switzerland,Germany,Russia,Italy,China,Denmark,Indonesia,United Kingdom}

2. The second variable named “Selected Country” is based “Current Report Filter” variable and is used in the chart definition and contains the following formula:
=If Match([Current Report Filter]; “*{” + [Country] + “}*”) Then [Country]
Else If Match([Current Report Filter]; “*{” + [Country] + “,*”) Then [Country]
Else If Match([Current Report Filter]; “*,” + [Country] + “}*”) Then [Country]
Else If Match([Current Report Filter]; “*,” + [Country] + “,*”) Then [Country]
Else “Other”

The formula is adding a “*” before and after the dimension value to be sure that the value is unique. Then we check if the value matches the following criteria:

  • {*value*,
  • , *value*}
  • , *value*,
  • {*value*}

So if the dimension value is not in the filter then we add it in the “Other” group.

So we are now ready to play with the input control and dynamically change the “Other” group content and chart display.

Dynamic 'other' group in charts - 4.png

We can duplicate the report and have the “Other” group behaving differently for each report and each chart.

Dynamic 'other' group in charts - 5.png

Dynamic 'other' group in charts - 6.png

In the attached document there are several charts that illustrate the document purpose.

You can download the Web Intelligence report attached to that publication.

Didier MAZOUE

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. JinChong Tsai

    Thank you for sharing this, Didier.

    Can you please save this Dynamic other group in charts.wid Webi report in 14.1.7 or BI4.1 SP07 when you have a chance?

    I got “The action cannot be performed” error when opening the attached report in BI4.1 SP07 WRC.

    Regards,

    Jin-Chong

    (0) 

Leave a Reply