Reporting Convergences Chapter: Multi-Detail Bands, SQL Reporting Services Style

by Lisa Nicholls Thu, August 09 2007 16:58

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 report. 

Why bother? because, among other uses, it might be a nice thing to be able to translate between VFP-RDL and RS-RDL (duh).

Some interesting RDL comparisons 

If you look at our VFP-RDL schema, and if you're a SQL Server RS person, you need to understand that it comprises both:

  • what you see in RS as the RDL -- the metadata -- and
  • what you get when you export RS reports to XML data at runtime.

VFP's FFC XmlListener exposes "tuning" properties so that, depending on what you want to do with the XML output, you can optionally request only one of the two parts. For example, you might only want the layout and data source contents and not need the evaluated results of the run if you were planning to move the metadata instructions to RS-RDL (or a Filemaker Pro report definition, or whatever).  This gives you the equivalent of the RS-RDL. 

Alternatively, you can request only the results of the run, if you don't need the design-time metadata information. This is similar to, but not the equivalent of, RS Export XML data, because you can also optionally request the results of the run with, or without, various levels of runtime layout formatting details. In SP2, this includes dynamic formatting and data type information, as well as the original formatting details: page placement for the individual control instances as they appeared in the default report output pages. 

It is often useful to have both parts in one document; you can use the layout information in the metadata section to "inform" new types of output content, for which our VFP-RDL provides an intermediary processing format. A post processor can read the layout instructions and apply them to the new target, discarding whatever is not of interest for its target format. For example, the FFC's HTMLListener can read, but not process, the extension rotation information from the RDL metadata section, and it can't do anything with datatype information either.  But a PDF post-processor could process the rotation instructions in the XML, and an Excel post-processor could make good use of the datatype information.

One thing that interested me immediately when I looked at the RS-XML output is that there is no equivalent to VFP-RDL's rendering of both the page and column "formatting bands" and the "data bands".  VFP-RDL provides options to set up collections of page and column nodes for the "formatting content", which typically would be related to the pagination of the original output content, alongside the collection of "data bands" (detail and group header/footers).  The two collections are cross-referenced by id and idref attributes.

(Tip: If you really want to look at VFP-RDL, although the documentation we wrote for 9.0 isn't bad, we have articulated the schema much more expressively for Sedna, so take a look at the spec or the revised XSD documents available as part of the SP2 ReportOutput source files.)

In Reporting Services, you don't get this pagination information relative to a "default" output of printable pages, when you request XML output. This provides some interesting challenges...  But that's another post.

Another thing that concerned me greatly, from a VFP-RS RDL translation point of view, was that there was no direct equivalent for a layout feature we were baking into the VFP Report Engine in version 9 for the first time: multiple detail bands. 

What are multiple detail bands, and why would we care? 

The scenario goes like this: you want to print all credits followed by all their debits for each of your customers. Let's say Transaction is a child of Account, and you are creating a statement with all Transactions for a time period. 

You can do this by flattening out the data into one table, where some rows have nulls in a set of Credit columns and others have nulls in a set of Debit columns, or by selecting the Credit children into a separate table from the Debit children...

There are lots of ways to handle it from a SQL-table-syntax-structure point of view. From a Reporting point of view, there are lots of ways to kludge how you express the result.

In VFP we wanted to add a way to take care of the multi-child scenario using separate detail bands.  While it was always possible to use innumerable kludges to handle the situation, such as overlaid controls with Print When (that's Visibility for you RS folks), or a stretching footer for the second group of children with everything concatenated with line breaks...

We wanted completely separate formatting to be possible for our two sets of child records, with no difficulties about which one had more records, or anything like that.  This is what we baked in with multi-detail bands. There are lots of wrinkles in this story.

Of course I was interested to see how SQL Server RS made out with the same type of report.

Getting there is definitely less than half the fun 

First, the kludges:

Overlaid controls aren't a great idea in RS but you can achieve pretty much the same thing with IIF() in the expression element and various formatting attributes.  I have to say that IIF() -- available in both environments, of course --always was, and still is, a report's best friend. 

I think (although I have not experimented much with it) that there is also an equivalent to the "stretching group footer" VFP solution in  RS: you can nest a table in a group footer.

Second, the "official" way of handling the story in RS:

Subreports do this, albeit with some caveats, gotchas, and extra work. In RS you'll put the children into separate tables rather than flattening it into one set of rows, much like, in VFP, you would SET RELATION and SET SKIP TO multiple child aliases.  

But I still wanted to see if I could get a flattened set of rows in one data table to create two sets of detail rows in Reporting Services rather than the overhead of a nested report, and multiple data tables. It turns out that you can.

Being there*

The method is something like this:

  • You can order a table any way you want so you can easily create an expression that forces all of the detail rows of one type into one group, followed by all of the detail rows of another type. 
    How you do this depends on the details of your data, and whether you want to do it in SQL or directly in the table properties -- but it's always possible and it's never difficult.
  • RS allows multiple detail lines in the detail set.
  • You can conditionally hide and show lines as a whole. So, while you can't iterate through a flattened set of details twice, to show first one group and then the other, you can force one, or one group, of detail lines to show for a set of detail lines representing one child or type, and then suppress the other detail lines for the second detail type. 
  • You can't change the number of columns in its row, theoretically. But you can merge the cells differently in each row -- so this pretty much takes care of completely-separate formatting for each detail line in your detail set, however it is appropriate to do it for your data and no matter how different the types of details are.

I worked this out concretely because I wanted to offer an walkthrough in my XMLRSDocs notes of how you could use the simple default schema to add a set of screens shots of a report to the documentation you were preparing about your reports.

I figured you might want to have all the screen shots appearing first for each report, followed by the rest of the documentation. It seemed like a good illustrative example of bending RDLDocumenter to your will, without doing any dev work.

It would be easy to do this using XMLRSDocs' concepts of categories, if you were adding rows containing the images or references to the images manually to your RSDocs table.  But I wanted to show how you could do it without adding XML manually and without enhancing the little RDLDocumenterDesigner add-in. RDLDocumenterDesigner's proof-of-concept UI doesn't even expose categories. 

All you really need is RDLDocumenterDesigner's ability to attach custom documentation elements to a report.  You can mark some of your custom doc items as "screen shots" by using a naming convention for the names you give those particular doc items. In your documentation report, you can then pay attention to that naming convention however is appropriate for you -- whether in your query statement(s) for the report data or in dynamic expressions on report items. 

The latter approach (dynamic expressions on report items) is what I use in the walkthrough, using an example naming convention of "IMG_" to represent doc items that held information about screenshots. I worked it out in practice in one of the supplied reports for RDLDocumenter:  ReportMany.RDLC, which provides a sample of documenting multiple reports.

The ReportMany.RDLC has a group on report filename.  I was doing all the ordering work in the RDL rather than in SQL, so I included this as one ordering expression on my layout table, after report filename, to ensure that the screen shots showed up first:

=IIF(Left(UCase(Fields!RDLLayoutName.Value),4) = "IMG_","A","Z") 

The table has one row used to display images; this row has all its cells merged, to provide maximum display area, since I didn't know what your images would look like. I added an  image layout item to this detail row, with the following properties set:

  • 9.125in, 0.125in (basically the width of the row)
  • Autosize
  • External Source
  • the Value property is =Fields!Value.Value -- which looks pretty weird, now that I come to type it <g>, but here's what you're looking at:
    A Value column in the RSDoc table structure that holds the significant value-expression for each documentation row. In this case, Value is used for the fully-qualified (http:// or file://) image filename.  Remember that this is a purely fictitious distinction I created for my example. From XMLRSDoc's point of view, the Value column can be overloaded for many different types of content, distinguished by category, docTag column or, as I have done here, by a naming convention.  Or you can use an entirely different table design.
  • a suitable tooltip expression based on other information in the documentation row:
    ="Screenshot: " & Fields!RDLLayoutName.Value.ToString().Replace ("IMG_","")
  • Visibility hiding the image control when it is not an image:
    =Not (Left(UCase(Fields!RDLLayoutName.Value),4) = "IMG_" AndAlso Fields!DocTag.Value="designerCustom")
    not sure if I actually needed to do this or not, belt and suspenders!
  • No padding

The detail row also has no padding, and has the same Visibility expression you see above:
=Not (Left(UCase(Fields!RDLLayoutName.Value),4) = "IMG_" AndAlso Fields! DocTag.Value="designerCustom")

The second detail row holds "normal" documentation content.  It has its cells broken out appropriately for "normal" documentation content, and textboxes in the cells.  Each of the items in this second row is marked Can Shrink, and all items plus the row are marked with the opposite of the Visibility expressions you see above. Just leave off the Not(), in other words:

=Left(UCase(Fields!RDLLayoutName.Value),4) = "IMG_" AndAlso Fields! DocTag.Value="designerCustom"

That's about it...  seems to work... and is a fully-worked example although your Visibility and Sort expressions would be different.

Where else I've been

There's definitely a lot more to this story of "what I learned in writing RDLDocumenter".  More soon. 

* apologies to Chauncey Gardener 

Tags:

XML/XSLT | Visual FoxPro | SQL Server | Reporting