XML FeedsSQL Server Reporting Services 2008 R2
Creating mailing labels is a common business need. From sending marketing postcards to prospects, to the annual Christmas cards for customers, labels are frequently used.
Are you currently running a report to get your customers mailing information, then using Mail Merge in Microsoft Word to create the labels? Perhaps you are simply exporting the data out of the database. If so, there is an easier way!
Step-by-Step
The basic steps are to: create a report with multiple columns, add a list, add a textbox, write an expression, and let Reporting Services do the hard work!
I'm using Avery 5160 labels as an example. Depending on the labels you have, you may need adjust your columns, width, height and padding.
1. Create a new, blank report. I'm using the AdventureWorks database. My query is:
- SELECT FirstName, LastName, AddressLine1, AddressLine2, City, StateProvinceName, PostalCode
- FROM Sales.vIndividualCustomer
2. Go to the Report properties.
A. Expand Columns, and add the appropriate number of columns. In this example, this will be three.
B. You'll need to add in the spacing between the labels, too, so they line up correctly when printed. This may be trial and error. In this example, it is .03125 inches.
C. Set your Margins. In this example, they are Left 0, Right 0, Top 0.1875, and Bottom 0.
3. Drag a List onto the report. Adjust the Size to be the Width and Height of your labels, plus a little extra for the strips that go between them. Avery 5160 have a width of 2.8125 and a Height of 1.0625.
4. Drag a Textbox onto the List.
A. Set the Width and Height properties of the textbox. Again, a width of 2.8125 and a height of 1.0625.
B. You'll also need to adjust the Padding. Remember, this box is slightly larger than the actual labels because of the margins and area between the labels. I set Padding to Left - 15pt, Right - 15 pt, Top - 0 pt, Bottom - 0 pt.
5. Set the properties of the Body. Again, a width of 2.8125 and a height of 1.0625 was used.
6. Go to your textbox and build an expression for your labels. My expression is:
=Fields!FirstName.Value & " " & Fields!LastName.Value & vbcrlf
& Fields!AddressLine1.Value & " " & IIF(NOT(Fields!AddressLine2.Value) = "Nothing", vbcrlf & Fields!AddressLine2.Value, vbcrlf)
& Fields!City.Value & ", " & Fields!StateProvinceName.Value & " " & Fields!PostalCode.Value
7. At this point, Design looks like this:
8. Now, go to Preview. You will only see one column of textboxes. This is by design.
9. Click the Print Layout button and you will see how the labels will print. There are three columns, with the labels nicely laid out! These can now be printed, or exported and printed.
Put It In The Mail!
This process is straightforward and can save you time, especially if you are creating labels frequently. Your report can easily be customized with parameters, also, to make it more flexible.
Jes Schultz Borland is a Consultant with Brent Ozar PLF, and a Microsoft SQL Server MVP. She holds an AAS - Programmer/Analyst degree, and has worked with SQL Server since 2007, focusing on Reporting Services and day-to-day administration. She is an active member of PASS, Director of Communication for MADPASS, founder of Tech on Tap, and a LessThanDot.com blogger. She's a frequent presenter at user groups, SQL Saturdays, and other community events. She is also an avid runner and chef. 
Nice post Jess. I've done something similar in the past although I used separate text boxes for each line of the address. I like yours better and will need to go change mine to use a single text box and an expression. :-D 
This set of instructions to create mailing labels was so perfect and on time for me, since I needed to get it done quickly. Your instructions worked perfectly, although I had to add padding to the top of the text box in order for the labels to print correctly on one of the user's printers (the top of the first line was getting chopped off just a little). Thanks!!! 
I'm using SSRS 2005 and I'm not able to get my data to line up. The labels I'm using are Avery 5161 (2 cols) and the labels are 4 X 1 inch. These labels have a 1/2 in top margin. 
Unfortunately, I've only tried this with the Avery 5160. It was a lot of trial and error time for me as well. The best thing I can suggest is to keep adjusting column spacing the textbox padding until it all lines up. 
Thank you very much !!! 
Cheers mate, great stuff. 
Fantastic solution. Thanks a lot this will be really helpful for some staff in our Housing department. I do have one question. When I run the report from the Reporting services website site only 1 column in laid out in the report as in BIDS. In BIDS when you print preview as you suggested it is fine. However, When you print from the website it prints as displayed in 1 column. A work around is to export the report as PDF then it lays out correctly. Is there a solution for printing directly from the SSRS website.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.