Complex Reporting: Part 4 - Introducing Sub-Reports

Published on 11 August 2008 • Estimated reading time: 3 minutes

A sub-report is a report which is embedded into another report—basically, it’s SRS’s answer to IFRAMEs. This should not be confused with the ability to drill through from one report to another, as those render separately and are provided separately. With sub-reports, the main report renders first, then the sub-report, and the output of all of those is combined to produce a single report. Sub-reports are normal SRS reports as well, so they have the same features as other reports.

So how do we use them? Well, if we look back at our previous image—where we had the fields scattered all over—there is a distinct pattern here: basically, there are five blocks (Q1, Q2, Q3, Q4, and Total) in a row under each fiscal year for each commodity/deal, and the horizontal total row at the end is essentially the same.

What we can do is merge those five cells in the table together and insert a sub-report into that merged cell. Since all the groups are the same, they can all point to the same report. The exception is the horizontal total row, which has the same look but is calculated slightly differently. So we only need two sub-reports, and we would structure it as follows:

So how does the sub-report know what to show? Well, remember—it’s a normal SRS report, so you can just pass parameters to it. Because it’s in a cell of a table, you can just access the values from the row that is being rendered. So all we need to do is pass two parameters: the fiscal year and the commodity. Now, the complexity is easy since it’s just a simple bit of SQL using the same UNION stuff we used before:

SELECT     f1q1value, f1q2value, f1q3value, f1q4value
FROM         Deals
WHERE     (deal = @projectid) AND (fiscal1 = @fiscal)
UNION
SELECT     f2q1value, f2q2value, f2q3value, f2q4value
FROM         Deals
WHERE     (deal = @projectid) AND (fiscal2 = @fiscal)
UNION
SELECT     f3q1value, f3q2value, f3q3value, f3q4value
FROM         Deals
WHERE     (deal = @projectid) AND (fiscal3 = @fiscal)
UNION
SELECT '0','0','0','0'

If you read that and saw the last SELECT and thought, "Whoa, good for spotting it!"—you’re on the right track. What’s happening is that I always want a result, regardless of whether there’s missing data. So by adding that line and only selecting the top record, I ensure there is always a value—even if it’s zero. The total column in the sub-report is just a calculated field that adds the four values up.

For the total row sub-report, it’s basically the same, except we wrap the fields in SUM()—that’s the only change.

The last thing to make sure of is that for the initial table on the main report, you get all the commodities for all the periods. To do that, your SQL needs to account for all the possibilities, like so:

SELECT     Deal
FROM        Deals
WHERE     ((fiscal1 = @FiscalYear)
        OR (fiscal2 = @FiscalYear)
        OR (fiscal3 = @FiscalYear)
        OR (CAST(RIGHT(fiscal2,2) AS INT) = CAST(RIGHT(@FiscalYear,2) AS INT) + 1)
        OR (CAST(RIGHT(fiscal2,2) AS INT) = CAST(RIGHT(@FiscalYear,2) AS INT) + 2)
        OR (CAST(RIGHT(fiscal3,2) AS INT) = CAST(RIGHT(@FiscalYear,2) AS INT) + 1)
        OR (CAST(RIGHT(fiscal3,2) AS INT) = CAST(RIGHT(@FiscalYear,2) AS INT) + 2))
ORDER BY Deal

@FiscalYear is the name of the dropdown we mentioned earlier, and we use a bit of SQL to convert it into an INT and manipulate it to give us every possible combination.