Skip to main content

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

Comments

Post a Comment

Popular posts from this blog

GRIDVIEW GROUPING

When displaying data, we sometimes would like to group data for better user experience or when displaying long list of hierarchal data, we would want to display them in a tree view kind of structure. There is more than way of doing this, but I am going to explain achieving this functionality using  AJAX Collapsible Panel Extender Control . Overview: I am going to use  Adventure Works  as datasource. Every product in  Production.Product  table belongs to a product sub category. We fetch handful of products and the sub categories they belong to from the database. Our objective is to list all the available sub categories and allow user to  expand/collapse  to look/hide the list of products belonging to each subcategory. Database Connection Added following entry under  connectionStrings  element in  web.config . < add   name = "Sql"   connectionString="Data Source=(local); Initial  Catalog = AdventureWorks ...
Post a Comment
Read more

Dynamic Generation of Word Document Report in ASP.NET with HTML

Overview Ever needed to generate Word documents from your ASP.NET applications? There are a couple of components which will help to generate Word documents, but using these components may have some overhead such as Component Registration, setting permissions, licenses, etc., Sometimes, it may even become difficult to understand their features and use them. Generating word document in ASP.NET with HTML is incredibly easy. The following sample demonstrates how to place Heading and Table dynamically in the word document in ASP.NET web applications using HTML. Requirements Microsoft Visual Web Developer 2005 Express Edition Create the Web Application project Add the new Web Site Application project (with name as Generate Word Document) and place the button (with name as btnGenerateDocument) in the Default.aspx web form as shown below. Double click the highlighted code behind file from the Solution Explorer and add the following namespaces. Listing 1 using  System...
Post a Comment
Read more

GRIDVIEW ZOOM IMAGE

When you have images in your  GridView , you would most likely show them as thumbnails so as to not distort the whole layout. However user would want to look at the full image by clicking on the image or just hovering his mouse over it. In today’s applications, this is a basic requirement and there are just so many third party controls or plugins which would support this functionality. I am going do this conventional way using  javascript  way in this article.  On top of it, I am also going to explain how to get images from database using  HttpHandlers . Example: I am using  Adventure Works  as datasource. We fetch handful of products and bind them to the grid. When page is initially loaded, we retrieve products from Production . Product  table and bind them to the grid. We display some product attributes such as Product ID, Product Number, Product Name, List Price and product’s thumbnail. When user hover his mouse on the page, we fetch the f...
Post a Comment
Read more