Creating Mailing Labels in SQL Server Reporting Services

Creating Mailing Labels in SQL Server Reporting Services

Microsoft SQL Server Reporting Services Recipes: for Designing Expert Reports  bookMost 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.

SQL Server Reporting Services mailing label report

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.

blank SQL Server Reporting Services report

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.

SQL Server Reporting Services report properties

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.

SQL Server Reporting Services properties page columns node

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.

SQL Server Reporting Services report page sizes, body sizes, margins and column spacing

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.

SQL Server Reporting Services report body

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.

SQL Server Reporting Services mailing label

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).

SQL Server Reporting Services mailing label print layout

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).

 

 

This article is excerpted from Part VI: "Enhanced Report Content" 
of the book "Microsoft SQL Server Reporting Services Recipes: for Designing Expert Reports" by Paul Turley, Robert M. Bruckner (ISBN: 978-0-470-56311-3, Wrox, 2010, Copyright Wiley Publishing Inc.)

Tags:

Comments

16 Responses to “Creating Mailing Labels in SQL Server Reporting Services”

  1. Anonymous says:

     Love this recipe!  *wink*  (not biased at all)
    –Thiago Silva

  2. Anonymous says:

    sweat job
    how can i reapeat reciept multiple times in one report
    thanks in advance
    Mohamed

  3. Anonymous says:

    Hi,
     Could you please provide the steps for this solution using SSRS 2005 ?
    Thanks
    VC
     

  4. Anonymous says:

    Thanks! You rule!

  5. Anonymous says:

    When creating a mailing in Word it specifies the left/right margins as 0.19" yet you used 0.21".
    For instructions for creating this in SSRS 2005 you should see chapter 7 of McGraw-Hill book Microsoft SQL Server 2005 Reporting Services by Brian Larson.
    MarkLD

  6. Anonymous says:

    Very, very helpful – thank you so much.  I’m new to ssrs and I am very seriously considering buying your book!

  7. Anonymous says:

    Thanks for the great tutorial.  Was able to create this report quickly and without hassle.  In SSRS 2008, however, a List won’t work correctly.  It skips two rows.  A Table is the way to go, as you indicated.

  8. Avinesh says:

    Hi, I am trying to get this working but my table just continues on first column, it has multiple pages.

    Please assist.

  9. Avinesh says:

    I got it, it works but when we click print layout, won’t it show that way on the report.

  10. Rita Bhatnagar says:

    Hi,

    I have created avery 8366 labels using this example. When I print from website it prints fine on my labels but If I export it to PDF and then print it is off and the labels and it does not print correctly on the labels.

    Thanks,
    Rita

  11. Rita Bhatnagar says:

    Hi,

    I have created avery 8366 using this example. When I print directly from the website it prints correctly on the labels.

    But when I export it to pdf and try to print it is is off on the labels.

    How do I fix it?
    Rita

  12. Terry says:

    Great article. Everything worked for me within the IDE(VS2008). The layout prints correctly on the label sheets.

    BUT, it will not print correctly when I deploy the report to our report server. Print preview, for what it’s worth, looks correct. For some reason the left margin increases to 1/2 inch when the report is printed.

    I think this may be partially due to there being different “rendering engines” between VS2008 and the SSRS report manager website.

    If I export to PDF the “Fit to Page” option is automatically selected in the print dialog. This messes up the layout even more. If I choose “Actual Size” the print job is very close to fitting correctly. This process adds steps that I don’t want my end users to have to deal with.

    Has anyone else run into this? Any suggestions?

    Terry

  13. James Harris says:

    Hi,

    I have followed your instructions to the letter (as far as i can tell!) but keep hitting the same problem where the labels arent aligned correctly so as you get further down the page the text is creeping up above the top of the label area. I have checked my top and bottom report margins, page size and body height but nothing seems to be incorrect! any advice would be really appreciated.

    James

  14. Steve says:

    “Recipe”…?
    Helpful article though in regard to the final comment: I’m not sure any serious developer would have been automating mailing labels using Office, and conversely (despite Microsoft’s attempt via “Report Builder”) I don’t think many non-developers will be doing it in SSRS.

  15. Bob says:

    I made good progress using your explanation, but can’t seem to get multiple fields of data into a single column, detail line table in SSRS 2008 or using Report Builder 3.0.

    Any additional hints, advice would be greatly appreciated!

  16. robert says:

    pdf issue listed above is the default setting in print is scale, turn off scaling in the print dialog and its perfect!

Leave a Reply

What is 5 + 12 ?
Please leave these two fields as-is:
IMPORTANT! To be able to proceed, you need to solve the following simple math (so we know that you are a human) :-)