SSRS – Matrix that adds a new column each time 5 rows are filled with data

Posted by

What if you want a dynamic list of values in a matrix but with a maximum of 5 rows. How do you create a matrix like this? I thought this should be an easy job but I found out it was not really simple…

I tried to create a matrix like this for a dynamic list of countries. In this blog I will explain how you can achieve this with a few simple steps.
1. You need to create an MDX(I used a SSAS datasource) query that returns the list of countries with a numbering:

2. Next thing you need to do is create a matrix:

3. Next and last thing you need to do is the following:

  • Use the following expression for the row group: =(Fields!Country_Number.Value – 1) Mod 5
  • Use the following expression for the column group: =Floor((Fields!Country_Number.Value – 1) / 5)

Result:

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