Parameter as Filter in SSRS

            If we want to make a Dataset as Shared Dataset---Datasource also be a sharedDataSource.

Query :

Select DimDate.CalendarYear,DimReseller.BusinessType,SUM(FactResellerSales.SalesAmount) as TotalSales,
DimDate.EnglishMonthName,DimDate.CalendarQuarter,SUM(FactResellerSales.TaxAmt) as Totaltax
From DimReseller INNER JOIN
FactResellerSales ON DimReseller.ResellerKey=FactResellerSales.ResellerKey INNER JOIN
DimDate ON FactResellerSales.OrderDatekey=DimDate.DateKey
Group by DimDate.CalendarYear,DimReseller.BusinessType,DimDate.EnglishMonthName,DimDate.CalendarQuarter

   

shareddataset has query/storeprocedure other datasets which are refereced this query/stroedprocedure.

Filters:  We can apply Filters at while design Dataset itself.

without filter Yearwise getting the matrix report: 

1. Apply Filter at Dataset level.

Dataset--Properties--> Filters 

output :

2. Adding Filter at DataResign level.( Matrix Report level)

            We can apply Filter at Runtime---end user prospective also.

then Apply Filter

output for both is same. we have wonderful advantage is Tablix data region Filter is upto that resigion only. which should not effect other data region.

Example :  Add a table ---CalenderYear wise then--set visiablity property also.

 set visiablity property for--DrillDown --option

output :

we can apply Filter at Group level also

Now done(Dataset,DataRegions,Grouplevel Filter) .-----done at report design time itself. 

Best Practice : 

                  we given option to end user to apply at runtime this Filter option. for this delete existing applied Fitler from above Matrix report--Tablix Filter

now we created a parameter--@Year --with 4 year( 2005,2006,2007,2008).

Now we can apply this parameter at any level --same as above(Dataset,Tablix,Grouplevel)

Dataset level :

=Parameters!Year.Value

output

Apply @Year at Tablix level also which doesn' effect other regions.

Query :

SELECT        DimDate.CalendarYear, DimReseller.BusinessType, SUM(FactResellerSales.SalesAmount) AS TotalSales, DimDate.EnglishMonthName, DimDate.CalendarQuarter, SUM(FactResellerSales.TaxAmt)
                         AS TotalTax
FROM            DimReseller INNER JOIN
                         FactResellerSales ON DimReseller.ResellerKey = FactResellerSales.ResellerKey INNER JOIN
                         DimDate ON FactResellerSales.OrderDateKey = DimDate.DateKey
GROUP BY DimReseller.BusinessType, DimDate.CalendarYear, DimDate.EnglishMonthName, DimDate.CalendarQuarter