SSRS Tips for Beginners No.1: know your source data

 
So, you’ve written a query that gets the data you need, and throws it onto a table. All fine so far, except when you preview the report, you end up with something like this:

The users are unhappy- they don’t like the wasted space, particularly because when printed the report wastes a tree or two. So, what has caused this? At first glance it looks like the header rows have had to expand to fit the text, and for some reason the body rows have similarly grown in height. Before you head to stackoverflow.com to hunt down an obscure SSRS feature that would account for this, take another look at your data.

I’d be willing to bet that you’re reading fixed length strings (chars), rather than the more familiar variable length strings (varchars). The chars are filled with white space to their fixed length, and SSRS decides not to read your mind, and gives it the space it needs.

If changing this at source is not appropriate, you can cast to a varchar in your query, or trim the blank space. Similarly, you could trim in the field expression or on the report itself. Hopefully, you’ll end up with something more like this:

This seems extremely obvious in retrospect, but I can assure you it has stumped more than one experienced report writer in its time! The lesson here is to keep at least one eye on the data types you’re reporting on, because you might get wildly different results than you’re expecting.

Advertisements
This entry was posted in Solutions and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s