Same Report but Different Methods in SQL Server Reporting Services

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.