Tim Minchin - too risky for ITV !

Watch "WoodyAllenJesus" on YouTube

SSRS 2008 R2 Dynamic Column Width Matrix (Sort of)

Something has been bugging me for a while now about Matrix reports and the column sizing – they are all the same width. I knew there had to be a way to solve this and I have found a work-around that is quite acceptable to me….

So, here is a sample report matrix…looks fairly normal:

And here is the output…note the middle column is bigger than the rest…have you guessed it yet?

Notice the other columns are smaller? Here is how:

1. Add a column inside your existing column group: (I have already done it – you can see I have two columns in my matrix border)

2. Select your column group in the lower editor and put the group editor into Advanced mode by clicking the little down arrow :

This displays the columns as “(Static)” in a list. 

3. Select the second “(Static)” column in the group editor (we just added this one) and add an expression to the “Hidden” property in the Properties window:

In my case, I am showing the column if my “Format” value is “MHTML” and hiding it otherwise.

This will hide or display the extra column depending on the data values we choose, but there is one more step….

4. Merge the cells in the data region by selecting both cells together and choosing Merge Cells from the context menu.

This will ensure that whenever the “extra column” is visible, it is merged with the normal column to provide extra width.

Summary and further work
This still doesn’t allow for true dynamic sizing, but with a bit of clever logic in the expressions and even a couple of extra hidden columns, you could accommodate quite a lot of extra widths (hide one,two or three columns as appropriate to get different sizes).

This could also be applied to make life simpler in reports with columns that need different treatments.
We could have different columns for each column type with different expressions (SUM for values, FIRST for strings etc) and different formatting so that we don’t have really complicated expressions with nested “ifs” in the data, just simple “IIF(columntype=”FOOBAR”,False,True)” expressions in the “Hidden” property of each column and the appropriate formatting (and different actions…)

The Dynamic Tally or Numbers Table - SQLServerCentral

The Dynamic Tally or Numbers Table - SQLServerCentral

A very useful article about how to efficiently use a dynamic Tally table.