• Article

Lesson 1: Adding Parameters to Filter Reports by Date (SSRS)

By including parameters for a start and end date in a query for your report, you can specify a date range that limits the data retrieved from the data source. You can create additional parameters to filter the data after it is retrieved from the data source.

In this lesson, you will add the parameters @StartDate and @EndDate to the query to limit the data retrieved from the data source. Two report parameters, StartDate and EndDate, are automatically created for you and appear in the Report Data pane. Parameters are case-sensitive. Query parameters begin with the @ symbol, whereas report parameters do not.

You will set the data type of the parameters to DateTime and see that a calendar control appears with the parameter text box on the report viewer toolbar. You will set default values for the parameters so that the report can run automatically. Finally, you will create a report parameter DayofWeek that is not bound to a query parameter and use it to filter data after the data is retrieved from the data source.

This tutorial requires that you have completed Tutorial: Creating a Basic Table Report (SSRS).

To open an existing report server project

  1. Click Start, point to All Programs, point to Microsoft SQL Server 2008 R2, and click Business Intelligence Development Studio.

  2. From the File menu, point to Open, and click Project/Solution.

  3. Click on Tutorialand then choose Tutorial.sln. This is the tutorial that was created in the Tutorial: Creating a Basic Table Report (SSRS).

  4. Click OK to open the project. The Tutorial project is displayed in Solution Explorer with one report called Sales Orders.rdl.

    Note   If Solution Explorer is not visible, from the View menu, click Solution Explorer.

To convert an embedded data source to a shared data source

  1. In the Report Data pane, right-click the data source AdventureWorks and select Convert to Shared Data Source. A data source called AdventureWorks.rds is added in Solution Explorer.

  2. In the Report Data pane, right-click the AdventureWorks data source and select Data Source Properties.

  3. In Name, type AdventureWorks_Ref.

  4. Click OK.

To replace the existing dataset

  1. In the Report Data pane, right-click the dataset AdventureWorksDataset, and then click Dataset Properties.

    Note

    If you do not see the Report Data pane, from the View menu, click Report Data.

  2. In Data source, verify that AdventureWorks_Ref is selected.

  3. In Query type, verify that Text is selected.

  4. Click the Query Designer button to open the query designer.

  5. Replace the text with the following query into the text box.

    SELECT 
   soh.OrderDate AS [Date], 
   soh.SalesOrderNumber AS [Order], 
   pps.Name AS Subcat, pp.Name as Product,  
   SUM(sd.OrderQty) AS Qty,
   SUM(sd.LineTotal) AS LineTotal
FROM Sales.SalesPerson sp 
   INNER JOIN Sales.SalesOrderHeader AS soh 
      ON sp.BusinessEntityID = soh.SalesPersonID
   INNER JOIN Sales.SalesOrderDetail AS sd 
      ON sd.SalesOrderID = soh.SalesOrderID
   INNER JOIN Production.Product AS pp 
      ON sd.ProductID = pp.ProductID
   INNER JOIN Production.ProductSubcategory AS pps 
      ON pp.ProductSubcategoryID = pps.ProductSubcategoryID
   INNER JOIN Production.ProductCategory AS ppc 
      ON ppc.ProductCategoryID = pps.ProductCategoryID
GROUP BY ppc.Name, soh.OrderDate, soh.SalesOrderNumber, 
   pps.Name, pp.Name,    soh.SalesPersonID
HAVING (ppc.Name = 'Clothing' 
   AND (soh.OrderDate BETWEEN (@StartDate) AND (@EndDate)))

    This is the same query as before, except that a condition with two limiting parameters has been added:

    AND (soh.OrderDate BETWEEN (@StartDate) AND (@EndDate))

  6. Click Run (!) on the toolbar. The Define Query Parameters dialog box opens to prompt you for parameter values.

  7. Provide two values to see a filtered result set:

    1. In the Parameter Value column, enter a value for @StartDate, for example, 1/31/2001.

    2. In the Parameter Value column, enter a value for @EndDate, for example, 1/31/2003.

  8. Click OK. 

  9. The result set displays a filtered dataset for orders in the years 2001 and 2002.

  10. Click OK twice. The Report Data pane is populated with the dataset fields. Also note that two report parameters, StartDate and EndDate, are automatically created and appear under the Parameters node.

After you have defined query parameters for the report, you have to change the data type of the report parameters to match the data type of the source data. The default is Text, which maps to the String data type in most data sources. If a source data is numeric, Boolean, or Date/Time, you have to change the report parameter data type.

To change the data type and default values of a report parameter

  1. In the Report Data pane, expand Parameters, and double-click StartDate. The Report Parameter Properties dialog box opens.

  2. Verify that the name of the parameter is StartDate and the prompt is Start Date.

  3. In Data type, select Date/Time.

  4. Click OK.

  5. In the Report Data pane, double-click EndDate. Verify the name and prompt values.

  6. In Data type, select Date/Time.

  7. Click OK. 

  8. Click Preview. The StartDate and EndDate parameters each appear on the report toolbar with a calendar control. Calendar controls automatically appear when the parameter is data type Date/Time and you have not defined an available values list. If you define an available values list, a drop-down list of values appears instead.

  9. Provide two parameter values to run the report:

    1. In the StartDate parameter text box, enter the date 1/31/2001.

    2. In the EndDate parameter text box, enter the date 1/31/2003.

  10. Click View Report. The report displays only the data that falls within the report parameter values.

After you have created report parameters for the report, you can add default values for those parameters. Default parameters allow the report to run automatically; otherwise, a user must enter parameter values to run the report.

To set default values for parameters

  1. In Design view, in the Report Data pane, expand Parameters, and double-click StartDate. The Report Parameter Properties dialog box opens.

  2. Click Default Values.

  3. Select the Specify values option. The Add button and an empty Value grid appears.

  4. Click Add. An empty row is added to the grid.

  5. Click in the Value text box and delete the default text (Null).

  6. Type 1/31/2001. Click OK. 

  7. In the Report Design pane, and double-click EndDate.

  8. Click Default Values.

  9. Select the Specify values option.

  10. Click Add.

  11. Type 1/31/2003. Click OK.

  12. Click Preview. The report runs immediately because there are default values defined for all parameters.

To add a new field to the query to use for filtering

  1. Switch to Design view.

  2. Right-click the dataset AdventureWorksDataset and select Dataset Properties. Open the query designer and replace the query with the following new query:

    SELECT 
   soh.OrderDate AS [Date], DATENAME(weekday, soh.OrderDate) as Weekday,
   soh.SalesOrderNumber AS [Order], 
   pps.Name AS Subcat, pp.Name as Product,  
   SUM(sd.OrderQty) AS Qty,
   SUM(sd.LineTotal) AS LineTotal
FROM Sales.SalesPerson sp 
   INNER JOIN Sales.SalesOrderHeader AS soh 
      ON sp.BusinessEntityID = soh.SalesPersonID
   INNER JOIN Sales.SalesOrderDetail AS sd 
      ON sd.SalesOrderID = soh.SalesOrderID
   INNER JOIN Production.Product AS pp 
      ON sd.ProductID = pp.ProductID
   INNER JOIN Production.ProductSubcategory AS pps 
      ON pp.ProductSubcategoryID = pps.ProductSubcategoryID
   INNER JOIN Production.ProductCategory AS ppc 
      ON ppc.ProductCategoryID = pps.ProductCategoryID
GROUP BY ppc.Name, soh.OrderDate, soh.SalesOrderNumber, 
   pps.Name, pp.Name,    soh.SalesPersonID
HAVING (ppc.Name = 'Clothing' AND (soh.OrderDate BETWEEN (@StartDate) AND (@EndDate)))

    In the query, an additional calculated column is defined for the day of the week a sale occurred by adding the following command to the SELECT statement:

    DATENAME(weekday, soh.OrderDate) as Weekday.

  3. Click Run (!).The Define Query Parameters dialog box opens.

  4. In the Parameter Value column, enter a value for @StartDate, for example, 1/31/2001.

  5. In the Parameter Value column, enter a value for @EndDate, for example, 1/31/2003.

  6. Click OK. You should see a new column in the result set labeled Weekday.

  7. Click OK twice. In the Report Data pane, verify that Weekday is a field.

(Optional) To format the date in the table data that will be filtered

  1. Click the Design tab.

  2. Right-click the cell with the [Date] field expression and then click Text Box Properties.

  3. Click Number, and then in the Category field, select Date.

  4. In the Type box, select Monday, January 31, 2000.

  5. Click OK.

To add a new report parameter

  1. In Design view, in the Report Data pane, click New, and then click Parameter. The Report Parameter Properties dialog box opens.

  2. In Name, type DayoftheWeek.

  3. In Prompt, type Filter on the day of the week:.

  4. Verify that the data type is Text.

  5. Click Default values.

  6. Select the Specify values option. The Add button and an empty Value grid appears.

  7. Click Add.

  8. Type Friday.

  9. Click OK.  

To set a table filter with a parameter expression

  1. In Design view, right-click on a row or column handle of the table and select Tablix Properties.

    Note

    The Table data region is a template based on a Tablix data region.

  2. Click Filters. An empty filter grid appears.

  3. Click Add. An empty row is added to the grid.

  4. In Expression, from the drop-down list, select [Weekday].

  5. Verify that Operator displays the equal sign (=).

  6. Click the expression (fx) button next to the Value text box. The Expression dialog box opens.

  7. In Category, click Parameters. The current list of parameters appears in the Values pane. Double-click DayoftheWeek. The parameter expression is added to the expression text box. The following expression now appears in the expression text box: =Parameters!DayoftheWeek.Value.

  8. Click OK. Click OK again to exit the Tablix Properties dialog box.

    The filter for the table is now set to compare the value in the field Weekday with the parameter value for DayoftheWeek. For example, when you enter the value Friday in the report toolbar for DayoftheWeek, the report processor will process only those rows in the table where the value for the field Weekday is Friday.

    Click Preview. Because all parameters have default values, the report runs automatically. The table shows only values that fall within the date range defined by StartDate and EndDate and that fall on a Friday.

Next Steps

You have successfully defined query parameters and report parameters, set default values for the parameters, and set a filter on the table. In the next lesson, you will learn how to create an available values, or valid values, list for a parameter. See Lesson 2: Adding Parameters to Create a List of Available Values (SSRS).