Introduction
SQL Server Reporting Services (SSRS) in SQL Server 2005 has given users many options to on how to create a report. This article describes several methods that can be used to develop the same report. In addition, we will analyse the performance of each method so that we know the pros and cons of each option. Whenever we have the option of selecting a method we need to ensure that we select the right one.
What do you need to have
The first thing you will need is a basic understanding of how to create a simple reports using SSRS. It will be an advantage if you also know how to create SubReports. In this article I will provide a basic explaination for those that are yet to use SSRS.
The examples that I am going to use are based on the AdventureWorks database. So you will need to make sure that you have the AdventureWorks database available on your SQL Server installation.
Software wise you will need either SQL Server Enterprise, Developer, Standard or Work Group edition along with SQL Server Business Intelligence Development Studio (BIDS) which you will use to create the SSRS reports.
Business Case
Let’s say we need to compare the sales figures fof the last four years for a product class, product colour and product category. The Report format may looks like following:
|
Type / Year |
2001 |
2002 |
2003 |
Total |
|
XXX |
9,9999,999.99 |
9,9999,999.99 |
9,9999,999.99 |
9,9999,999.99 |
|
XXX |
9,9999,999.99 |
9,9999,999.99 |
9,9999,999.99 |
9,9999,999.99 |
|
Total |
9,9999,999.99 |
9,9999,999.99 |
9,9999,999.99 |
9,9999,999.99 |
There may also be a need for a graph to present the above data.
In this report the user should be able to select the type he wants, whether it is class, colour or category. If the user selects colour then the first column should contain the colour with the corresponding value for the year.
Consider the following table relationships in the AdventureWorks database:
By joining the Sales.SalesOrderHeader, Sales.SalesOrderDetail, Prodcuction.Product and Production.ProductCategory tables you will be able to find the necessary data.
Will will now examine how we can use SSRS to deliver a report to satisfy the business requirement.