\nThis 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.\n<\/p>\n
\nI'll be working on TMM while I'm away, slowly, but I'm not sure if I will have any opportunities to post.\n<\/p>\n
\nIt 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.\n<\/p>\n
\nI don't know… how goes it, in your world?\n<\/p>\n
\nMailing 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.\n<\/p>\n
\nWhat I can't really figure out is why people tend to ask questions about this type of thing in bunches. \n<\/p>\n
\nIt 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.\n<\/p>\n
\nApparently 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. \n<\/p>\n
\nThis 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.\n<\/p>\n
\nYou can solve this classic problem using the equally-classic Cartesian join.\n<\/p>\n
\nThere have always been a bunch of ways to do this in SQL Server, but (thank goodness) SQL Server 2005 has Top N-variable syntax.\n<\/p>\n
\nSo, now, all you have to do is what you see in the quick sketch below. \n<\/p>\n
\nNotice that I've used a Master<\/strong> database table known to have a decent size record set here; you can use any table you like in the nested SELECT<\/strong> as long as it has >= the max number of label copies you might want to produce. Also notice the parens around the TOP<\/strong> clause, without which it might not work:\n<\/p>\n \nDECLARE<\/font> @LabelCount AS<\/font> integer \nWhat 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.\n<\/p>\n \n… Here's the VFP version. The SELECT<\/strong> 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.\n<\/p>\n \nYou 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:\n<\/p>\n \n#DEFINE <\/font>DEFAULT_COUNT 4 \niSelect = SELECT<\/font>(0)\n<\/p>\n \nDO WHILE RECCOUNT() < iLabelCount \nIF RECCOUNT() > 0 IF USED("YourRecipients") \nIF USED("YourLargeEnoughTable") \nSELECT (iSelect) <\/font>\n<\/p>\n \nRETURN <\/font>\n<\/p>\n<\/div>\n \nJust 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.\n<\/p>\n \nYou 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.\n<\/p>\n \nWhat more could a label-meister ask? \n<\/p>\n \nHow 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. <\/p>\n","protected":false},"excerpt":{"rendered":" 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?Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,6,9],"tags":[],"class_list":["post-150","post","type-post","status-publish","format-standard","hentry","category-reporting","category-sql-server","category-visual-foxpro"],"_links":{"self":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts\/150","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/comments?post=150"}],"version-history":[{"count":0,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts\/150\/revisions"}],"wp:attachment":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/media?parent=150"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/categories?post=150"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/tags?post=150"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\nSET<\/font> @LabelCount =<\/font> 4
\nSELECT<\/font> *<\/font> FROM<\/font> Recipient
\n RIGHT<\/font> OUTER<\/font> JOIN<\/font>
\n (<\/font>SELECT<\/font> TOP<\/font> (<\/font>@LabelCount)<\/font> Name<\/font>
\n FROM<\/font> Master.<\/font>dbo.<\/font>spt_values )<\/font> X ON<\/font> 1 =<\/font> 1
\nORDER<\/font> BY<\/font> Recipient_ID\n<\/p>\nMorph this to VFP SQL <\/h5>\n
\nLPARAMETERS <\/font>iLabelCount
\nLOCAL <\/font>iSelect
\nIF EMPTY<\/font>(iLabelCount) OR VAL<\/font>(TRANSFORM<\/font>(iLabelCount)) < 2
\n iLabelCount = DEFAULT_COUNT
\nELSE
\n<\/font> iLabelCount = INT<\/font>(VAL<\/font>(TRANSFORM<\/font>(iLabelCount)))
\nENDIF<\/font>\n<\/p>\n
\n SELECT 0
\n WAIT WINDOW NOWAIT "Pick a table with at least " ;
\n + TRANSFORM(iLabelCount) + " recs… "
\n USE ? ALIAS YourLargeEnoughTable SHARED AGAIN
\n WAIT CLEAR
\n IF EMPTY(ALIAS())
\n EXIT
\n ENDIF
\nENDDO <\/font>\n<\/p>\n
\n SELECT 0
\n WAIT WINDOW NOWAIT "Pick the table with your label recipients… "
\n USE ? ALIAS YourRecipients SHARED AGAIN
\n WAIT CLEAR
\nENDIF <\/p>\n
\n SELECT * FROM YourRecipients ;
\n RIGHT OUTER JOIN ;
\n (SELECT TOP (iLabelCount) .T. ;
\n FROM YourLargeEnoughTable ;
\n ORDER BY .T.) X ON 1 = 1 ; && doesn't matter what fields or order
\n<\/font> ORDER BY 1 && or whatever is appropriate
\n && for your recipient table
\n<\/font> USE IN YourRecipients
\nENDIF <\/font>\n<\/p>\n
\n USE IN YourLargeEnoughTable
\nENDIF <\/font>\n<\/p>\nPut X-Up and Times-N together and they spell label heaven <\/h4>\n