Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
Former Member

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.

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:

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

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.

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

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

2 Comments
Labels in this area