Complex Reporting: Part 2 - Report Requirements
In part 1, we looked at the requirements at a high level and also examined the data structure. In part 2, we’ll now dive into the finer details.
First, the user must be able to select from a dropdown containing all possible fiscal years—the first fiscal year they want to see. Next, the report should display blank spaces for commodities that don’t feature in that fiscal year but may appear in future fiscals. Lastly, we need subtotals for years and commodities.
The dropdown is a simple SRS parameter, linked to a dataset executing this 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
Then, set the dropdown’s value and name to the _Fiscal field.
The next requirement reveals something subtle at the core of the complexity: If a user selects FY06, the report should render FY06, FY07, and FY08. If a commodity appears in any of those years, it must be shown. We’re not just displaying what starts in FY06—we’re also showing commodities that begin in FY07 and FY08.
Thus, the final output should resemble:
If you’re a regular SRS developer, your instinct might tell you this is just a table—or perhaps a matrix. Unfortunately, it’s slightly more than that. (We’ll explore that next time.)
The last requirement—subtotals—is straightforward and logical (especially considering the image above), so I won’t elaborate further.