Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Handling truncated leading spaces in Reporting Services


By:   |   Last Updated: 2007-07-16   |   Comments (12)   |   Related Tips: > Reporting Services Formatting

Problem

When attempting to display hierarchical data in Reporting Services by using additional spaces, the web browser truncates the additional leading spaces and all data becomes left justified with all of the indentation removed.  This is because, like or not, the web browser's job is to compress multiple sequence space characters into one. This may be desired in normal HTML coding, but not so with reporting data. So how can you get Reporting Services to not remove the leading spaces, so you can display your hierarchal tree?

Solution

To take this a step further let's take a look at an example where you would like to display your data as a hierarchy.

For instance, you want your data to look like this:

Rank
Captain
     Lieutenant
          Sergeant
               Corporal
                    Private

...but when the report is run, Reporting Services displays the data like this:

Rank
Captain
Lieutenant
Sergeant
Corporal
Private

As mentioned above, this is because the web browser's job is to compress multiple sequence space characters into one and therefore all of the leading spaces are removed.

To handle this you can embed a non truncating space character by using an ASCII key combination code. To embed this ASCII key code you would hold the ALT key, type 0160 and then let go of the ALT key.

So to do this for your report, in the Reporting Services table, right click on the textbox with the problem, select (fx) Expression. Then in the Edit Expression editor use the REPLACE function to embed the non-truncating space with the normal one as follows:

=REPLACE(Fields!Rank.Value," "," ") -- (The first value between the double quotes is a space and the second value between the double quotes is the ALT-0160 combination code.)

After you have made this function change and redisplay the report your hierarchy should be displayed properly like the following:

Rank
Captain
     Lieutenant
          Sergeant
               Corporal
                    Private

Next Steps
  • Little formatting tricks like this could be a pain to resolve, but as you see here is simple way to get your report to display properly.
  • Take a look at the other Reporting Services tips and stay tuned for more tips


Last Updated: 2007-07-16


get scripts

next tip button



About the author
MSSQLTips author Rob Fisch Rob Fisch has worked with SQL Server since version 6.5 as a dba, developer, report writer and data warehouse designer.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, June 05, 2017 - 3:11:13 PM - Joe Back To Top

 Thanks! I had a report working fine but embedded spaces on the fly (i.e. built the hierarchy in an exprression). When I deployed to server, the html robbed me. lol

I had used ascii chars before but it had been years. I will post it here in case it helps someone else. My part may look a tad hokey, but here it is. It works in the html, pdf, word, and excel. Spaces are preserved.

=replace(iif(Fields!BomLevel.Value=2,"    ","") &

iif(Fields!BomLevel.Value=3,"        ","") & 

iif(Fields!BomLevel.Value=4,"            ","") & 

iif(Fields!BomLevel.Value=5,"                ","") & fields!ItemId.value," ","" & chr(160) & "")

This represents an indented bill of materials where the level is checked and the item number is indented 4 spaces per level.

Thanks again,

Joe

 


Friday, August 15, 2014 - 9:53:57 AM - Denilson Grupp Back To Top

My friend, use the  chr(160) that's the hard space on (ASCII)

 =chr(160) & chr(160) & chr(160) & "Field"

 

 

 

http://ashwaniashwin.wordpress.com/2013/04/03/whitespace-characters-missing-in-ssrs-reports-while-viewing-it-in-ie-multiple-consecutive-whitespaces-collapsed-into-single-space/


Monday, February 10, 2014 - 5:58:46 AM - Pedro Oliveira Back To Top

What worked for me was to replace the space for the unicode char: '202F', when preparing the dataset.

For example in c# StringLib.ToString("    Test Leading Space").Replace("  ", string.Concat('\u202F','\u202F')); 

In the example I'm replacing 2 empty spaces with 2 '202F' chars, because '202F' char won't cause a break, and this can be usefull when can grow is enabled for the textbox.


Friday, January 17, 2014 - 1:41:04 PM - cyrille Back To Top

Worked for me, thank you !


Thursday, April 19, 2012 - 12:02:26 PM - Beng Back To Top

I could not get the REPLACE to work, however if I typed the ALT + 0160 to the left of the field, that worked.  In fact, I type ALT + 0160 3 times since I wanted 3 spaces and in both the browser and the PDF version, the 3 spaces appeared. 


Friday, May 28, 2010 - 3:26:33 PM - NoMercy Back To Top

Yup, tried it all. Held ALT and with the numlock on, typed 0160. You can tell if it worked because your cursor moves a space when it's right. I found a hotfix on the ms support site that says it will fix it, but it didn't work either. They say the issue is caused by SP2 and this hotfix is supposed to correct it, but no luck.  *sigh*


Friday, May 28, 2010 - 12:34:08 PM - ososa174 Back To Top

Hello there

 Have you tried to copy the text? remember about the ALT key...


=REPLACE(Fields!Rank.Value," "," ") -- (The first value between the double quotes is a space and the second value between the double quotes is the ALT-0160 combination code.)

The second value, instead of typing your spacebar, type and hold <ALT> key while typing 0160 on the Number Pad.

It worked for me...

Don't hesitate in tell me if it worked


Friday, May 28, 2010 - 11:55:09 AM - NoMercy Back To Top

It doesn't work for me either. Export to PDF still truncates all leading spaces. Bullets are pointless with no indentation. I've tried this, padding, and other recommendations, nothing works and nobody seems to know why it does this.


Tuesday, March 16, 2010 - 1:30:19 PM - stevegnostic Back To Top

The tip worked as stated.

The next step I was working on is still giving me trouble.


When I export to .pdf from ReportViewer I am still getting the leading spaces (now ascii characters) truncated.


Monday, February 15, 2010 - 9:11:16 AM - rduclos Back To Top
Normally what I do is use an expression on the "Left" padding to multiply by the hierarchy level.

=IIf(Level("table1_Details_Group") > 0, CStr(Level("table1_Details_Group") * 10), "2") + "pt"


Thank you,
Ryan Duclos

http://rduclos.wordpress.com

Wednesday, January 27, 2010 - 3:41:44 PM - ososa174 Back To Top
As the article said:

=REPLACE(Fields!Rank.Value," "," ") -- (The first value between the double quotes is a space and the second value between the double quotes is the ALT-0160 combination code.)

The second value, instead of typing your spacebar, type and hold <ALT> key while typing 0160 on the Number Pad.

It worked for me...

Thanks for the help!

Wednesday, September 03, 2008 - 2:44:58 PM - Amarjot Back To Top

It still does not work. I believe it is a browser issue because when you export the report to PDF the spaces are honored. I ended up using _ underscore.


Learn more about SQL Server tools