Reporting Services provides a few features that allow you to create mailing labels in different formats – the only thing you need to know are the exact dimensions of the label template you are targeting when printing. A common mailing label format is to use multiple columns (newspaper layout) in order to maximize the number of labels printed.
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.
Here is 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!
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:
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.