Hard Work Pays Off: Aligning matrices (SSRS non-ugly #6)

In the previous posts in the 'SSRS non-ugly' series, we looked at grid-based report layouts, a powerful technique to standardize your layout and align all elements within your report. All this grid stuff is cool, but how to cope with matrices? I mean, c'mon Koos - last time you just deliberately left out the matrices in your example, while your original example used a matrix displaying 12 columns AND a row descriptor - which makes 13 columns! That obviously won't fit into a 12-column grid, will it?

Well - actually it does fit into the grid - as long as you remember that the entire matrix is one report element. But in order to align matrices, you've got to do some calculations - therefor, I present to you:

The hard-work-pays-off win: aligning matrices

First off, I have to admit I only use repeating column groups in matrices when I know beforehand the maximum number of columns that will possibly exist. So in my reports, months or quarters are okay to group on columns, but products are not. I find it pretty annoying to have to print a dashboard which is way too wide for one page.

As long as the number of columns is known, you can calculate the available space, treating the entire matrix as one 'report item' (spanning for example all grid columns). Notice that I usually do this only on the very end of the development cycle! It's really a 'fine-tuning' thing which takes some work because of all the calculations.

In the previous post, we used a 12-column grid for our reports with a body width of 420 mm, a page margin of 10 mm, a column width of 26 mm and a gutter of 81. How does that apply to the report we used earlier in the series?

Quick win web look

The SSRS report we used earlier in the series - visually improved a lot, but obviously not (yet) aligned to any grid..

  • the upper chart blocks each span 6 grid columns, so according to my calculations (shown in the previous post) have the following properties:
    • chart block 1 (upper left) left: 10 mm, width: 196mm
    • chart block 2 (upper right): left: 214 mm, width: 196 mm
  • matrix block: left: 10 mm, width: 400 mm

Careful now! Although the matrix block should become 400 mm, it can't be designed as such: as you might know, SSRS scales the space you leave out inside a rectangle proportionally: when you put a matrix inside of a rectangle, leaving 20 mm between the right of the matrix and the edge of the rectangle, SSRS will make sure it stays 20 mm.

So let's keep a margin of 6 mm inside the matrix container (the white area). The matrix itself should now become exactly mm. The column containing store names has to be around 50 mm for the store names to fit, so remaining for the columns is mm. The month (grouped) column therefor should be exactly mm.

At this time, the design mode of SSRS looks as follows:

Matrix layout

This shows why it's the 'hardest' work to align the matrix: in the design mode you cannot see whether the matrix will align at the end of the row. And yes, maybe it's freaky to do this amount of work for your report layout - but the resulting layout turns out to align pretty well and looks quite nice2:

Hard work pays off win

Oh, and before I forget to mention: I've put the two rectangles of the 'upper charts' inside one large rectangle - otherwise the growing matrix would 'push' the upper right chart aside (because SSRS would try to maintain the space that was originally between the right side of the matrix rectangle and the left side of the upper-left chart).

Conclusion

Well, I don't think there was that much news in here, but I hope I've inspired at least a few folks to just do this tedious calculation and create some pixel-perfect reports, even for (semi-)dynamically sized reports.

To be honest, there are two ways other than the one to nail width of your matrices just right.

  1. You can change the SSRS behaviour in regarding what to do with container space that's left with the ConsumeContainerWhitespace property at report level. However, as you can set this only on report level, SSRS will always consume all whitespace everywhere in your report. Yuck - wouldn't recommend using that.
  2. Define all 12  month columns beforehand, filter to display only the month corresponding to the column. Now the matrix has in design mode exactly the same width as it will have in (pre)view mode. In my opinion, this creates even more work than just calculating the column width and setting it once - especially when fine-tuning or updating setting on your matrix.

So in my opinion the easiest way (though still not really easy) to make your matrices pixel-perfect is to do some calculations for the column widths. But it will not always work out: sometimes the number of columns is variable, for example showing the YTD sales per month: Although there's a maximum of 12 months, it is well possible that there are fewer columns displayed by default - which you could fix by adding a CROSS JOIN in your SQL query, or a 'coverage fact', or - well, you figure it. There is always a way to fix your design 🙂 .

 

Series: SSRS non-ugly

  1. SSRS non-ugly #1: Use of Typography and fonts
  2. SSRS non-ugly #2: Formatting Tables
  3. SSRS non-ugly #3: Chart Styles
  4. SSRS non-ugly #4: Think about Layout
  5. SSRS non-ugly #5: The Nitty Griddy Details
  6. SSRS non-ugly #6: Hard Work Pays Off: Aligning Matrices

Founder of this blog. Business Intelligence consultant, developer, coach, trainer and speaker at events. Currently working at Dura Vermeer. Loves to explain things, providing insight in complex issues. Watches the ongoing development of the Microsoft Business Intelligence stack closely. Keeping an eye on Big Data, Data Science and IoT.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Next ArticleBuilding the Poor Man's IoT