Handling truncated leading spaces in Reporting Services

By:   |   Comments (12)   |   Related: > 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.)

replace

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Monday, June 5, 2017 - 3:11:13 PM - Joe Back To Top (56793)

 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 (34148)

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 (29385)

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 (28124)

Worked for me, thank you !

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

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 (5570)

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 (5569)

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 (5568)

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 (5063)

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 (4902)
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 (4802)
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 3, 2008 - 2:44:58 PM - Amarjot Back To Top (1735)

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.














get free sql tips
agree to terms