I could be talking about the tables in my database – I do make them skinny by using the right data types – but in this case I’m talking about SQL Server Reporting Services tables. You’ve seen them before. The average table is bland and boring, with nothing to distinguish it from the next, and no reason for you to remember the data in it.

Is this memorable?

Being the Tim Gunn of SSRS, I’m going to transform this table so it looks good.

Formatting

The default formatting isn’t easy to read. For example: for Order Date, I only want to see the date; and I want the sales columns to show dollar signs and decimal points. All of this can be done using the Format property of a cell. (You can find formatting options here.)

I set my OrderDate text box to “d” for short date, and my Subtotal, Tax Amt, Freight, and Total Due text boxes to “c” for currency.

I do other minor cleanup as well – I set the font of the report title to bold, I set the font of the column headers to bold, and I adjust the width of some of the columns.

The report is looking better already.

Grouping

The order of the records isn’t the way I want to see it. We’re looking at the information sorted by order ID. The purpose of this report is to see, at a glance, which sales territory and which sales person have the highest sales for a given date range. I need to add groups for territory and salesperson.

Groups can be added at the row and column level, by using the grouping pane. I add two groups to this table – one based on sales territory ID, and the other based on sales person ID.

I add totals at the group levels and preview the report again. It’s much easier to read now.

Toggling

If I exported this report to PDF, it would be 27 pages. No one is going to read through all of that to try to figure out which territory had the most sales. I need to either reduce the information in the report, or make it more readable. If I wanted to show only the totals at the territory and salesperson levels, I could select the detail row and set the Hidden property to True – but the blank rows would still appear under the groups. Or, I could delete the detail row – but then I couldn’t view the data for a sales person if I wanted to.

What I really want to do is make it so I can collapse and expand each section as needed. In SSRS, this is called toggling. I pick the row I want to change the visibility for – in this case, the detail row – and then I set a field that I will toggle on.

In the group properties, I go to the visibility section. I choose whether this row should be hidden or visible when the report is first run. To enable toggling, I check “Display can be toggled by this report item” and choose a text box – in this case, sales person.

This is a huge improvement in readability. The next step is to add toggling at the sales person level, triggered by the sales territory.

Expressions

Not all properties in SSRS are true/false or a set list of options. Many can be configured dynamically, using expressions. Expressions are based on Visual Basic, and can help you do things like set dynamic background colors on cells, change grouping on the fly, or perform mathematical calculations.

Let’s take a simplistic and not-too-realistic example: I want the value at the salesperson level to have a green background if the sales are over $1,000,000. (This isn’t terribly realistic because I’m letting the user choose a date range. It would be more realistic if that was a one-year goal and the user had to pick a specific year.)

In the text box Background Color property, I choose Expression and the expression builder opens. Using an IIF statement, I say, if the total due field is greater than or equal to 1,000,000, the color should be green; otherwise, it should be white.

Now, at a glance I can tell which sales people were “high performers” – without having to look at each field and mentally calculate that.

Expressions allow you to extend the functionality of properties greatly. Become familiar with expressions. Then become great at them. You’ll thank yourself for this bit of knowledge.

Make it Work

Building an SSRS table can be a quick, simple process. Your report will look simple. I challenge you to consider how you can improve it. In each report, pick one element – the formatting of a cell, the ordering of the data, something that can be visualized – and change it. Your reports will stand out, and tell the story better.