Write for Us
--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. << Prev Page Next Page>>
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.