Tuesday, December 20, 2011

SSRS Reporting Services Tips & Tricks

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:

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:

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

For More Info 

For More Info 2 

No comments:

Post a Comment