In this quick post I’ll show you how to create and format a report so that you can configure individual table cells to display their contents with a mixture of text formatting. I have heard many people asking how to achieve this and it wasn’t until I was reading a blog post recently that I came up with this idea. Apologies to the blog that gave me the inspiration, I have lost the link and cannot recall the site that it was on. If I recall this information I’ll edit this and put the details in.

So, how do we achieve something like this?

At this point it’s important to set some expectations. I’m assuming the reader has the knowledge to create a report based on a database stored procedure, for the purposes of this demonstration I am using a customised version of Tim Ford’s method of collecting data about database file metrics. There is a link to his document in the references selection below and I would strongly recommend you take a look and see how it is used.

Let’s start from a basic situation where the report has been created, simply clicking through the New Report Wizard and connecting to the stored procedure followed by a small amount of layout tweaks (such as added Report Header and Footer, columns width changes and merging some cells in the group headers).

This layout is a bit rubbish – the physical file name is cluttered and in the way when reading the logical filename to the left and the size values on the right. Let’s pop back to design view and make some changes. Right click on the cell and select Expression from the pop-up menu options.

In the editor that you are presented with adjust the function to be:

This will bring the values from both database columns into the single SSRS table cell but on separate lines. This means we can delete the other column in the report table and preview it again to see something similar to the image below.

This is a little more clear but it would now be nice if we could reduce the physical filename font a little and possibly even change it to a grey rather than black. The physical filename information is what I would see as secondary information on this report. I see this sort of report being used by a DBA firstly looking for a database name, checking for the Free Pct values and then looking for the physical file name value in order to go and make changes. You don’t look to the Physical filename to locate data on the report, like you might with a database name, but it is something that you would look to after you have identified that a file is low on space.

Sadly as this is all in one text box this isn’t possible within the current report structure. You can only apply formatting to a complete text box.

We need to make a couple of changes to the report before we can carry out the formatting that we want to.

Start off by deleting the expression in the table cell and expanding the table row so there is more room to work. Drag a rectangle from the BIDS Toolbox into the enlarged cell. It will snap to the whole size of the cell. For the illustration below I have changed the rectangle properties to have a Solid Red border so you can see it (numbered 1. in the image). Next drop in two textboxes (numbered 2 + 3). Again I have given them different borders so that you can see them in this demonstration.

Having the two textboxes in the rectangle fixes their relative positions but will allow us to format them as we want to. Move textbox 2 up to the left and make changes to the font as you desire. Move textbox 3 up to the left, below textbox 2, and make the changes there too. I have made textbox 2 font bold, reduced textbox 3 font to 8pt and gray rather than black.

When the report is previewed now it looks like this:

I hope you will agree this makes the report a lot easier to read but also retains all of the important information for the reader to review and react to the data it contains.

All being well you will be able to use this technique to bring similar changes to your reports and adapt it to many other applications.

References

Database file metric collection method – http://www.mssqltips.com/sqlservertip/1426/collect-sql-server-database-usage-for-free-space-and-trending/