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.
]]>