{"id":151,"date":"2007-11-14T09:40:00","date_gmt":"2007-11-14T09:40:00","guid":{"rendered":"\/lisa\/post\/2007\/11\/14\/Sideways-Report-SQL-for-VFP-A-Reporting-Convergence-Chapter.aspx"},"modified":"2007-11-14T09:40:00","modified_gmt":"2007-11-14T09:40:00","slug":"sideways-report-sql-for-vfp-a-reporting-convergence-chapter","status":"publish","type":"post","link":"https:\/\/spacefold.com\/lisa\/2007\/11\/14\/sideways-report-sql-for-vfp-a-reporting-convergence-chapter\/","title":{"rendered":"Sideways Report SQL for VFP: A Reporting Convergence Chapter"},"content":{"rendered":"

\nA couple of days I wrote about how to "snake columns across" in SQL Server for use with Reporting Services<\/a>. \n<\/p>\n

\nAs 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.\n<\/p>\n

\nHere'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.\n<\/p>\n

\nSo… how should Ludek kludge this in the Report Designer? Or… should he tell his users they can't have this layout?\n<\/p>\n

\nNot 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.\n<\/p>\n

\nTake 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:\n<\/p>\n

<\/p>\n

\nCLOSE ALL
\nUSE ? <\/font>ALIAS <\/font>YourTable <\/font>SHARED
\n<\/font>YourField = <\/font>FIELD<\/font>(1)
\n<\/font>
\nSELECT A.<\/font>RecNo<\/font>, B.Col1, B.YourMemoCol1, C.Col2, C.YourMemoCol2 ;
\n<\/font>   FROM <\/font>(<\/font>SELECT <\/font>(YourField), <\/font>RECNO<\/font>() <\/font>AS RecNo FROM <\/font>YourTable) A ;
\n<\/font>LEFT JOIN <\/font>;
\n   (<\/font>SELECT <\/font>&YourField <\/font>As <\/font>Col1, ;
\n<\/font>    PADR<\/font>("The Memo" + <\/font>TRANSFORM<\/font>(<\/font>RECNO<\/font>()),20) <\/font>AS <\/font>YourMemoCol1, ;
\n<\/font>    RECNO<\/font>() <\/font>AS RecNo <\/font>;
\n<\/font>FROM <\/font>YourTable) B ;
\n<\/font>   ON <\/font>B.<\/font>RecNo <\/font>% 2 = 1 AND A.<\/font>RecNo <\/font>= <\/font>CEILING<\/font>(B.<\/font>RecNo<\/font>\/2) ;
\n<\/font>LEFT JOIN <\/font>;
\n   (<\/font>SELECT <\/font>&YourField <\/font>As <\/font>Col2, ;
\n<\/font>    PADR<\/font>("The Memo" + <\/font>TRANSFORM<\/font>(<\/font>RECNO<\/font>()),20) <\/font>AS <\/font>YourMemoCol2, ;
\n<\/font>    RECNO<\/font>() <\/font>AS RecNo <\/font>;
\n<\/font>FROM <\/font>YourTable) C ;
\n<\/font>   ON <\/font>C.<\/font>RecNo <\/font>% 2 = 0 AND A.<\/font>RecNo <\/font>= <\/font>CEILING<\/font>(C.<\/font>RecNo<\/font>\/2) ;
\n<\/font>WHERE <\/font>(A.<\/font>RecNo <\/font><= (<\/font>SELECT CEILING<\/font>(<\/font>COUNT<\/font>(*)\/2 ) <\/font>FROM <\/font>YourTable)) ;
\n<\/font>ORDER BY <\/font>A.<\/font>RecNo <\/font>&& or whatever you want <\/font>\n<\/p>\n

<\/font><\/p>\n

\n \n<\/p>\n

\nThe result of this query, using the Northwind Customers table, looks like this:\n<\/p>\n

\nRecno   Col1      Yourmemocol1     Col2      Yourmemocol2       \n1       ALFKI     The Memo1        ANATR     The Memo2           \n2       ANTON     The Memo3        AROUT     The Memo4           \n3       BERGS     The Memo5        BLAUS     The Memo6           \n4       BLONP     The Memo7        BOLID     The Memo8           \n:44     WARTH     The Memo87       WELLI     The Memo88\n45      WHITC     The Memo89       WILMK     The Memo90          \n46      WOLZA     The Memo91       NULL.     .NULL.              \n<\/pre>\n
\nYou 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. \n<\/p>\n
\nThe 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.\n<\/p>\n
\nAs 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.\n<\/p>\n
\nAnd that is pretty much all she wrote on this one…<\/p>\n","protected":false},"excerpt":{"rendered":"
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: LudekRead 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-151","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\/151","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=151"}],"version-history":[{"count":0,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts\/151\/revisions"}],"wp:attachment":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/media?parent=151"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/categories?post=151"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/tags?post=151"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}