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:

Dimension values in 1 cell - 1.png

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:

Dimension values in 1 cell - 2.png

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:

Dimension values in 1 cell - 3.png

We can also add measures and obtain this report:

Dimension values in 1 cell - 4.png

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.

Dimension values in 1 cell - 5.png

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

To report this post you need to login first.

Be the first to leave a comment

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

Leave a Reply