The gods of VFP tried really hard, for many years, to update the FoxPro reporting system. When people have trouble conceiving sometimes they take a lot of hormonal inducements and do other unnatural stuff (in the case of Microsoft, the unnatural part was hiring C & me), to have that longed-for baby.
And, as you probably know, one of the effects of all this unnatural stuff is that, after years of not being able to conceive, the parents suddenly have quintuplets.
Creating the VFP 9 Reporting System was a little like that. Except…
Once we’d stretched our budget and our thoughts as far as we dreamed they might go, and an ultra sound showed a likelihood of several bouncing baby enhancements… it turned out we had not covered something very important that the VFP community was pining for.
And that something was multiple detail bands.
We swallowed hard, and hoped for a miracle.
It was a difficult birth
MD is only a fraternal sibling of all the other VFP 9 reporting enhancements. It has completely different genetics. It tends to fight with its siblings all the time, for no good reason (they don’t even eat the same dogfood). In the womb, it fought like crazy: for nourishment, for light, for space.
It’s going to have a difficult life
While everybody wanted MD, I have a feeling nobody loves MD. Even when it’s trying to be a good little feature, and often you wonder if it’s doing just the opposite, MD probably frustrates one’s expectations as often as not.
It has to happen, because the expectations are limitless and self-defeating. No matter how flexible we tried to make the rules, no matter how many ways we tried to accommodate to what people might do, there is no way that every possible use of database tables can fit into multiple detail bands the way you might want.
That’s just it: they’re bands. The report engine processes bands. If it processed amorphous regions, you’d have other problems. SSRS can’t relate tables by a key, for example; you need to call a subreport if you want to drive a second dataset based on a group break in the first.
You can’t just take any object or business entity, as expressed in a paper report, and say “this has to be expressed in relational tables and extruded out through a set of concentric bands”, and get it to work every time. The relational schema doesn’t perfectly match the real world, for starters, and when the real world creates a paper design, it’s completely unrestrained.
But… you can usually fake it. And MD wants to make it easier for you to do that.
When the schema needs a little help
Here’s the puzzle that an old friend posed to me a couple of weeks ago: Suppose you have a parent entity with transactions against it. You wish to have each parent show its transactions by date, with a subgroup on financial period.
So far, so good. You don’t even need a second detail band for this.
So what’s the big deal?
The problem is that we need to show all the transactions for the parent, aggregated by period, before any of the period groups that show the details.
Since that’s probably clear as mud, let’s say that the aggregation we need to precede the details looks something like this:
SELECT parentKey , SUM(TransactionTotal) As Net, FinancialPeriodInformation FROM ;
Child GROUP BY parentPK, Financial PeriodInformation
… and my old friend had tried to make this easier to do by setting up a table holding this aggregated header information, thinking “here we have a parent with two children, let’s use multiple detail bands for each of the children”. Which, I’m sure, seems reasonable to you, too.
This is where we all start to clutch our heads and moan.
Why doesn’t it “just work”?
The second child, holding the true detail, needs to break on period, so that each period can have appropriate header and footer information. The first child, holding the aggregate, should not break on period. It should show all the data related to the parent up-top.
… and even though detail bands get their own headers and footers in VFP 9, group breaks are still concentric. If we start from the viewpoint of the parent and group break on period, these breaks are going to affect both detail bands, not just the second one.
Parents need to let their children drive more.
There may be a hundred different ways to fake this in VFP. I’m just going to show you the solution that I gave my friend. But I’m pretty sure that the subheader I just wrote gives you the key to all of the possible solutions: don’t look at the situation of the group breaks based on the parent. Go to your lowest level — in this case, the transaction level — and think about the group breaks from that point of view.
In my solution, I first looked at all the detail rows and figured out which ones were the “header rows” for each parent; that is, which child row was the first row in each period, for its parent.
SELECT *, RECNO() As Item FROM Child2 ;
ORDER BY parentKey, PeriodStart, TransactionName ;
INTO CURSOR AllChild2Rows
* the order here must match the reporting order, whatever
* that turns out to be
SELECT parentKey, MIN(Item) As TopItem FROM ;
AllChild2Rows ;
GROUP BY parentKey ;
INTO CURSOR Child2HeaderRows
… now we have a fighting chance in hell of creating a ‘child driver’ with information about which children (the header rows) should be paired with the aggregate information, like this:
SELECT AllChild2Rows.*, “1” As KeyValue FROM AllChild2Rows INNER JOIN ;
Child2HeaderRows on AllChild2Rows.parentKey = Child2HeaderRows.parentKey ;
AND AllChild2Rows.Item = Child2HeaderRows.TopItem ;
UNION ;
SELECT AllChild2Rows.*, “0” As KeyValue FROM AllChild2Rows LEFT OUTER JOIN ;
Child2HeaderRows on AllChild2Rows.parentKey = Child2HeaderRows.parentKey ; “
AND AllChild2Rows.Item = Child2HeaderRows.TopItem ;
WHERE Child2HeaderRows.TopItem IS NULL ;
INTO CURSOR Child2Details
… the critical point here is to create a new key value on which we can group break, which will serve both masters, er, children. Once we have that, we can relate and order our tables properly for the report:
SELECT Child2Details
* Child2Details may be the youngest member of the family,
* but he’s our designated driver today…
INDEX on parentKey + DTOS(PeriodStart) + DetailItemName TAG rptOrder
SET RELATION TO parentKey INTO Parent
SET RELATION TO TRANSFORM(parentKey) + “-” + KeyValue INTO Header ADDITIVE
SET RELATION TO TRANSFORM(parentKey) + DTOS(PeriodStart) INTO Child1 ADDITIVE
* we’re ready to set up for our report,
* and we can get rid of some of the
* scaffolding that the report won’t actually be using
* in our environment
USE IN AllChild2Rows
USE IN Child2HeaderRows
USE IN Child2
The result makes the data look the way we need it to look to emulate what’s expected in the “real world” of this originally relational data. Notice the disparity of the periods represented in the “header rows” with the period actually represented in the first child row for the parent. This is possible because we’re not using the period to relate the tables, only our calculated key.
(When you look at these images, remember that the Report Engine is going to SET SKIP to all three of the children for us by default, but it isn’t really going to do anything for two of the children in this case. So, to make the situation clearer, I’ve just SET SKIP to the one child where it counts.)
We’re not quite done
So much for the data problem. Now we have to get the report to do what we want. There will be a number of gratuitous screenshots in here, partly for extra clarity, but mostly because I love C’s dialog designs. 😉
Of course you know the MD feature has been tapping its foot impatiently, wondering “when do I get my starring role? when is it MY turn?” And, now, of course it will try to cause problems when it has a chance. But it’s really not too bad.
To get this report to work as planned, we need to group on ParentKey (obviously) and, within that, on Period Start (so that the “true” details in the second detail band can have their expected header and footer at the correct places). So far, so good, and nothing to do with MD.
Now we have to create that first detail band, where our aggregate data will show up, with a Target Alias Expression of “Header”. This isn’t too much of a big deal.
In the first detail band, we’re going to have to make sure our header values show only where we need them to show. We can do this by using a Print When that points up the value of our calculated Key Value, and removing blank lines to close up the resulting extra space.
Finally, there are a couple of tricky bits in the second detail band (which you would think would be straightforward).
First, the summary line for the details isn’t in the detail footer, it’s in the group footer. Remember, we’ve grouped on the period, which is just want we want for our detail.
Second, you can’t just calculate the sum of the detail item values and get the right result. This is because the SET SKIP for that header cursor is going to interfere (the value for the first line is going to get summed in for as many times as there are periods for the parent).
I suppose you actually could make this work using Calculate, with an IIF() and a check of the calculated key value … but I didn’t think it was worth it when we already had those aggregated values … so, I just used the correct value from the Child1 relationship, instead.
Et voilà.
As I’m sure the poor little MD feature has been waiting all its poor little life to say… who’s your daddy now?
