Display only the first 10 characters

  • middletree

    Hall of Fame

    Points: 3316

    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.

  • Bob Griffin

    SSCertifiable

    Points: 5976

    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...

  • middletree

    Hall of Fame

    Points: 3316

    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.

  • Ramesh Saive

    SSC-Insane

    Points: 24275

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

    --Ramesh


  • Bob Griffin

    SSCertifiable

    Points: 5976

    Something like this in your expression for the text box:

    =iif(displayfulltextparm=true,"mylongteststring"),left("mylongtestingstring",10)

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

  • middletree

    Hall of Fame

    Points: 3316

    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.

  • Ramesh Saive

    SSC-Insane

    Points: 24275

    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.

    --Ramesh


  • Bob Griffin

    SSCertifiable

    Points: 5976

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

    BIDS menu, "Report", "Report Parameters"

  • middletree

    Hall of Fame

    Points: 3316

    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.

  • Bob Griffin

    SSCertifiable

    Points: 5976

    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...

  • middletree

    Hall of Fame

    Points: 3316

    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.

  • Bob Griffin

    SSCertifiable

    Points: 5976

    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....

  • ravinesh.r

    Grasshopper

    Points: 11

    This is a bit late but you can use this in your report expression:
    Left(Fields!FieldName.Value,10)

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

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