Display only the first 10 characters

  • One field that I need to display in a report has the potential of being kind of long (over 100 characters). I'm using SQL Server 2008, and in this case, I'm using a table for the display. Most users will want to export the report to Excel.

    I'd like to display only the first few characters of that one field, but I don't want to truncate it in my SQL query. I want users to be able to see the entire contents if they choose to. I have Googled this and have had no luck. I have a hard time thinking I'm the first person to ask this.

  • Are you using sql reporting services for this? If so, use an expression to truncate the field on normal display and then create a toggle to show the remainder....Need to know what you are using to display the data...

  • Yes, I'm using Reporting Services.

    I understand I have to to create something that will truncate for display, and then show the whole thing, as you suggest. I guess what I'm asking is, how do i do it? I have looked at Books Online and done a lot of Googling. I guess I don't know the proper search terms.

  • You can use the reporting services functions such as LEFT & SUBSTRING to display few characters.


  • Something like this in your expression for the text box:


    You would have to add the boolean parameter to check against. This gives the user the option to display the long text

  • Bob:

    Thanks. I am not clear on what you said here regarding the boolean parameter. I don't see where to add one in the textbox properties box. I also see a red underline when I have the word "displayfulltextparm", which means I have to declare that somewhere. I got that term from your sample code. But I am not sure where or how to declare it.

  • Bob was saying that you need a add a Boolean report parameter through "report parameters" option and this will let the users provide an to display full text or the truncated text in the report.


  • That's it Ramesh...Thanks...

    BIDS menu, "Report", "Report Parameters"

  • Ok, I added a parameter, set it to Boolean, put in a value of Fields!Agencies.Value (as the name of the field I am trying to affect is "Agencies") and I am getting all sorts of errors. I am just not getting what you guys are saying. Oh well. It was a nice thought. Thanks.

  • the boolean parameter is just a flag for the IIF statement, nothing more. The only thing referencing it is the IIF statement in the text box...

  • I tell you, after working with SSRS for a few months, I thought I had a handle on it, but I am not getting what anyone is saying, and I'm feeling dumb as a rock right now. I think I will bow out. thanks for your time.

  • Oh but you are so close! Let us know if you want to jump back on it. I threw the boolean parameter in for free...It's just one way to do it. For simplification's sake, take that piece out and just use the left function then when you are ready look into tackling the problem of displaying the whole thing....And don't feel dumb, everyone starts somewhere. Frustration is a sign you are learning....

  • This is a bit late but you can use this in your report expression:

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply