A couple of days I wrote about how to "snake columns across" in SQL Server for use with Reporting Services.
As luck would have it, Ludek.Coufal wrote again this morning asking a completely different question he faces in the VFP reporting environment — but it can be solved exactly the same way.
Here's the deal: Ludek wants to show images "two-up".This is the "proofsheet" variant of the classic snaking column problem that I mentioned when I wrote earlier. He has stretching memo fields to go with those images. But the VFP Report Engine doesn't allow variable band height when you decide that columns should flow "across and then down". Its algorithms aren't good enough to figure out what to do when the memo fields that are supposed to stretch next to each other have wildly different lengths — especially when, as is often the case, some records' text might flow through multiple pages.
So… how should Ludek kludge this in the Report Designer? Or… should he tell his users they can't have this layout?
Not a bit of it. This is simply another case of the best tool for the job. In this case, as in SQL Reporting Services, the best tool for the job is not the report layout. It's your SQL smarts.
Take another look at the query I wrote for SQL Server in that other post. Here is a version for VFP — designed with just two columns, for Ludek's scenario, but obviously extensible to however many columns you need — it is not, however, guaranteed to work terribly well with huge tables. I have written this example so you can use it with any table:
CLOSE ALL
USE ? ALIAS YourTable SHARED
YourField = FIELD(1)
SELECT A.RecNo, B.Col1, B.YourMemoCol1, C.Col2, C.YourMemoCol2 ;
FROM (SELECT (YourField), RECNO() AS RecNo FROM YourTable) A ;
LEFT JOIN ;
(SELECT &YourField As Col1, ;
PADR("The Memo" + TRANSFORM(RECNO()),20) AS YourMemoCol1, ;
RECNO() AS RecNo ;
FROM YourTable) B ;
ON B.RecNo % 2 = 1 AND A.RecNo = CEILING(B.RecNo/2) ;
LEFT JOIN ;
(SELECT &YourField As Col2, ;
PADR("The Memo" + TRANSFORM(RECNO()),20) AS YourMemoCol2, ;
RECNO() AS RecNo ;
FROM YourTable) C ;
ON C.RecNo % 2 = 0 AND A.RecNo = CEILING(C.RecNo/2) ;
WHERE (A.RecNo <= (SELECT CEILING(COUNT(*)/2 ) FROM YourTable)) ;
ORDER BY A.RecNo && or whatever you want
The result of this query, using the Northwind Customers table, looks like this:
Recno Col1 Yourmemocol1 Col2 Yourmemocol2 1 ALFKI The Memo1 ANATR The Memo2 2 ANTON The Memo3 AROUT The Memo4 3 BERGS The Memo5 BLAUS The Memo6 4 BLONP The Memo7 BOLID The Memo8 :44 WARTH The Memo87 WELLI The Memo88 45 WHITC The Memo89 WILMK The Memo90 46 WOLZA The Memo91 NULL. .NULL.
You can easily see how this solves the "two-up" issue. Ludek just needs a regular detail band, without multiple columns, with a two layout control sets in the band.
The layout elements on the left side of the band reference a different set of columns in the row than the elements on the right side of the band. The detail band can stretch just fine, and pays attention to the longer (or longest, with more than two-up) memo for its height. Everybody's happy.
As I said above, this won't work wonderfully with large tables, but there are probably a lot of ways you can optimize it in VFP so it does. I basically did a straight port from my SQL Server version, swapping ROW_NUMBER() for RECNO() functionality.
And that is pretty much all she wrote on this one…