Artikelen

SSRS non-ugly #5: The nitty griddy details

Dataviz / SSRS / Uncategorized

SSRS non-ugly #5: The nitty griddy details

In this post - part of my 'creating non-ugly SSRS reports' series - I'll introduce you to a design and layout technique quite common in webdesign, but AFAIK not too often applied in reporting: using a column grid. Curious? Read on!

One of the easiest ways to create a consistent, modern layout (for us, not being professional designers) is to construct a layout template for our reports & dashboards by defining a 12-column grid, and laying out all report element on that grid[ref]Other column numbers are also possible, but stick to 12 and you'll hardly ever regret it.[/ref]. Descriptions of grid-based design can be found all across the web (James George has an excellent introduction here), here's how to use it for your reports (all units in mm):

Step Possibilities Example
1. Determine the desired report width The paper size used at your client's (420 mm = A3 in landscape)
2. Choose a page margin Anything that looks good to you, rule of the thumb:
3. Choose a column width and gutter [ref]gutter is the space between two columns[/ref] Anything such that

Now, all your report elements need to have a width of , where is any integer .

To make it easy, I've created an Excel sheet with all calculations which you can download here. In our example, this means our report item (chart, graph) can be any of the following widths and x-locations:

Element width Starting column
1 2 3 4 5 6 7 8 9 10 11 12
26 10 44 78 112 146 180 214 248 282 316 350 384
60 10 44 78 112 146 180 214 248 282 316 350
94 10 44 78 112 146 180 214 248 282 316
128 10 44 78 112 146 180 214 248 282
162 10 44 78 112 146 180 214 248
196 10 44 78 112 146 180 214
230 10 44 78 112 146 180
264 10 44 78 112 146
298 10 44 78 112
332 10 44 78
366 10 44
400 10

Notice that by adhering to a grid, you automatically align all items inside your report horizontally.

Because our example above only has 3 report elements, I've borrowed an example Clint Huijbers displayed recently on LinkedIn to show the difference between an evenly sized report and one that's not. First, the 'original' dashboard:

Clint's original dashboard (silly titles are mine)
Clint's original dashboard (silly titles are mine)

As you can see, the tiles are layed out carefully. What change would it make if all items were layed out using the just described grid? Well, this:

Naively applied grid

IMHO, the overall layout provides somewhat more rest for the eyes, but the grid doesn't align very well with the pie charts in the left bottom! In the original dashboard, they were evenly sized. Now, they differ in size and the MT #9 diagram (center bottom) has become almost too narrow. You can fix this in two ways:

  1. Alter the grid
  2. Alter dashboard design

To alter the grid, you could use an 18 or 24 column grid instead of a 12-column. However, this makes the design way more complex (and you've got even more ways to make your dashboard confusing)[ref]A 12-column grid has the great advantage you can divide 12 by 3 as well as 4. You can't do that with an 18-column grid, for example. Of course, with 24 you can)[/ref].

Personally, I would stick with a simple grid and alter the dashboard design so it fits. Rethink for example why you'd need three pie charts in the left bottom[ref]It's always easy to stay in your dataviz-comfort zone and bash on pie charts. If you're open to somewhat broader worldview, read 'The Backlash Against Data Dogmatism'.[/ref]. In this particular dashboard, the pies were used to compare current year with two previous years. Personally, I would do this with a 100% stacked bar chart, a minor change creating space for the other charts on the bottom row. Now, the dashboard adheres to the grid and doesn't look crammed:

Adapted layout

To finish this one, let's apply all other 'tricks' I've shown in earlier posts and above (including the 'web look' and A3 sizing). Because it's a real-world example, I've not allowed myself to alter the main colors :

Improved dashboard incl weblayout

This one still fits on a single page and is very crisp to see - printed as well as on your modern hi-res screen. I'll be the first to admit still much can be fine-tuned about the report in terms of font sizes, grey shades & colors, exact placement and sizing of different report items. If you've got any suggestions, leve them in comments below! Still, I think it's a major improvement to have a crisp-looking report (A3 sizing), modern looking ('web look', grid layout, tricks from earlier posts) and more focused on the content (less clutter and visual weight for non-essential report items).

More about grids

I've shown how to use a grid for report layout in a consistent and more-or-less easy way. Apart from my Excel workbook-tool to calculate your own grid system[ref]http://www.msbiblog.com/wp-content/uploads/2015/12/reporting-grid-calculator.xlsx[/ref], there are plenty other tools and grid systems readily available on the interwebz too - thegridsystem.org has a nice collection.

Initially, this will increase the time you'll spend with reports, especially when learning a new technique like a grid system! Soon enough however you'll see that looking with towards your report design with 'griddy' eyes becomes a habit - and the results are consistent layouts across all reports.

I've thought about ways of fixing this 'extra time' it costs me. One thing I thought of was using rectangles for every 'object', giving the rectangles a recognizable name - for example starting with rect_grid_(name) and more or less position them as I'd like. Then I'd run a tool on top of the RDL (it's all XML after all) to position and re-size all elements according to the grid of my choice. So far, I haven't found time to develop a small script / tool to do this for me though. If I ever will, I'll let you know 🙂 .

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

Comments (6)

  1. […] SSRS non-ugly #5: The nitty griddy details […]

  2. Ken

    Great series!
    How did you manage to achieve the altering fill colours on the line charts? I would like to implement the same styling on my line & bar charts.

    Thanks,

    Ken

    1. Hi Ken,

      Thanks! If I understand you correctly, you want to have one line (visually) with multiple colours. Altering colours inside an SSRS line chart aren’t really altering: in fact they are two series (each with its own colour). So basically the way to achieve this is to split your data into two parts, which are then conditionally displayed.

      Does that answer your question?

      1. Ken

        Hi Koos,

        Thanks for the quick reply! Sorry i didn't make myself clear before. I basically want to replicate the grey and white background you have in 'Meaningful Title 1' & 'Meaningful Title 2' , i understand how to do accomplish this for a a normal table but how did you go about this for a bar chart or line chart like you have in your improved dashboard layout?

        Thanks,

        Ken

        1. Hi Ken,

          Charts have the option to "use interlacing" for so-called "strips". A step-by-step guide how to do this can be found at https://docs.microsoft.com/en-us/sql/reporting-services/report-design/highlight-chart-data-by-adding-strip-lines-report-builder-and-ssrs

          Is this what you meant?

          Regards,

          Koos

          1. Ken

            That's exactly what i was after,!

            Many thanks,

            Ken

Comments are closed.