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.