Creating Mailing Labels in SQL Server Reporting Services

Most word processing applications (Word, WordPerfect, and so on) provide the capability to create a “mail merge” from which to generate mailing labels in different formats and layouts. Mailing labels are an automated way to generate the address labels for a large number of envelopes or parcels that need to be mailed.

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.

This recipe shows you how to leverage Reporting Services’ multi-column layout features to create basic mailing labels, while explaining certain limitations in the rendering engine.

Product Versions

• All versions (examples provided in Reporting Services 2008)
  What You’ll Need

• AdventureWorksDW2008 database (or your own database and query that provides name and address data to the report)
• The exact template size for the labels you will use when printing (including all margins and column widths)
• A PDF reader or Image viewer (Windows provides an image viewer that supports TIFF, JPG, GIF, and PNG)

Designing the Report

The final outcome of this recipe should be a multi-column report “perfectly” sized to fit the print layout of a mailing label template, as shown in Figure P6-7.

Figure 6-7

For the purposes of this recipe, you will utilize the Avery 5160 label template, which contains the following dimensions:

• Length: 2.5935”
• Height: 1.0000”
• Margins: Top 0.5”, Bottom 0.5”, Left 0.21975”, Right 0.21975”
• Horizontal Spacing (gutter): 0.14000”
• Vertical Spacing (gutter): 0”
• 30 labels per sheet of letter size (8.5” x 11”) paper

1. Begin by creating a new report in Report Builder, and removing all default items and the page footer from the report. You need to have a blank design surface - the report dimensions need to be very exact in order to match the labels when printing.

2. Add a new data source to the report and set its connection string to the SQL Server where the AdventureWorksDW2008 database is stored. If you are using your own database, simply choose the server and database for your own data, so you can provide your own query in the next step.

3. Add a new dataset for the data source created in the previous step. Set the query type to Text, and type the following SQL query in the command text window:

SELECT

       c.Title, c.FirstName, c.MiddleName, c.LastName

       , c.AddressLine1, c.AddressLine2

       , g.City, g.StateProvinceCode, g.PostalCode

       , g.EnglishCountryRegionName

FROM

       dbo.DimCustomer c

LEFT OUTER JOIN

       dbo.DimGeography g

       ON g.GeographyKey = c.GeographyKey

WHERE

       g.EnglishCountryRegionName = @Country

ORDER BY

       g.StateProvinceCode

       , g.City

       , c.LastName

Notice that this includes a parameter for the Country field—this will allow you to filter down the dataset to a specific country. Also, you order by State/Province, then by City, and finally, by Last Name. That seems like a reasonable way to order your labels and keep your mail person happy when processing a large case of envelopes or parcels. You should have a blank report with a dataset as shown in Figure P6-8.

Figure P6-8

4. The label template will contain three columns on a letter-size sheet of paper, so you need to set up the report size and layout for multiple columns:

a. In the Report Properties, set the Orientation to Portrait, and Paper Size to Letter (8.5in x 11in).

b. Set the Left and Right Margins to 0.21975in

c. Set the Top and Bottom Margins to 0.5in

Click the OK button to save changes. The report properties should look as shown in Figure P6-9.

Figure P6-9

5. Next, you need to set up multiple columns. The Columns and ColumnSpacing properties of the Report are not exposed via the Report Properties dialog shown in Figure P6-9. Instead, you must edit them in the Properties page for the report (if you don’t see it in Report Builder, choose the View menu from the ribbon and check the Properties box to display it).

Expand the Columns node from the Properties page, and make the following edits (see Figure P6-10):

a. Change the Columns property to 3

b. Change the ColumnSpacing property to 0.14in. This is the size of our label template’s Horizontal Spacing gutter - the spacing between columns on the page.

Figure P6-10

Notice that the report body has been “duplicated” by the number of columns specified in the Columns property, even though you only get to work on the leftmost body template (the other ones are simply placeholders to show the designer that multiple columns will be rendered at runtime).

Since you already specified the dimensions for your label template, you might be wondering why the report is so wide, making you scroll to the right to see the multiple columns. There still are a few dimensions that you must set for the body of the report.

It’s important to understand how report page sizes, body sizes, margins and column spacing relate to each other in the report. Figure P6-11 illustrates how these dimensions fit together.

Figure 6-11

From the diagram, you can then infer that the labels themselves will be the body, while the sheet of paper will be the report page. With that in mind you will set the body dimensions according to the label size specified previously.

6. Click on the Body element, and change the following properties in the Properties window:

a. Expand the Size node and set the Width to 2.5935in

b. Set the Height to 1in

Your report body should now look like the diagram in Figure P6-12.

Figure P6-12

7. Finally, you add a data region to the body of the report, attach it to your dataset and drag data fields in for the mailing data.

In Reporting Services 2008, you can use either a List or a Table data region (both use the underlying tablix). However, if you are using an earlier version, I have found the table layout to yield more consistent results and provide a better design surface to control your formatting. I’ll let you, as the report developer, decide what best fits your needs according to the requirements of your mailing label design.

I’ll simply use a table with a single column and detail row, and rely on the Reporting Services 2008 rich text features of the textbox that allow you to drag and drop multiple dataset fields onto the table cell. Make sure your data region (table or list) stretches to fill 100% of the body size, without expanding it. In other words, the width and height of the data region should match that of the body. The easiest way to do this is by drawing the data region on the design surface instead of dragging it from the menu. After adding the data region to the report, double-check that your report body was not modified by the data region.

If you are using a previous version of Reporting Services, you will either use string concatenation expressions (not recommended) or use a rectangle in the cell to make the cell a free-form container for your textboxes - then you can use multiple textboxes for the dataset field, each positioned absolutely within the cell.

Here’s a trick to ensure that your labels are positioned correctly within the cell: select the cell textbox and set its vertical alignment to “Middle”. For some reason, Reporting Services will duplicate the data cell otherwise.

Assuming your mailing labels will require First and Last Name, Address 1 and 2, City, State, Postal Code, and Country name to be displayed, the table cell layout should look similar to Figure P6-13.

Figure P6-13

While previewing the report, keep in mind that the report viewer used in the preview of the report designer uses the Graphics Device Interface (GDI) to render the report to the screen, and because we are using multi-columns, a feature only supported in the print-oriented renderers for Reporting Services, you will only get to see the expected outcome if you click the “Print Layout” button in the preview window. This leverages the print-preview renderer and not the regular preview renderer. Also, because of this limitation, a multi-column report layout is only supported in print-oriented formats: PDF, TIFF (Image), Print, and Print Preview. You cannot export and save your report to Word, so your best option is to use PDF.

The final outcome of the report in Print Layout preview is shown in Figure P6-14 (I added light gray borders to my textbox, so you can see the size of the labels).

Figure P6-14

Final Thoughts

Reporting Services provides developers with several features to help create insightful analytical reports, as well as print-ready reports. With its multicolumn capabilities, we are able to drastically change the layout of reports that target a print layout such as PDF.

This recipe showed you how to leverage the multicolumn feature to create mailing labels akin to those found in Microsoft Word’s mail merge feature. The compelling story, however, lies within the integration and automation possibilities. As most developers would agree, Office automation is rather complex and can be a bit frustrating at times. However, with Reporting Services’ web services API, its extensibility, and rich subscription model, the task of automating the creation of mailing labels becomes much simpler.

Credits and Related References

Mailing label dimensions provided by Worldlabel.Com, Inc. (www.worldlabel.com).