SQL 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.
- 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
- 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.
- 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.
- 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.
- Set the properties of the Body. Again, a width of 2.8125 and a height of 1.0625 was used.
- 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
- At this point, Design looks like this:
- Now, go to Preview. You will only see one column of textboxes. This is by design.
- 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 Borland is a Premier Field Engineer - SQL Server for Microsoft, where she is a trusted technical adviser, deep technical support, and teacher for her customers. Her experience as a DBA, consultant, and 5-time Data Platform MVP allow her to help the business and the IT teams reach their goals. She has worked with SQL Server as a developer, report writer, DBA, and consultant. Her favorite topics are administration, automation, and performance. She frequently presents at user groups, SQL Saturdays, and other community events. She is also an avid runner and chef.
