• Article

Lesson 1: Adding Parameters to Filter Reports by Date (Report Builder 2.0)

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. In Transact-SQL, query parameters begin with the @ symbol, whereas report parameters as displayed in the Report Data pane 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 (Report Builder 2.0).

To open an existing report

  1. Click Start, point to Programs, point to Microsoft SQL Server 2008 Report Builder, and then click Report Builder 2.0.

  2. From the Report Builder button, click Open.

  3. Navigate to Sales Order. This is the report that was created in the Tutorial: Creating a Basic Table Report (Report Builder 2.0).

  4. Click Open. The report definition opens in design view.

    Next, you will replace the dataset query with a query that contains a query parameter.

To replace the existing dataset query

  1. In the Report Data pane, right-click the dataset DataSet1, and then click Query. The query designer opens.

  2. In the query pane, replace the text with the following query:

    SELECT
  SH.OrderDate
  ,SH.SalesOrderNumber
  ,SD.OrderQty
  ,SD.LineTotal
  ,P.Name AS [Product]
  ,PS.Name AS [Subcategory]
FROM
  Sales.SalesOrderHeader AS SH
  INNER JOIN Sales.SalesOrderDetail AS SD
    ON SH.SalesOrderID = SD.SalesOrderID
  INNER JOIN Production.Product AS P
   ON SD.ProductID = P.ProductID
  INNER JOIN Production.ProductSubcategory AS PS
   ON PS.ProductSubcategoryID = P.ProductSubcategoryID
  INNER JOIN Production.ProductCategory AS PC
   ON PC.ProductCategoryID = PS.ProductCategoryID
WHERE PC.Name = 'Clothing' AND SH.OnlineOrderFlag = 0
   AND (SH.OrderDate BETWEEN (@StartDate) AND (@EndDate))

    This is the same query as before, except that the sort ORDER BY clause has been removed and a condition with two limiting parameters has been added:

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

  3. Click Run (!) on the toolbar. The Define Query Parameters dialog box opens and prompts for a value for each parameter.

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

    1. In the Parameter Value column, enter a value for @StartDate, for example, 20010101.

    2. In the Parameter Value column, enter a value for @EndDate, for example, 20030101.

  5. Click OK. 

    The result set displays a dataset with values for orders in the years 2001 and 2002.

  6. Click OK. 

    In the Report Data pane, expand the Parameters node to view the two report parameters, StartDate and EndDate, that are automatically created from the query parameters.

    After you have defined query parameters that represent a date or time, you must change the data type of the report parameters to match.

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

  1. In the Report Data pane, right-click StartDate, and then click Parameter Properties.

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

  3. In Data type, select Date/Time. This sets the report parameter data type that is saved in the report definition.

  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 Run to preview the report. 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. You can use the calendar control or just type the following dates in:

    1. In the StartDate parameter text box, type 2001-01-01.

    2. In the EndDate parameter text box, type 2003-01-01.

  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 enable the report to run automatically; otherwise, a user must enter parameter values to run the report.

To set default values for parameters

  1. Switch to Design view.

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

  3. Click Default Values.

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

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

  6. In the Value text box, replace the default text (Null) and type 2001-01-01.

  7. Click OK. 

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

  9. Click Default Values.

  10. Select the Specify values option.

  11. Click Add.

  12. Type 2003-01-01.

  13. Click OK.

  14. Click Preview. The report runs immediately because each parameter has a default value.

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

  1. Switch to Design view.

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

    SELECT
  SH.OrderDate
  ,DATENAME(weekday, SH.OrderDate) as Weekday
  ,SH.SalesOrderNumber
  ,SD.OrderQty
  ,SD.LineTotal
  ,P.Name AS [Product]
  ,PS.Name AS [Subcategory]
FROM
  Sales.SalesOrderHeader AS SH
  INNER JOIN Sales.SalesOrderDetail AS SD
    ON SH.SalesOrderID = SD.SalesOrderID
  INNER JOIN Production.Product AS P
   ON SD.ProductID = P.ProductID
  INNER JOIN Production.ProductSubcategory AS PS
   ON PS.ProductSubcategoryID = P.ProductSubcategoryID
  INNER JOIN Production.ProductCategory AS PC
   ON PC.ProductCategoryID = PS.ProductCategoryID
WHERE PC.Name = 'Clothing' AND SH.OnlineOrderFlag = 0
   AND (SH.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, 20010101.

  5. In the Parameter Value column, enter a value for @EndDate, for example, 20030101.

  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.

    Now that the table has data that identifies the day of the week, you can add a filter so that only sales on specific of the week are used in the report. To help verify this, you can display the day of the week with the sales order date.

To format the date in the table

  1. Switch to Design view.

  2. Click the cell with the OrderDate field expression. On the Home tab, in the Number group, click the dialog box launcher.

  3. In Category, verify that Date is selected.

  4. In the Type box, select a date type that displays the day of the week. For example, for regional setting English (United States), you can select Monday, January 31, 2000.

  5. Click OK.

  6. Click Run to preview the report. The date in the first column of the table displays the day of the week as well as the calendar date.

    Next, you will add a parameter to enable the user to filter by the day of the week.

To add a new report parameter

  1. Switch to Design view.

  2. In the Report Data pane, click New, and then click Parameter. The Report Parameter Properties dialog box opens.

  3. In Name, type DayoftheWeek.

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

  5. Verify that the data type is Text.

  6. Click Default values.

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

  8. Click Add.

  9. Type Friday.

  10. Click OK. 

    Next, you will add a filter to display only the value you choose for the DayoftheWeek parameter. You can add a filter to the table or to the dataset. This report has both a table and chart that display the same data. Add the filter to the table if you want to display the filtered data in the table only. Add the filter to the dataset if you want both the table and the chart to display filtered data. In the following procedure, you will set the filter on the dataset.

To set a dataset filter with a parameter expression

  1. In Design view, in the Report Data pane, right-click DataSet1, and then click Dataset Properties.

  2. Click Filters.

  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. 

    Value displays the simple expression for the DayoftheWeek parameter: [@DayoftheWeek].

  9. Click OK. 

    The filter for the dataset 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 and chart show only values that fall within the date range defined by StartDate and EndDate and that fall on a Friday. For this dataset, only three dates fall on a Friday:

    • February 1, 2002

    • March 1, 2002

    • November 1, 2002

Next Steps

You have successfully defined query parameters and report parameters, set default values for the parameters, and set a filter on a dataset. 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 (Report Builder 2.0).

See Also

Other Resources

Tutorials (Report Builder 2.0)