Filtering Techniques for SQL Server 2008 R2 Reports

It is important to consider to filter report data based on user’s selection criteria as many databases contain large amounts of data. This post discusses different techniques to filter report data using report parameter concepts. By using the filters on reports, it greatly reduces the network traffic and helps the report to render efficiently.

We are using same data source and query that we used to demonstrate the tabular reports demo. You can read the post  here.

Filtering by query -  You can filter the records in query by adding a where clause to it. Write the following query in query designer as follows

   1: Select CalendarYear,SalesTerritoryRegion, SalesAmount 

   2: FROM 

   3:  FactResellerSales as F INNER JOIN DimDate as D ON

   4:  F.OrderDateKey = D.DateKey INNER JOIN DimSalesTerritory as ST

   5:  ON F.SalesTerritoryKey = ST.SalesTerritoryKey

   6: WHERE

   7: D.CalendarYear = 2006

   8:  order by CalendarYear , SalesTerritoryRegion

Defining parameters – There are two different types of parameters which you can use in report design.

• Data Set Parameters- can be defined using database objects like stored procedures and user defined functions.
• Report Parameters – are defined from ad hoc query or SQL stored procedure, but you don’t have to parameterize the queries to use parameters in a report unlike in Data Set Parameters.

The following query contains two parameters used to specify the range of date values for filtering a Date type field

   1: Select CalendarYear,SalesTerritoryRegion, SalesAmount 

   2: FROM 

   3:    FactResellerSales as F INNER JOIN DimDate as D ON

   4:    F.OrderDateKey = D.DateKey INNER JOIN DimSalesTerritory as ST

   5:    ON F.SalesTerritoryKey = ST.SalesTerritoryKey

   6:    WHERE D.CalendarYear BETWEEN @DateFrom AND @DateTo

   7:    order by CalendarYear , SalesTerritoryRegion

You can set the data type for the parameters that defined above as shown below

Run the report to see the result , you can notice a date picker control appears in report to select the date value.

Creating a list of parameters – It would be nice if you could provide user a list of values as parameter for selection. You can create this list using either Parameter properties dialogue box or use a query to return a list of available values.

To create a query-driven list, create a data set called Years under the same data source. Enter the following SQL statement as shown in the dialogue box

   1: SELECT DISTINCT CalendarYear FROM DimDate

   2: ORDER BY CalendarYear

To set up the parameter list , right click on the Year Parameter and select the Get values from a Report parameter properties dialogue box and select the Years from dataset dropdown and select the CalendarYear from value field list.

Configuring Multi-Value Parameters – You can configure parameters for users to select a multiple values. Modify the year by right clicking on parameter properties. Check Allow multiple values in the Report Parameter Properties dialogue and click ok.

run the query, you should be able to see the results as shown below

More about SQL Server 2008 Report parameters can be read here

Share this post :