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)