Robert MacLean
11 August 2008
A sub report is a report which is embedded into another report, basically it’s SRS 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, then the sub-report then 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 5 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 basically the same.
What we can do is merge those five cells on the table together and insert a sub report into that merged cell, and since all the groups are the same they can all point to the same report. The exception is the horizontal total row, which is the same in look is calculated a little 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 so you can just parameters to it, and because it’s in a cell of a table you can just access the values from 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 as we used before:
If you read that and saw the last SELECT and went, WHOA, good for spotting it. What’s happening is that I always want a result regardless, so that I don’t get issues caused by missing fields. So by adding that and only selecting the top record I ensure that there is always a value, even if it is zero. The total column on the sub report is just a calculated field adding the four values up.
For the total row sub report, it’s basically the exact same except we are now wrapping the fields in SUM’s, 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 take into all the possibilities like so:
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 5 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 basically the same.
What we can do is merge those five cells on the table together and insert a sub report into that merged cell, and since all the groups are the same they can all point to the same report. The exception is the horizontal total row, which is the same in look is calculated a little 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 so you can just parameters to it, and because it’s in a cell of a table you can just access the values from 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 as 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 went, WHOA, good for spotting it. What’s happening is that I always want a result regardless, so that I don’t get issues caused by missing fields. So by adding that and only selecting the top record I ensure that there is always a value, even if it is zero. The total column on the sub report is just a calculated field adding the four values up.
For the total row sub report, it’s basically the exact same except we are now wrapping the fields in SUM’s, 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 take into 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 our drop down we mentioned earlier and we use a little bit of SQL to get it into an INT and manipulate it to give us every possible combination.