Monday, 29 October 2007

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

kick it on DotNetKicks.com

49 comments:

Anonymous said...

sweet trick with the checkbox/wingding item. i was banging my head with this as well, and this worked like a charm

keri said...

This was SO helpful, that alternate row shading is really cool and I love the date formatting as well!

Anonymous said...

1) Date formating
You can also use the property Format in tables - e.g. "d", "D" for Date

3) Newlines
Chrw(13) & Chrw(10) works fine - also in Exports

Dimmai said...

Informative - Good job
vuf vuf

Gavin said...

Cheers Jo!

I've just used 3 of those tips tonight!

Anonymous said...

Thats great, THANKS buddy :P

Paulino said...

This was very helpful. You have saved my lots of hours and possible frustration! THANKS!

rsb said...

You are awesome!! I was braking my head to find a way to display a check box on my report, this was very helpful!, thanks

Anonymous said...

The checkbox was great! But have you tried exporting it to PDF.. There were no checks on the box,but in report viewer and excel it has.

Prithviraj Bhosale said...

Thanka buddy for that VBCRLF command!!! I was going crazy because I was not finding a way to do it !!

Anonymous said...

Too bad I didn't find your info about checkboxes before setting numerous checkboxes to IIF(field!name) = true because I couldn't get the IIF statement to recognize the actual 1 or 0 in the boolean field.

Now I need to figure out how to use the vbCrLf to put a break after x amount of characters.

evil1 said...

Hello,
I'm trying to write a report with using a matrix showing totals and subtotals. And the problem is those total rows are not that easily distinguishable since they share the same format as regular table field.

Is there any way of changing the formatting for total fields? (i.e. changing background color) I tried using expression in background color property but couldn't find a correct formula.

Suellen said...

THANK YOU - for taking the time to do this, for all those who don't but depend on great tips like this. appreciate it soooo much.

Anonymous said...

Thanks, you had two awesome tips that I was able to use!

The alternate grouping colors, and date formatting.

My report looks great now!

Cheers.
Kellie

Anonymous said...

Thanks for the Tips and Tricks..Really useful and time saving..

Sanjeev said...

Hi,

Wonderful Posting.... Very Helpful.

Regards,
Sanjeev

Alok said...

such a nice command..it helps a lot thank for all

Alok said...

such a nice command..it is helping too much .thanks a lot

jpanzeca said...

I would like to reuse the actual code of an expression in multiple matrices. For example I have a complex expression that calculates an amount in a matrix. This same calculation or amount is used in other matrices. I don't want to have to copy and paste the complex expression into other complex expressions in the other matrices but would like to be able to reference the actual expression in the other matrices. Do you know how I could accomplish this?

jpanzeca said...

Here is an example of how to change the background color in a matrix depending upon the scope...

=iif(InScope("IntCred_RowGroup1"),
iif(InScope("IntCred_RowGroup2"),
"White",
"Lavender"),
"MistyRose"
)

Jo said...

@jpanzeca - yes you can reuse code in SSRS: you can write methods that get embedded in your report and can be used from anywhere within it. In SSRS 2005, you can find them in Report/Report Properties on the Code tab.

Arunkumar S P said...

Really an excellent article. But checkbox using Wingdings is not supporting in PDF. So use the two image(check and uncheck) and assign any one to value of image control

lori.vinc said...

Regarding the VBCRLF issue, I have also found that if I needed a line break at the beginning of my textbox (or multiple line breaks) that I don't want stripped out during exports, I set the top padding to a larger point size. For example, if I'm using a font size of 12 pt, I'll set the top padding property to 12 pt to simulate a single line break. If I need two line breaks, I'll set the top padding property to 24 pt (and so on). This doesn't get stripped out on export. You can apply the same method to the bottom padding property if you need line breaks at the end of your textbox.

Anonymous said...

I have a column containing dates. The date is on the format of string as dd/mm/yyyy.
The examples are
01/01/2005
04/05/2009
24/12/2008
01/03/2002

Once, i use the interactive sorting property for this column.
the result of sorting is as not desirable because it sorts the date like
01/01/2005
01/03/2002
04/05/2009
24/12/2008

I want to sort it like
01/03/2002
01/01/2005
24/12/2008
04/05/2009

HHElp plz

Jo said...

@Anonymous Looks like your column contains strings rather than dates - try casting your data as a date either in the sql or in the report.

Mike250 said...

@evil1,

You should see a tiny green triangle in the matrix totals box. Try right clicking that to format the totals rows separate from the detail rows.

Dom Horton said...

Useful tips thanks,

I've got some similar ones (and additionals) on my blog if required:

http://sqlreportingservicescrystalreports.blogspot.com/

thanks,

Dom

Jag Reehal said...

Love the post title!

Tod Nicholas Thomson said...

> Since the RowNumber resets at every group all my rows stayed white.

It doesn't work like that for me... It seems to continue the row numbers on over each group...

i.e. in my case if i have one row in each group it alternates row shading...

However I would like it to restart the row numbering in each group.

Am I doing something wrong?

Any help would be much appreciated.

Cheers!

Tod.

Jo said...

@Tod - without seeing your code, only thing I can immediately suggest is to check the scope parameter you're passing into the RowNumber method. Passing in the name of your group (instead of "Nothing") might sort it out.

Tod Nicholas Thomson said...

IGNORE THAT ;0

http://msdn.microsoft.com/en-us/library/ms159225.aspx

Pass in the "group by context" and you get the row counts restarting for each new group!

How I missed that the first time I "hit up google" I have no idea...

MOKO said...

Nice ... Nice tricks!

Anonymous said...

just what I need. thanks.

Troy said...

I was reading this post concerning addin checkboxes to SSRS. When looking at this in HTML view it is GREAT, however it doesn't export to PDF well. Have you encountered this or have a workaround?

Anonymous said...

I have a query as follows, but I want to add to it to group by Date Ranges such as (0-30, 31-60, 61-90, 90-120, >120) How would I do that ?

SELECT ID, Created, datediff (day, Created, getdate()) [Age], Partner_Priority, LOB, Service_Manager, Service, SubService, Service_Task_Name, Short_Description, Status_
FROM v_MOSS_SDS_ServiceRequest
Where Status_ <> 'Completed'
AND
Status_ <> 'Cancelled'

Paul said...

I have a query as follows, but I want to add to it to group by Date Ranges such as (0-30, 31-60, 61-90, 90-120, >120) How would I do that ?

SELECT ID, Created, datediff (day, Created, getdate()) [Age], Partner_Priority, LOB, Service_Manager, Service, SubService, Service_Task_Name, Short_Description, Status_
FROM v_MOSS_SDS_ServiceRequest
Where Status_ <> 'Completed'
AND
Status_ <> 'Cancelled'

Anonymous said...

very nice on the alt row shading method. I needed a quick look up for the syntax to shade for every group of Cycledates and it worked perfect. Great post Thanks!!!

Anonymous said...

Change font name to wingdings2
and use this code
=iif(Fields!BookingMade.Value = "1",chr(82),chr(84))

Kev And Charlotte said...

It's funny, I've done a number of googles over the last 4 months that wound me right on this page, this tip, and it saved the day each time.

Evandro Junqueira Ramos said...

I used the line break in my report. It works!
Thanks!

Anonymous said...

found your tips useful.

Dhaval Amin said...

Still Error on NULL date field in SSRS. Try every combibnation suggested.

Anonymous said...

Thanks, Really Useful

IrishAdo said...

I have a reprot with some columns two of which are a description and a description order field

The desc_order column is hidden on the report and I want the sort of the description column to sort by desc_order?

Each time I put into the interactive sort that it should sort by the desc_order I get the sort icons in the cells of that column.

Any Ideas?

IrishAdo

Anonymous said...

Hi All,
If we use Chrw(13) & Chrw(10) OR VBCRLF OR CHR(13) & CHR(10) in a textbox, we are getting the output like below:
For Ex: Assume we have '="A" & Chrw(13) & Chrw(10) & "B"'
Output:
A
B

When we export the report to pdf or word, the second line gets one space indendation which is not required.
Expected output:
A
B

Please advice.
thx

Ravi Gupta said...

checkbox tip was cool. liked it.

MystiN said...

Thank you for this, I'll bookmark this page! It looks to me like the VBCRLF is more than just the end of a textbox, maybe also text runs; that would explain why when exporting to Word it would see that new-line but not exporting to pdf. What I had was actually something like (in the same text box):
TEXT
<>
<>TEXT
Where the first expression ended with an extra VBCRLF if the text was going to display at all. I think the extra newline I was depending on being there between the two expressions was getting lost in the pdf export. So I added the implied new-line to the first expression and put them on the same line, so as to control the lines to be consistent between exporting to Word and PDF. It seems to work, I hope that helps others who are fighting this.

Anonymous said...

that Alternating row colours made my report load like 20 times slower than it loaded without it. I dont know why but that is really not worth it when u have a big report.

Anonymous said...

Really helpful notes - thank you.