This will be a quick Reporting Convergences chapter, as C and I get ready for a week+ of fun, travel, and the madcap thrills of completely Life Re-arranging.

I'll be working on TMM while I'm away, slowly, but I'm not sure if I will have any opportunities to post.

What is it about mailing labels?

It always amuses me that Microsoft and other vendors are off in their flow-page, screen-only, and web-centric universe while most of the people I know who have to slam out reports for real business applications are still worried about delivering fixed page layouts for printed output.  As a primary problem, not as an afterthought.

I don't know… how goes it, in your world?

Mailing labels represent the epitome of the fixed page layout, in some ways, because label sheets are made up of fixed-layout sections, each of which is minutely specified and each of which must be exactly placed.  As you know.  That makes them a real PITA from the POV of report layout design. As we all know.

What I can't really figure out is why people tend to ask questions about this type of thing in bunches. 

It just happened AGAIN. Just like I got two Sideways report questions in a row, in the past two weeks, two people asked how to print a variable number of label copies, one in SQL Server and one in VFP.

The labels-times-N problem

Apparently more than one of you are writing an application for UPS or whomtever to handle the holiday rush and they want multiple copies of each recipient's label.  Of course you don't want to hard-code the number of label copies. 

This is a classic problem. You can do it in both VFP and SQL Server RS report layouts — but it's a real mess. Just like the Sideways problem you should solve this one at the data level whenever possible, rather than kludging around in the report layout.

You can solve this classic problem using the equally-classic Cartesian join.

The SQL Server resolution 

There have always been a bunch of ways to do this in SQL Server, but (thank goodness) SQL Server 2005 has Top N-variable syntax.

So, now, all you have to do is what you see in the quick sketch below. 

Notice that I've used a Master database table known to have a decent size record set here; you can use any table you like in the nested SELECT as long as it has >= the max number of label copies you might want to produce.   Also notice the parens around the TOP clause, without which it might not work:

DECLARE @LabelCount AS integer
SET @LabelCount = 4
SELECT * FROM Recipient
   RIGHT OUTER JOIN
   (SELECT TOP (@LabelCount) Name
      FROM Master.dbo.spt_values ) X ON 1 = 1
ORDER BY Recipient_ID

What you're looking at here (the Cartesian join part) is "join every record in one table with every record in the other table".  Not usually recommended, but perfect when you want multiple copies of your data.  You just join it with some set of records, any set at all, containing the proper number of rows.

Morph this to VFP SQL

…  Here's the VFP version. The SELECT statement in it is almost exactly the same as the SQL Server version, but this sample code is fully-worked to be runnable with any tables you happen to have handy.

You can pick a table to use in place of the Master table you see in the SQL Server sketch above, and you also pick a table to serve as your "recipient" table, as part of this demo:

Just so you know: I am aware of the hash that VFP's p*ss-poor attempt at rich text copy and paste sometimes makes in these posts.  I hope the code comes out runnable, and some days that's the best I can do.

Put X-Up and Times-N together and they spell label heaven

You can put Sideways reports for SQL Server or for VFP, from my recent posts, together with what you have here.  You get a variable number of label copies, with each set of copies on its own horizontal line.

What more could a label-meister ask? 

How about rational label sizes? Doubt that will ever happen, because odd sizes of labels, precisely placed on a sheet, are a business requirement in the real world. Gotta respect that.