Complex Reporting: Part 1 - Introduction

Recently I got the chance to build a report using Microsoft SQL Server Reporting Service 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:

1. What needs to be displayed is so simple in concept, that you get misled into thinking it’s easy.
2. To do this you need to use a component of SRS which is seldom needed, sub-reports.

But before I give away too much let me explain what the report should show, the customer I am using in this series, tracks the deals for commodities over a three year period, broken down to each quarter. So they want to know in year 1, quarter 1 they sold x values worth of beer, and so on for all 12 quarters. As they are constantly adding new commodities the year 1 is not the same for each commodity. So they may start selling beer in 2008 and chickens in 2009, so they would only sell beer until 2011 but chickens would be sold until 2012. And all we need is a report to show this.
Disclaimer: Most of this has been changed to protect the innocent, so the report images are drawn in Visio and are not real images, the customer doesn’t sell commodities (especially not coffee and beer) and the data is completely faked.

So lets look at the data structure, it’s a simple single table (no I didn’t have anything with the design of this), which has a Deal field for the name of commodity; Fiscal1, Fiscal2, Fiscal3 store the years that relate to each year we are tracking and the FxQxValue is the value for that year and quarter.

Lets look at how this would look with sample data, this is the same data we will use for the rest of the series:

Next time we will look at the report requirements itself and start to look at how to build it.