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

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