Create a mailing labels report? 3

[Catadmin]

Hey, all.

Has anyone had occasion to create an RS report to use as a mailing labels report? I have to generate one that prints on Avery 5160 label sheets. I'm thinking I'm going to have to create text boxes within text boxes along a whole sheet of paper, but I was hoping someone knew of a paper format (like Crystal or Word has) where you can just stick in one label's worth of settings and it will automatically generate everything you need when it prints.

Yeah, I know. It takes a little more tweaking than that, but not as much as formatting the info 15 or more times which is what I'm currently looking at. @=)

Thanks!



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[ookete]

I haven't seen any functionality specifically aimed at label creation (Avery, etc). Perhaps in the future.

Just use Word.

 

[Catadmin]

Can't. I'm supposed to be converting an Access report (to which I don't have access) to an RS report. And I'm just the developer. We have a lot of end users who run this report. Using Word to pull data from SQL would just be a pain and I'd have to get it to too many users to make it feasable.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[lanm]

I have the "Pro SQL Server Reporting Services" book by Apress, and they're saying to use a datalist with multiple-columns. It's very brief, and the description is kinda lame, but that's their suggestion at least.

Maybe this will help ya!

 

[Catadmin]

Do they have any other details on it? I tried the list and it's doing the same thing a multi-column Table does, repeats the information three times across and only goes to the new info on the next row.

Thanks, lanm.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[ookete]

Perhaps there is something you can do in your stored procedure that can put the addresses in the correct columns. You need to get your dataset to be three addresses "wide". I have done similar things before. I am assuming you have a table like this:

Last      First     Address    etc...
Jones     Rick      123 ...
Smith     Victor    234 ...
Barnes    Bubba     345 ...
Pitt      Brad      .
Drew      Nancy     .
Jane      Mary      .
etc...

The following method is kind of a pain, but it works. There are probably better ways you can try. Here goes... The first thing you do is create a table variable that will put an auto incrementing Identity on each record.

DECLARE @TempTable table (
   TempID  int IDENTITY(1,1),
   TempGroup int,
   Last    varchar(50),
   First   varchar(50),
   Address varchar(200),
   etc.... )

INSERT INTO @TempTable (Last, First, Address, etc...)
SELECT Last, First, Address, etc...
FROM   MailingList

This will generate the same table but with that extra column like follows:

TempID  TempGroup  Last      First     Address    etc...
1                  Jones     Rick      123 ...
2                  Smith     Victor    234 ...
3                  Barnes    Bubba     345 ...
4                  Pitt      Brad      .
5                  Drew      Nancy     .
6                  Jane      Mary      .
7                  etc...

Notice we have an extra column called TempGroup and that it is Null. This column is needed to group on for the pivot. You may have to use a cursor to populate it, but there might be another way. Your table should end up looking like:

TempID  TempGroup  Last      First     Address    etc...
1       1          Jones     Rick      123 ...
2       1          Smith     Victor    234 ...
3       1          Barnes    Bubba     345 ...
4       2          Pitt      Brad      .
5       2          Drew      Nancy     .
6       2          Jane      Mary      .
7       3          etc...

(Let me know if you need help with the TempGroup column). The last step is where you actually pivot your information into a type of cross-tab table that is three address listings wide. This uses the Modulus operator

SELECT
   MAX(CASE WHEN TempID % 3 = 1 THEN Last END) Last1,
   MAX(CASE WHEN TempID % 3 = 2 THEN Last END) Last2,
   MAX(CASE WHEN TempID % 3 = 0 THEN Last END) Last3,
   MAX(CASE WHEN TempID % 3 = 1 THEN First END) First1,
   MAX(CASE WHEN TempID % 3 = 2 THEN First END) First2,
   MAX(CASE WHEN TempID % 3 = 0 THEN First END) First3,
  (etc...)
FROM  @TempTable
GROUP BY TempGroup '<-- notice the grouping

So now you have three sets of full address listings per row. You can now build your report as needed. It's a lot of SQL, but it works.

Not sure if this is what you want, but good luck.

 

[Catadmin]

Thank you, ookete, for the idea. I found a simpler solution, though, from someone on the Microsoft news groups.

Here's how to create mailing labels in Reporting Services, folks! @=)

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

Thank you everyone for your assistance!

Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[xlbo]

Stars to both as I know this is gonna come up for me in the next month or so !

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[Catadmin]

An extra star for ookete for the hard work he put into that solution. Also a star for lanm for pointing me in the right direction.

BTW, I've submitted my solution as a FAQ. Hopefully, it'll be approved shortly.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???