Click here to monitor SSC

Jonathan has been working with SQL Server since 1999. He enjoys performance tuning, development and using SQL Server to provide appropriate business solutions. He is the founder and leader of the PASS SQL South West user group http://www.sqlsouthwest.co.uk , is a moderator at SQL Q + A forum ask.sqlservercentral.com and is on twitter at @fatherjack. He has spoken at SQLBits and SQL in the City, SQL Saturdays and local user groups across the UK and Europe.

SSRS Table Cells with Mixed Formatting

Published 28 November 2013 12:47 pm

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?

SSRSTableCellFormatting08

 

 

 

 

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).

image001

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.

image003

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

image005

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.

image007

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.

image009

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.

image011

When the report is previewed now it looks like this:

image013

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/

3 Responses to “SSRS Table Cells with Mixed Formatting”

  1. dataminor says:

    Interesting workaround, but why complicate things? You can achieve the same thing simply by using placeholders.

  2. SAinCA says:

    I use Placeholders now, but as someone who doesn’t normally use HTML, abstracting the formatting using this approach is much easier and produces the same results. The only downside to this vs. Placeholders is that conditional formatting, i.e., show/no-show or severity-coloring, is in Properties rather than in a nicely formatted expression. Both work, just depends on how much HTML one wants to carry in one’s head and for me that’s very, very little.

    Thanks for the tip, Jonathan.

  3. Fatherjack says:

    Hi dataminor and SAinCA, I must admit I am not a regular user of SSRS and had not used placeholders until after I published this blog and then got a comment on Twitter about them. They certainly offer a simple way to format text in a very specific way and more flexibly than the method I describe above. Having both methods as options will let you choose which is most appropriate at design time to let you get the most from your report(s).

    Thanks to you both for taking the time to add your comments.

Leave a Reply