 |
|
Handling truncated leading spaces in Reporting Services
|
By: Rob Fisch | Read Comments (8) | Print Rob has worked with SQL Server since version 6.5 as a dba, developer, report writer and data warehouse designer. Related Tips: More |
|
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 Update: 7/16/2007
| Share: |  |
Share | Tweet |
|
 |
 |
Free SQL Server Learning |
Comments and Feedback:
| Wednesday, September 03, 2008 - 2:44:58 PM - Amarjot | Read The Tip |
|
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. |
|
| Wednesday, January 27, 2010 - 3:41:44 PM - ososa174 | Read The Tip |
|
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! |
|
| Monday, February 15, 2010 - 9:11:16 AM - rduclos | Read The Tip |
|
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 |
|
| Tuesday, March 16, 2010 - 1:30:19 PM - stevegnostic | Read The Tip |
|
The tip worked as stated.
|
|
| Friday, May 28, 2010 - 11:55:09 AM - NoMercy | Read The Tip |
|
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. |
|
| Friday, May 28, 2010 - 12:34:08 PM - ososa174 | Read The Tip |
|
Hello there Have you tried to copy the text? remember about the ALT key...
|
|
| Friday, May 28, 2010 - 3:26:33 PM - NoMercy | Read The Tip |
|
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* |
|
| Thursday, April 19, 2012 - 12:02:26 PM - Beng | Read The Tip |
|
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. |
|
|
privacy | disclaimer | copyright | advertise | about authors | contribute | feedback | giveaways | user groups Some names and products listed are the registered trademarks of their respective owners. Edgewood Solutions LLC | MSSharePointTips.com | MSSQLTips.com |