Reporting Services Tips, Tricks and Funkiness

I've noticed over the last few months that people have been coming to me with questions about Sql Server Reporting Services... and I've been able to answer them. I had an issue with a Report Server myself a while ago and, when I asked who the Reporting Services guru was so I could get some help, was told "You are."

Anyway, the upshot of this is that I've realised that, even though I don't feel like it, I've picked up a fair amount of knowledge on the ins and outs of SSRS. Since I frequently try to Google reporting services issues I'm having and am unable to find answers easily I figured that putting some of the bits and pieces I've figured out into a blog post might help somebody :)

Here's a list of ideas that came to mind - I've either been asked how to do it by a colleague or I've had a client who required it and found it difficult to find a solution.

1 - Date formatting
If you have a string parameter coming in to your report (from a stored procedure for example) and need it displayed nicely, try using the expression:
=CDate(Fields!MeetingDate.Value).ToString("dd/MM/yyyy")

You will get minimum date values being displayed if the date is null for particular search criteria. If this is a problem for you, try using:
=IIF(IsDate(Fields!MeetingDate.Value),CDate(Fields!MeetingDate.Value).ToString("dd/MM/yyyy"),"")

Obviously in both these cases you can insert whatever format you'd like in place of "dd/MM/yyyy" :)

2 - IIF statement gotcha
Beware of using the IIF statement above - it works beautifully if the string is either null or a valid date but if the string is something like "tomorrow" (or even an empty string) then you will see "#Error" in your report. This is because IIF statements execute both paths when evaluating (they're not lazy enough!) so Reporting Services tries quite hard to convert "tomorrow" to a date and falls over when it can't. If you need to get around this try writing your own function (Report/Report Properties/Code) using more standard .NET IF statements and call it from your expression. Otherwise, just be aware and be careful when using IIF.

3 - Newlines in text boxes
If you're using an expression to build up a paragraph or two of text in a text box and need a new line, use this:
VBCRLF (Visual Basic Carriage Return Line Feed for those who're interested!)

E.g. ="This is line 1" + VBCRLF + "This isn't" will produce:
This is line 1
This isn't

Gotcha: If VBCRLF is the first or last thing in your text box, Reporting Services will ignore it (I have no idea why!). Make sure you put in one extra VBCRLF at the beginning or end if you're using them there.

4 - Checkboxes
I would have thought it would be fairly common to want to display a check box on a report which is checked based on a value in the dataset but it would appear that it's not so common as there is no control provided to handle this. If your client, like mine, really wants a checkbox displayed on a report, you'll need to use wingdings. Yes, you heard right, add a small text box to your report, set the font to wingdings and use something like the following expression:
=iif(Fields!BookingMade.Value = "1",chr(0254),"o")

Feel free to play around with different wingdings characters, but I found these two gave me a nice looking checkbox that stayed the same size whether it was checked or not!

5 - Alternating row colours
Large tables look better and are easier to read if you shade alternate rows. This is easily accomplished by adding the following to the BackgroundColour expression of your detail row (substituting your desired colours of course):
=iif(RowNumber(Nothing) Mod 2, "#eefafd", "White")

I had a situation recently where my client needed alternating row colours but, for a variety of reasons, the rows displayed on the report were all group header rows. Since the RowNumber resets at every group all my rows stayed white. Using the following expression gave me header rows with alternating colours (the IDNumber is what I was grouping on):
=iif(RunningValue(Fields!IDNumber.Value, CountDistinct, nothing) mod 2,"#eefafd","White")

Enough from me for today I think. For more on Reporting Services check out these websites that I've found helpful:
Rules to better SQL Reporting Services
Chris Hays' Reporting Services Sleazy Hacks

Are there any more useful tips and tricks out there? Or improvements to mine? Drop a comment :)