Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Devin Knight

Devin is a BI consultant at Pragmatic Works Consulting. Previously, he has tech edited the book Professional Microsoft SQL Server 2008 Integration Services and was an author in the book Knight's 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services. Devin has spoken at past conferences like PASS and at several SQL Saturday events. He is a contributing member to the Business Intelligence Special Interest Group (SIG) for PASS as a leader in the SSIS Focus Group. Making his home in Jacksonville, FL, Devin is a participating member of the local users’ group (JSSUG).

SSRS – Dealing with truncating spaces

I apologize ahead of time on this post today because it will require lots of pre explanation and screenshots to set the stage for what I want to show you! Often when building a report with multiple row groupings I like to change the default way SSRS places the fields on the report. Default multiple row groups:

image

While this behavior is fine it can lead to a lot of useless white space when browsing the report.

image.

So what I like to do is still have the three groups by eliminate all that dead space.

image

Of course this presents a new problem. When you view this report now the white space is eliminated but, it appears that all row groups are at the same level, which would confuse an end user.

image

So I thought to do the obvious I’ll make each group and expression with some leading spaces to indent the second and third groups. I could have also used the SPACE function to simulate the problem I’m demonstrating as well.

="   "+Fields!Subcategory.Value

This works beautifully from the BIDS preview screen but once deployed to the Report Server those spaces are then truncated. This happens because the compresses the leading spaces for you automatically.

The workaround is to use a non truncating space character. By using the ASCII key code for 0160 you will get the spaced effect you’re looking for. So by holding Alt + 0160 on your number pad non truncating spaces will be added to your report expression. Do this the desired number of times on each group and your report will look great! By the way number pad was intentionally bolded here because if you’re working on your laptop make sure to turn on the number lock and use the appropriate number pad keys for this to work. The final report looks like this!

image

Comments

Posted by Ammer on 25 September 2011

Why not just use the padding properties for a given cell/ojbject, when you want to 'indent' the displayed name/value?  Much easier to control and negates the need to add expressions to those fields being output :-)

Posted by fizzled on 26 September 2011

As Ammer said, this is best done with the Level() function in the padding property. Refer to:

technet.microsoft.com/.../dd255243.aspx

Posted by knight_devin@hotmail.com on 27 October 2011

Yep Ammer you're right.  Really i was just coming up with an excuse to show the Alt + 0160.  This was the first way that came to mind!

Leave a Comment

Please register or log in to leave a comment.