Group dimension values in a single cell

[Former Member]

I often seen the requirement to display all members of a given dimension in one cell separated by any character.

So the idea is to use Web Intelligence variables to group all dimension values (with or without a condition) in a single variable that can be used anywhere in a report.

So rather to display a classic table like this:

Country

USA

UK

France

Germany

Canada

Japan

China

Brazil

Spain

Russia

India

Sometimes we want to display the table like this:

Country

USA UK France Germany Canada Japan China Brazil Spain Russia India

Or like this:

Country

USA;UK;France;Germany;Canada;Japan;China;Brazil;Spain;Russia;India

The result can also depend on other dimensions such as list of available colors for a given car.

Group dimension values in a single cell

Before explaining the solution, here is a screenshot of a report I created with my solution:

To obtain the results we need 2 variables per dimension and I will explain why.

First variable named [All Store_Stype]

=(Previous(Self) ForEach ([Store_Stype])) + Char(10) + [Store_Stype]

Formula explanation:

1. Take the previous value of a given dimension
2. Add a character separator, it can be anything: linefeed, comma, semicolon, dash, etc.
3. Take the current value of a given dimension

In the Previous(Self) expression I added a ForEach expression. This is mandatory, otherwise the result will only display the current value of the dimension.

Now if we add this variable in a report, here is the result we obtain:

You can notice that at each occurrence of a dimension value the variable is increased with the current dimension value.

But in my requirement, I am only interested by the last computation.

So to do that I need a second variable. I cannot do it in a single variable because I need a dimension variable first and the second expression is always a measure and cannot be changed according to Web Intelligence.

Second variable named [Max Store_Stype]

=Max(Right([All Store_Type];Length([All Store_Type])-1))

Formula explanation:

• Max expression to only retrieve the last computation. This is the restriction because Max implies a measure in Web Intelligence.
• Right

Now we can create such reports:

We can also add measures and obtain this report:

The measures value is in fact the sum for all Dimension values. In my example there is no dependcy on other dimensions, no context.

Products bought per year by a customer

But we can imagine an advanced usage.

I created a “Customer Info” report. The goal is to list all product bought by a given customer for a given year with the Quantity and Amount for this list of products.

To obtain the result the first variable needs to be modified to take into account the context:

=(Previous(Self) ForEach ([Product Name]) In ([Customer Name];[Customer].[Year]) ) + Char(10) + [Product Name]

The core of the formula did not change. I just added the context: In ([Customer Name];[Customer].[Year])

I hope you will find several usages of this solution in your report.

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

Didier MAZOUE