articles >> business intelligence >> Same Report but Different Methods in SQL ...

Same Report but Different Methods in SQL Server Reporting Services

By : Dinesh Asanka Mar 12, 2008

Page 3 / 3


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.