none
Customer name and address too spaced apart

    Question

  • I am using report builder 2.0. The parameter is set so that a customer can be selected and it will display their address block and some other information. This works, but the name, address, city, state and zip are dramatically spaced apart. 

    It was recommended that I put it all in a rectangle. I tried putting it all within an inserted rectangle. No luck. Still very spaced apart.  

    I tried putting it all in a text box too:

    But that resulted in this error. 

    Please help! It looks really unprofessional. 


    ipainter

    Thursday, June 27, 2013 6:55 PM

Answers

  • I figured it out. Here is the answer. 

    First(Fields!Customer_Name.Value,"DataSet1") & vbCrLf & First(Fields!Address_1.Value,"DataSet1") & vbCrLf & TRIM(First(Fields!City.Value,"DataSet1")) & "," & TRIM(First(Fields!State.Value,"DataSet1")) & " " & TRIM(First(Fields!Zip.Value,"DataSet1"))


    ipainter

    • Marked as answer by hizic Thursday, July 11, 2013 8:03 PM
    Thursday, July 11, 2013 8:03 PM

All replies

  • Hi Ipanter,

    It seems that you create multiple datasets in your report, in this case, you should specify scope for the field. The textbox can only display the first record in the dataset, if you want to dispaly the first record, you can chang the expression like:
    =First(Fields!Customer_Name.Value,"DataSet") & First(Fields!Address_1.Value,"DataSet") & First(Fields!City.Value,"DataSet") & First(Fields!State.Value,"DataSet") & First(Fields!Zip.Value,"DataSet")

    If you want to dispaly all the records, you can use a tablix to dispaly it.

    If you have any questions, please feel free to ask.

    Regards,


    Charlie Liao
    TechNet Community Support

    Friday, June 28, 2013 9:22 AM
  • Thank you Charlie. That didn't fix it. The result was different but with even more spacing. 

    We need it to display their address block so that it can be printed and mailed. Thus, we need the address block to fit in the window of the envelope. 

    Your suggestion looks like this (the report is cut off to protect our customers information):

    We need it to look like this (screenshot from excel, after manipulation)


    ipainter

    Wednesday, July 03, 2013 7:00 PM
  • Use:

    =TRIM(Fields!City.Value,"DataSet") & ", " & TRIM(Fields!State.Value,"DataSet") & " " & TRIM(Fields!Zip.Value,"DataSet")


    Please see: http://technet.microsoft.com/en-us/library/ms157328.aspx
    • Edited by Tom Phillips Wednesday, July 03, 2013 8:26 PM Added link
    Wednesday, July 03, 2013 8:25 PM
  • Thank you Tom.

    I looked at the link and I think this is what I need, in combination with the TRIM expression you provided. 

    However, whenever I put in my expression it gives me back the following error

    Expression:

    =Fields!Customer_Name.Value,"DataSet1" & vbCrLf & Fields!Address_1,"DataSet1".Value & TRIM(Fields!City.Value,"DataSet1") & ", " & TRIM(Fields!State.Value,"DataSet1") & " " & TRIM(Fields!Zip.Value,"DataSet1")

    Error:

    That is the name of the field and that is the name of the dataset. I'm confused. 


    ipainter

    Friday, July 05, 2013 7:48 PM
  • The issue may be because of Leading and/or Trailing spaces in the Fields coming from database. Use LTRIM and/or RTRIM for Fields
    Friday, July 05, 2013 9:11 PM
  • Where is your textbox located?
    Sunday, July 07, 2013 11:09 PM
  • If you will look at the first post, it has a screenshot of where the textbox is located. It is at the top of my report. 

    ipainter

    Monday, July 08, 2013 3:10 PM
  • I figured it out. Here is the answer. 

    First(Fields!Customer_Name.Value,"DataSet1") & vbCrLf & First(Fields!Address_1.Value,"DataSet1") & vbCrLf & TRIM(First(Fields!City.Value,"DataSet1")) & "," & TRIM(First(Fields!State.Value,"DataSet1")) & " " & TRIM(First(Fields!Zip.Value,"DataSet1"))


    ipainter

    • Marked as answer by hizic Thursday, July 11, 2013 8:03 PM
    Thursday, July 11, 2013 8:03 PM