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:
#DEFINE DEFAULT_COUNT 4
LPARAMETERS iLabelCount
LOCAL iSelect
IF EMPTY(iLabelCount) OR VAL(TRANSFORM(iLabelCount)) < 2
iLabelCount = DEFAULT_COUNT
ELSE
iLabelCount = INT(VAL(TRANSFORM(iLabelCount)))
ENDIF
iSelect = SELECT(0)
DO WHILE RECCOUNT() < iLabelCount
SELECT 0
WAIT WINDOW NOWAIT "Pick a table with at least " ;
+ TRANSFORM(iLabelCount) + " recs… "
USE ? ALIAS YourLargeEnoughTable SHARED AGAIN
WAIT CLEAR
IF EMPTY(ALIAS())
EXIT
ENDIF
ENDDO
IF RECCOUNT() > 0
SELECT 0
WAIT WINDOW NOWAIT "Pick the table with your label recipients… "
USE ? ALIAS YourRecipients SHARED AGAIN
WAIT CLEAR
ENDIF
IF USED("YourRecipients")
SELECT * FROM YourRecipients ;
RIGHT OUTER JOIN ;
(SELECT TOP (iLabelCount) .T. ;
FROM YourLargeEnoughTable ;
ORDER BY .T.) X ON 1 = 1 ; && doesn't matter what fields or order
ORDER BY 1 && or whatever is appropriate
&& for your recipient table
USE IN YourRecipients
ENDIF
IF USED("YourLargeEnoughTable")
USE IN YourLargeEnoughTable
ENDIF
SELECT (iSelect)
RETURN
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.
I found a similar post on another blog and couldn’t fully see the implications, but your post explains it better. Thanks!
That’s a really neat solution when combined with the IQueryable change that John suggested. I have been putting up with having to hit the database for Linq to SQL queries to date and it was starting to really bug me! Tell your friend he needs to blog!!