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

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: