{"id":191,"date":"2007-08-09T10:58:00","date_gmt":"2007-08-09T10:58:00","guid":{"rendered":"\/lisa\/post\/2007\/08\/09\/Reporting-Convergences-Chapter-Multi-Detail-Bands-SQL-Reporting-Services-Style.aspx"},"modified":"2007-08-09T10:58:00","modified_gmt":"2007-08-09T10:58:00","slug":"reporting-convergences-chapter-multi-detail-bands-sql-reporting-services-style","status":"publish","type":"post","link":"https:\/\/spacefold.com\/lisa\/2007\/08\/09\/reporting-convergences-chapter-multi-detail-bands-sql-reporting-services-style\/","title":{"rendered":"Reporting Convergences Chapter: Multi-Detail Bands, SQL Reporting Services Style"},"content":{"rendered":"<p>\nWhile I was specifying <a href=\"http:\/\/msdn2.microsoft.com\/en-us\/library\/ms994713(VS.80)\/\" title=\"VFP-RDL documentation topic\">VFP-RDL<\/a>, of course I spent a lot of time figuring out how<br \/>\nit might be used, and why. As part of that effort, I paid attention to what was the same, and what<br \/>\nwas different, between what completely expresses the features of a VFP report, and what existed in<br \/>\nan RS report.&nbsp;\n<\/p>\n<p>\nWhy bother? because, among other uses, it might be a nice thing to be able to translate between<br \/>\nVFP-RDL and RS-RDL (duh).\n<\/p>\n<h3>Some interesting RDL comparisons&nbsp;<\/h3>\n<p>\nIf you look at our VFP-RDL schema, and if you&#39;re a SQL Server RS person, you need to<br \/>\nunderstand that it comprises both:\n<\/p>\n<ul>\n<li>what you see in RS as the RDL &#8212; the metadata &#8212; and \n\t<\/li>\n<li>what you get when you export RS reports to XML data at runtime. \n\t<\/li>\n<\/ul>\n<p>\nVFP&#39;s FFC XmlListener exposes &quot;tuning&quot; properties so that, depending on what you<br \/>\nwant to do with the XML output, you can optionally request only one of the two parts. For example,<br \/>\nyou might only want the layout and data source contents and not need the evaluated results of the<br \/>\nrun if you were planning to move the metadata instructions to RS-RDL (or a Filemaker Pro report<br \/>\ndefinition, or whatever).&nbsp; This gives you the equivalent of the RS-RDL.&nbsp;\n<\/p>\n<p>\nAlternatively, you can request only the results of the run, if you don&#39;t need the design-time<br \/>\nmetadata information. This is similar to, but not the equivalent of, RS Export XML data, because<br \/>\nyou can also optionally request the results of the run with, or without, various levels of runtime<br \/>\nlayout formatting details. In SP2, this includes dynamic formatting and data type information, as<br \/>\nwell as the original formatting details: page placement for the individual control instances as<br \/>\nthey appeared in the default report output pages.&nbsp;\n<\/p>\n<p>\nIt is often useful to have both parts in one document; you can use the layout information in the<br \/>\nmetadata section to &quot;inform&quot; new types of output content, for which our VFP-RDL provides<br \/>\nan intermediary processing format. A post processor can read the layout instructions and apply<br \/>\nthem to the new target, discarding whatever is not of interest for its target format. For example,<br \/>\nthe FFC&#39;s HTMLListener can read, but not process, the extension rotation information from the<br \/>\nRDL metadata section, and it can&#39;t do anything with datatype information either.&nbsp; But a<br \/>\nPDF post-processor could process the rotation instructions in the XML, and an Excel post-processor<br \/>\ncould make good use of the datatype information.\n<\/p>\n<p>\nOne thing that interested me immediately when I looked at the RS-XML output is that there is no<br \/>\nequivalent to VFP-RDL&#39;s rendering of both the page and column &quot;formatting bands&quot; and<br \/>\nthe &quot;data bands&quot;.&nbsp; VFP-RDL provides options to set up collections of page and<br \/>\ncolumn nodes for the &quot;formatting content&quot;, which typically would be related to the<br \/>\npagination of the original output content, alongside the collection of &quot;data bands&quot;<br \/>\n(detail and group header\/footers).&nbsp; The two collections are cross-referenced by id and idref<br \/>\nattributes.\n<\/p>\n<p>\n(Tip: If you really want to look at VFP-RDL, although the documentation we wrote for 9.0 isn&#39;t<br \/>\nbad, we have articulated the schema much more expressively for Sedna, so take a look at the spec<br \/>\nor the revised XSD documents available as part of the SP2 ReportOutput source files.)\n<\/p>\n<p>\nIn Reporting Services, you don&#39;t get this pagination information relative to a<br \/>\n&quot;default&quot; output of printable pages, when you request XML output. This provides some<br \/>\ninteresting challenges&#8230;&nbsp; But that&#39;s another post.\n<\/p>\n<p>\nAnother thing that concerned me greatly, from a VFP-RS RDL translation point of view, was that<br \/>\nthere was no direct equivalent for a layout feature we were baking into the VFP Report Engine in<br \/>\nversion 9 for the first time: multiple detail bands.&nbsp;\n<\/p>\n<h3>What are multiple detail bands, and why would we care?&nbsp;<\/h3>\n<p>\nThe scenario goes like this: you want to print all credits followed by all their debits for each<br \/>\nof your customers. Let&#39;s say Transaction is a child of Account, and you are creating a<br \/>\nstatement with all Transactions for a time period.&nbsp;\n<\/p>\n<p>\nYou can do this by flattening out the data into one table, where some rows have nulls in a set of<br \/>\nCredit columns and others have nulls in a set of Debit columns, or by selecting the Credit<br \/>\nchildren into a separate table from the Debit children&#8230;\n<\/p>\n<p>\nThere are lots of ways to handle it from a SQL-table-syntax-structure point of view. From a<br \/>\nReporting point of view, there are lots of ways to kludge how you express the result.\n<\/p>\n<p>\nIn VFP we wanted to add a way to take care of the multi-child scenario using separate detail<br \/>\nbands.&nbsp; While it was always possible to use innumerable kludges to handle the situation, such<br \/>\nas overlaid controls with <em>Print When<\/em> (that&#39;s <em>Visibility<\/em> for you RS folks),<br \/>\nor a stretching footer for the second group of children with everything concatenated with line<br \/>\nbreaks&#8230;\n<\/p>\n<p>\nWe wanted completely separate formatting to be possible for our two sets of child records, with no<br \/>\ndifficulties about which one had more records, or anything like that.&nbsp; This is what we baked<br \/>\nin with multi-detail bands. There are lots of wrinkles in this story.\n<\/p>\n<p>\nOf course I was interested to see how SQL Server RS made out with the same type of report.\n<\/p>\n<h3>Getting there is definitely less than half the fun&nbsp;<\/h3>\n<p>\nFirst, the kludges:\n<\/p>\n<p>\nOverlaid controls aren&#39;t a great idea in RS but you can achieve pretty much the same thing<br \/>\nwith <strong>IIF()<\/strong> in the expression element and various formatting attributes.&nbsp; I<br \/>\nhave to say that <strong>IIF()<\/strong> &#8212; available in both environments, of course &#8211;always was,<br \/>\nand still is, a report&#39;s best friend.&nbsp;\n<\/p>\n<p>\nI think (although I have not experimented much with it) that there is also an equivalent to the<br \/>\n&quot;stretching group footer&quot; VFP solution in&nbsp; RS: you can nest a table in a group<br \/>\nfooter.\n<\/p>\n<p>\nSecond, the &quot;official&quot; way of handling the story in RS:\n<\/p>\n<p>\nSubreports do this, albeit with some caveats, gotchas, and extra work. In RS you&#39;ll put the<br \/>\nchildren into separate tables rather than flattening it into one set of rows, much like, in VFP,<br \/>\nyou would <strong>SET RELATION<\/strong> and <strong>SET SKIP TO<\/strong> multiple child<br \/>\naliases.&nbsp;&nbsp;\n<\/p>\n<p>\nBut I still wanted to see if I could get a flattened set of rows in one data table to create two<br \/>\nsets of <em>detail rows <\/em>in Reporting Services rather than the overhead of a nested report,<br \/>\nand multiple data tables. It turns out that you can.\n<\/p>\n<h3>Being there*<br \/>\n<\/h3>\n<p>\nThe method is something like this:\n<\/p>\n<ul>\n<li>You can order a table any way you want so you can easily create an expression that<br \/>\n\tforces all of the detail rows of one type into one group, followed by all of the detail rows of<br \/>\n\tanother type.&nbsp; <br \/>\n\tHow you do this depends on the details of your data, and whether you want to do it in SQL<br \/>\n\tor directly in the table properties &#8212; but it&#39;s always possible and it&#39;s never difficult.\n\t<\/li>\n<li>RS allows multiple detail <em>lines<\/em> in the detail set.<\/li>\n<li>You can conditionally hide and show <em>lines<\/em> as a whole. So, while you can&#39;t<br \/>\n\titerate through a flattened set of details twice, to show first one group and then the other, you<br \/>\n\tcan force one, or one group, of detail lines to show for a set of detail lines representing one<br \/>\n\tchild or type, and then suppress the other detail lines for the second detail type.&nbsp; <\/li>\n<li>You can&#39;t change the number of columns in its row, theoretically. But you can<br \/>\n\tmerge the cells differently in each row &#8212; so this pretty much takes care of completely-separate<br \/>\n\tformatting for each detail line in your detail set, however it is appropriate to do it for your<br \/>\n\tdata and no matter how different the types of details are.\n\t<\/li>\n<\/ul>\n<p>\nI worked this out concretely because I wanted to offer an walkthrough in my <a href=\"\/articles\/xmlrsdocs\/\" title=\"XMLRSdocs article-docs\">XMLRSDocs<\/a> notes of how you<br \/>\ncould use the simple default schema to add a set of screens shots of a report to the documentation<br \/>\nyou were preparing about your reports.\n<\/p>\n<p>\nI figured you might want to have all the screen shots appearing first for each report, followed by<br \/>\nthe rest of the documentation. It seemed like a good illustrative example of bending RDLDocumenter<br \/>\nto your will, without doing any dev work.\n<\/p>\n<p>\nIt would be easy to do this using XMLRSDocs&#39; concepts of categories, if you were adding rows<br \/>\ncontaining the images or references to the images manually to your RSDocs table.&nbsp; But I<br \/>\nwanted to show how you could do it <em>without<\/em> adding XML manually and <em>without<\/em><br \/>\nenhancing the little RDLDocumenterDesigner add-in. RDLDocumenterDesigner&#39;s proof-of-concept UI<br \/>\ndoesn&#39;t even expose categories.&nbsp;\n<\/p>\n<p>\nAll you really need is RDLDocumenterDesigner&#39;s ability to attach custom documentation elements<br \/>\nto a report.&nbsp; You can mark some of your custom doc items as &quot;screen shots&quot; by using<br \/>\na naming convention for the names you give those particular doc items. In your documentation<br \/>\nreport, you can then pay attention to that naming convention however is appropriate for you &#8212;<br \/>\nwhether in your query statement(s) for the report data or in dynamic expressions on report<br \/>\nitems.&nbsp;\n<\/p>\n<p>\nThe latter approach (dynamic expressions on report items) is what I use in the walkthrough, using<br \/>\nan example naming convention of &quot;<strong>IMG_<\/strong>&quot; to represent doc items that held<br \/>\ninformation about screenshots. I worked it out in practice in one of the supplied reports for<br \/>\nRDLDocumenter:&nbsp; ReportMany.RDLC, which provides a sample of documenting multiple reports.\n<\/p>\n<p>\nThe ReportMany.RDLC has a group on report filename.&nbsp; I was doing all the ordering work in the<br \/>\nRDL rather than in SQL, so I included this as one ordering expression on my layout table, after<br \/>\nreport filename, to ensure that the screen shots showed up first:\n<\/p>\n<p>\n<strong>=IIF(Left(UCase(Fields!RDLLayoutName.Value),4) =<br \/>\n&quot;IMG_&quot;,&quot;A&quot;,&quot;Z&quot;)&nbsp;<\/strong>\n<\/p>\n<p>\nThe table has one row used to display images; this row has all its cells merged, to provide<br \/>\nmaximum display area, since I didn&#39;t know what your images would look like. I added an&nbsp;<br \/>\nimage layout item to this detail row, with the following properties set:\n<\/p>\n<ul>\n<li>9.125in, 0.125in (basically the width of the row)<\/li>\n<li>Autosize<\/li>\n<li>External Source<\/li>\n<li>the Value property is <strong>=Fields!Value.Value<\/strong> &#8212; which looks pretty<br \/>\n\tweird, now that I come to type it &lt;g&gt;, but here&#39;s what you&#39;re looking at: <br \/>\n\tA Value column in the RSDoc table structure that holds the significant value-expression<br \/>\n\tfor each documentation row. In this case, Value is used for the fully-qualified (http:\/\/ or<br \/>\n\tfile:\/\/) image filename.&nbsp; Remember that this is a purely fictitious distinction I created for<br \/>\n\tmy example. From XMLRSDoc&#39;s point of view, the Value column can be overloaded for many<br \/>\n\tdifferent types of content, distinguished by category, docTag column or, as I have done here, by a<br \/>\n\tnaming convention.&nbsp; Or you can use an entirely different table design. <\/li>\n<li>a suitable tooltip expression based on other information in the documentation row:<br \/>\n\t<strong>=&quot;Screenshot: &quot; &amp; Fields!RDLLayoutName.Value.ToString().Replace<br \/>\n\t(&quot;IMG_&quot;,&quot;&quot;)<\/strong>\n\t<\/li>\n<li>Visibility hiding the image control when it is not an image: <br \/>\n\t<strong>=Not (Left(UCase(Fields!RDLLayoutName.Value),4) = &quot;IMG_&quot; AndAlso<br \/>\n\tFields!DocTag.Value=&quot;designerCustom&quot;)<br \/>\n\t<\/strong><em>not sure if I actually needed to do this or not, belt and suspenders!<br \/>\n\t<\/em><\/li>\n<li>No padding<em><br \/>\n\t<\/em><\/li>\n<\/ul>\n<p>\nThe detail row also has no padding, and has the same Visibility expression you see above: <br \/>\n<strong>=Not (Left(UCase(Fields!RDLLayoutName.Value),4) = &quot;IMG_&quot; AndAlso Fields!<br \/>\nDocTag.Value=&quot;designerCustom&quot;)<\/strong>\n<\/p>\n<p>\nThe second detail row holds &quot;normal&quot; documentation content.&nbsp; It has its cells<br \/>\nbroken out appropriately for &quot;normal&quot; documentation content, and textboxes in the<br \/>\ncells.&nbsp; Each of the items in this second row is marked <strong>Can Shrink<\/strong>, and all<br \/>\nitems plus the row are marked with the <em>opposite <\/em>of the Visibility expressions you see<br \/>\nabove. Just leave off the <strong>Not()<\/strong>, in other words:\n<\/p>\n<p>\n<strong>=Left(UCase(Fields!RDLLayoutName.Value),4) = &quot;IMG_&quot; AndAlso Fields!<br \/>\nDocTag.Value=&quot;designerCustom&quot;<\/strong>\n<\/p>\n<p>\nThat&#39;s about it&#8230;&nbsp; seems to work&#8230; and is a fully-worked example although your<br \/>\nVisibility and Sort expressions would be different.\n<\/p>\n<h3>Where else I&#39;ve been<\/h3>\n<p>\nThere&#39;s definitely a lot more to this story of &quot;what I learned in writing<br \/>\nRDLDocumenter&quot;.&nbsp; More soon.&nbsp;\n<\/p>\n<p>\n* apologies to Chauncey Gardener&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>While I was specifying VFP-RDL, of course I spent a lot of time figuring out how it might be used, and why. As part of that effort, I paid attention to what was the same, and what was different, between what completely expresses the features of a VFP report, and what existed in an RS<a class=\"more-link\" href=\"https:\/\/spacefold.com\/lisa\/2007\/08\/09\/reporting-convergences-chapter-multi-detail-bands-sql-reporting-services-style\/\">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,10],"tags":[],"class_list":["post-191","post","type-post","status-publish","format-standard","hentry","category-reporting","category-sql-server","category-visual-foxpro","category-xml-xslt"],"_links":{"self":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts\/191","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=191"}],"version-history":[{"count":0,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts\/191\/revisions"}],"wp:attachment":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/media?parent=191"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/categories?post=191"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/tags?post=191"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}