Same Report but Different Methods in SQL Server Reporting Services

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.



Method 1 : SubReports
A SubReport will be the easiest way of doing it. In this case we need to create one main report (MainReport.rdl) where the user select the report type they want and three sub reports for colour (colorreport.rdl), class (classreport.rdl) and subcategory (SubCategoryReport.rdl).

First we create the SubReport. Let us start with the colour SubReport.

After creating the colour report, we need to add a data set for the report and for that we will use the following query:

SELECT YEAR(SOH.OrderDate) Year
,Color
,SOD.LineTotal
FROM Sales.SalesOrderDetail SOD
INNER JOIN production.product PROD
ON SOD.Productid = PROD.ProductID
INNER JOIN Sales.SalesOrderHeader SOH
ON SOH.SalesOrderID = SOD.SalesOrderID


From the report format, we can imagine that this report needs a matrix rather than table, as the report has data dependent columns and rows. You can see that Year is also a variable as well as the color.

After dragging a matrix control to the report body, drag the Year column to Columns and Colour to Rows. Using the matrix it is very easy to provide a total column. Simply right click the Year column and select subtotal which will provide the sub total of the column, nwo do the the same to the Color column.

Next we will add a graph. Drag the graph control from the tool box and then drag year to the series field, color to category field and Line total to the data field. After doing this you should have a report layout as following:



To create the other sub reports for subcategory and class it is almost the same process as the color report. Here is the T-SQL queries for the datasets of class and subcategory respectively.

--Query for Product Class
SELECT YEAR(SOH.OrderDate) Year
,CASE
WHEN Class IS NULL THEN 'N'
ELSE Class
END class
,SOD.LineTotal
FROM Sales.SalesOrderDetail SOD
INNER JOIN production.product PROD
ON SOD.Productid = PROD.ProductID
INNER JOIN Sales.SalesOrderHeader SOH
ON SOH.SalesOrderID = SOD.SalesOrderID

--Query for Product Subcategory
SELECT YEAR(SOH.OrderDate) Year
,PSC.Name
,SOD.LineTotal
FROM Sales.SalesOrderDetail SOD
INNER JOIN production.product PROD
ON SOD.Productid = PROD.ProductID
INNER JOIN Sales.SalesOrderHeader SOH
ON SOH.SalesOrderID = SOD.SalesOrderID
INNER JOIN production.productsubcategory PSC
ON PSC.ProductSubCategoryID = PROD.ProductSubCategoryID
--Where Month(SOH.OrderDate) = 1

So now we have three sub reports ready. Now we have to create the main report to link to these subreports.

Let us see how to create the main report.

The main report will have report parameter so that a user can choose the report type they want.

For the sake of simplicity I have assigned the same values for both the label and value of the Type report parameter. After that drag and drop a sub-report component to the report body from the toolbox and select colorreport from the sub-report list in the General tab of the sub-report component as illustrated below:

Similarly, add another two sub-reports components to the reports and assign ClassReport and SubcategoryReport respectively. Now we need to display the sub-report one at a time. That will depends on the users selection. For this we need to add an expression to the Initially Visibility option at the Visibility tab of the sub-report component. It is a very simple expression which is =IIF(Parameters!Type.Value="Color",False,True). This means that the colour report will be visible only when the user has selected the Colour option. For Product class and subcategory sub-report add the =IIF(Parameters!Type.Value="Class",False,True) and =IIF(Parameters!Type.Value="SubCategory",False,True) expression respectively. You can extend the visibility option to the other two sub-reports as well.

Now if you run the Main Report, select the colour option from the type list and click the View Report button you will be able to see following report.

You can see that you can only view the data for colour and similarly you can verify the data for the other two parameters. The issue with this method is you have to have four reports. The more reports the more maintenance that is required.

Method 2 : Same Report Different Objects
Rather than having three sub-reports we can do this in one report.

We can include three datasets in the report. We can include three matrices and three charts. We can set the initial visibility option from an expression. For example, for the color option you can set the visibility expression like=IIF(Parameters!Type.Value="Color",False,True)

The issue with this report is the more controls used in the report the greater the complexity.

Method 3 : Same Report Using Switch
Having seen a method with four reports and another method with six controls, we would like to see a method with less controls and less reports.

There is a command called Switch in SSRS. We can write a query which comprises of all the columns we need.

SELECT
YEAR(SOH.OrderDate) Year
,PSC.Name
,CASE
WHEN Class IS NULL THEN 'N'
ELSE Class
END class
,Color
,SOD.LineTotal
FROM Sales.SalesOrderDetail SOD
INNER JOIN production.product PROD
ON SOD.Productid = PROD.ProductID
INNER JOIN Sales.SalesOrderHeader SOH
ON SOH.SalesOrderID = SOD.SalesOrderID
INNER JOIN Production.productsubcategory PSC
ON PSC.ProductSubCategoryID = PROD.ProductSubCategoryID

After creating this data set, drag two controls, a matrix and a chart to the report. The Linetotal field needs to be draged and droped in the detail box. The year column should be attached to the column header. Question will be what are we going to attached to rows because row will depend on the user's selection. In this we can use SWITCH command. So for the row you can enter following expression.

=SWITCH(
Parameters!Type.Value ="Class",Fields!class.Value,
Parameters!Type.Value ="SubCategory",Fields!Name.Value ,
Parameters!Type.Value ="Color",Fields!Color.Value )

You can see that depends on the selection, filed value is change. Apart from this we need to change the grouping of the matrix. It is nothing but same above expression. You have to enter above expression for the row grouping.

For the graphs, you need to have the same expression for series. That is all you need to complete the report. From this method we have only one report with two controls. So this will be the easiest way of developing a report.

Sample
If you need the sample for this article examples, please send me an email to dineshasanka@dbfriend.net and I would like to send the reports to the people who request it.