Complex Reporting: Part 1 - Introduction
Recently I got the chance to build a report using Microsoft SQL Server Reporting Services 2005 for a customer—that, in itself, is nothing fantastic. However, this was one of the most complex reports I have ever had to build, for two main reasons:
- What needs to be displayed is so simple in concept that you get misled into thinking it’s easy.
- To do this, you need to use a component of SRS that is seldom required: sub-reports.
But before I give too much away, let me explain what the report should show. The customer I’m using in this series tracks deals for commodities over a three-year period, broken down by quarter. So they want to see, in Year 1, Quarter 1, how much they sold in terms of value (e.g., beer), and so on for all 12 quarters. However, since they’re constantly adding new commodities, Year 1 is not the same for each. For example, they might have started selling beer in 2008 and chickens in 2009, meaning beer is only tracked until 2011 but chickens until 2012. All we need is a report to display this.
Disclaimer: Most of this has been changed to protect the innocent, so the report images are drawn in Visio and are not real; the customer doesn’t actually sell commodities (especially not coffee and beer), and the data is completely faked.
Now, let’s look at the data structure—it’s a simple single table (no, I didn’t have anything to do with the design of this). It includes:
- A Deal field for the commodity name,
- Fiscal1, Fiscal2, Fiscal3 (storing the years being tracked for each commodity), and
- FxQxValue (the value for that year and quarter).
Let’s look at how this would look with sample data—this is the same dataset we’ll use for the rest of the series.
Next time, we’ll examine the report requirements and start building it.