How to Create a Mailing Label Report

Here's how to create mailing labels in Reporting Services, folks, without coding hassel or being able to set your paper type to Avery XXXX.

First, set your Report Property margins to the margins of the label sheet in question. For Avery 5160, I used Top & Bottom of .5in and Left & Right of .15in.

Next, grab a ruler and a sheet of your labels. You'll need to measure margins and the space between columns.

Then go into the Body properties. Change Columns to the # of columns you have on the label sheet (in my case 3), change ColumnSpacing to the size of the space between the columns (in my case .125in which is the equivilant of 1/8th of an inch), and change the Size properties to the actual size of the labels (width is first, height is second).

Third, the only column in layout that you can work with will be the first column. Add a list box that is slightly smaller than the Body properties. Add a rectangle that is slightly smaller than the List box properties. Inside the rectangle (assuming you already have created your dataset), put your fields. I did a concatenation expression for City & State, then put Zip below. Adjust font & field spacing as necessary to remain within the size of the rectangle (don't go all the way to the edge or it will mess your formatting).

To see your label sheet, go to Preview, right click the report and click on Print Preview. Regular Preview only shows one row, Print Preview will show all of them.

If you see the first and third columns filled in on the first page and the middle column only on the second page, or if you see the name & zip in one column with the street address & city/state in another, the problem comes from a combination of your margin spacing, column spacing and the sizes of the boxes within the body (they are the same size as the body).

You'll have to play with the sizes, but once you get them, it looks just like you created the labels from a Word Doc.

BTW, you'll want to count your rows & columns to make sure you have the right number. I kept getting 11 rows or 9 rows, not the ten on the label sheet, until I corrected my height problems.

Looks like the ListBox default is Ascending order, in case anyone was interested. I certainly didn't do an Order By on my SQL Statement, but things are going Alphabetically A-Z, from first row, left to right, then wrapping down to second row, first column.

Hope this helps other people out! @=)