Post reply

Forcing a Carriage Return/Line Feed within data

  • June 27, 2007 at 10:16 am

    #117687

    I have a report that is returning a column with serial numbers. If the serial number is new, I want to prepend a effective date ("Eff. 06/27/07") to the serial number and add a CRLF. I am prepending my effective date & serial number in SQL.

    The report would look something like this...

    Make Model Color Serial
    GE BT104 Red 70923873
    SMU BT105 Blue 70933329
    SSD BT107 Black Eff. 6/27/07

    70933577

    SPD BW100 Blue 70226544

    psydo code... select make, model, color, CASE WHEN StartDate <> Effectdate THEN 'Eff. ' + CONVERT(VARCHAR(20),Effectdate,1) + <CRLF CODE HERE???> + SerialNum ELSE SerialNum END AS SerialNum

    Or, is this not possible?

    Thanks,

    James

     

  • June 27, 2007 at 11:27 am

    #715597

    Look into using the CHAR() function (not the datatype). If I recall correctly it's something like CHAR(13).

  • June 27, 2007 at 11:11 pm

    #715718

    I did try that CHR(13)+CHR(10) but it just put it inside my data as a string like this.... And didn't actually do the carriage return & line feed.

    Make Model Color Serial
    GE BT104 Red 70923873
    SMU BT105 Blue 70933329
    SSD BT107 Black Eff. 6/27/07 CHR(13) CHR(10) 70933577
    SPD BW100 Blue 70226544

    psydo code... select make, model, color, CASE WHEN StartDate <> Effectdate THEN 'Eff. ' + CONVERT(VARCHAR(20),Effectdate,1) + CHR(13) + CHR(10) + SerialNum ELSE SerialNum END AS SerialNum

  • June 29, 2007 at 12:37 pm

    #716279

    You can use a VB function called, vbCrLf.

    For example:

    "text" & vbCrLf & "text"

  • July 2, 2007 at 2:15 am

    #716468

    uhm.. If you did use CHR, then I guess that the problem is there: You should use CHAR, as below:

    SELECT make, model, color, CASE WHEN StartDate <> Effectdate THEN 'Eff. ' + CONVERT(VARCHAR(20),Effectdate,1) + CHAR(13) + CHAR(10) + SerialNum ELSE SerialNum END AS SerialNum

    Bye, Daniele.

     

  • July 6, 2007 at 2:51 pm

    #717805

    Thanks Scout... This was the code that I needed.

  • July 9, 2007 at 10:17 am

    #718066

    Wouldn't this solution be better than placing your formatting in the SQL statement?

    This way reporting services handles it when it displays it, however, if you format that in the SQL statement the the results themselves would include a line return.

    If you try to import that into another program down the road it might cause "weird things" to happen.

  • July 10, 2007 at 6:05 pm

    #718457

    What about using environment.newline() in the field expression?

    Something like:  iif(<condition for line 1>, <line 1> + environment.newline()) + <line 2>

Viewing 8 posts - 1 through 7 (of 7 total)

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