Complex Reporting: Part 2 - Report Requirements

In part 1 we looked at the requirements at a high level and also looked at the data structure, in part 2 we will now look at the finer details of the requirement. First the user needs to be able select from a drop down, which contains a list of all possible fiscal years, the first fiscal year they want to see, next the report must show blank spaces for commodities which do not feature in that fiscal, but may feature in future fiscals and lastly we need sub totals for years and commodities.
So the drop down is a simple SRS parameter which is connected to a data set which does a simple SQL command:

SELECT DISTINCT Fiscal1 AS Fiscal FROM Deals 
UNION
SELECT DISTINCT Fiscal2 AS Fiscal FROM Deals 
UNION
SELECT DISTINCT Fiscal3 AS Fiscal FROM Deals 

and then set the value and name of the drop down to the Fiscal field.
The next requirement is actually telling us something very subtle, which is at the core of the complexity. That is that if I choose FY06, the report will render FY06, FY07 and FY08 and that if a commodity starts in any of those years it must be shown. So we are not ONLY showing what starts in FY06, we are showing things that start in FY07 and FY08 too. This means our final result should look like:


If you are a regular SRS developer your gut is telling you that it is just a table, or maybe a matrix, unfortunately it’s a little more than that. But we will look at that next time.
The last requirement, sub totals, is very easy and logical (especially looking at the image above) so I am not going to go into detail about that.