Lesson 2: Adding Parameters to Filter by Date
New: 17 July 2006
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 query parameters @StartDate and @EndDate. You will see that case-sensitive report parameters StartDate and EndDate (without the query parameter indicator @) are automatically created for you. Note that different data providers use different query parameter indicators; for example, the Oracle data provider uses a colon (:) rather than the at (@) symbol.
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 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.
To add date parameters to your query
In Data view, select the AdventureWorks dataset.
Replace the existing query with the following query:
SELECT S.OrderDate, S.SalesOrderNumber, S.TotalDue, C.FirstName, C.LastName FROM HumanResources.Employee E INNER JOIN Person.Contact C ON E.ContactID = C.ContactID INNER JOIN Sales.SalesOrderHeader S ON E.EmployeeID = S.SalesPersonID WHERE (S.OrderDate BETWEEN (@StartDate) AND (@EndDate)) -- Accurate Enddate would be '20020228 23:59:59.997'Note
SQL Server stores DATETIME data accurate to one three-hundredth of a second. For more information, see Date and Time (Transact-SQL). If you do not receive the filtered results that you expected using a DateTime variable, you might need to use a more accurate time in your query parameter.
In this tutorial, you will use approximate datetime values.
Click Run (!) on the toolbar. The Define Query Parameters dialog box opens.
In the Parameter Value column, enter a value for @StartDate. For example, 20010101.
In the Parameter Value column, enter a value for @EndDate. For example, 20030101.
Click OK. The report parameters StartDate and EndDate are automatically created and default to data type String. In the next three steps, you will set these parameters to data type DateTime.
From the Report menu, click Report Parameters. The Report Parameters dialog box opens.
In the Parameters list box, verify StartDate is highlighted. From the Data type drop-down list, select DateTime.
In the Parameters list box, select EndDate. From the Data type drop-down list, select DateTime.
Click OK.
Click Preview. The StartDate and EndDate parameters each appear in the toolbar with a calendar control. Calendar controls automatically appear when the parameter is data type DateTime and you have not defined an available values list. Defining an available values list creates a drop-down list of values instead of a calendar control.
In this tutorial, you will enter a value for the data instead of using the calendar control.
In the StartDate parameter text box, enter the date 2001-01-01.
In the EndDate parameter text box, enter the date 2003-01-01.
Click View Report. The report displays only those data records that fall within the report parameter values.
After you have created query 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 for the report to run.
To set default values for date parameters
In Layout view, from the Report menu, click Report Parameters. The Report Parameters dialog box opens.
In the Parameters list box, verify StartDate is highlighted.
In the default values section, select Non-queried and type 2001-01-01. Press the TAB key to exit the text box.
In the Parameters list box, select EndDate.
In the default values section, select Non-queried and type 2003-01-01. Press the TAB key to exit the textbox.
Click OK.
Click Preview. The report runs immediately because there are default values defined for all parameters.
When report users run the report, they can set the defaults with parameter values that fall within the scope of the query parameters. For example, you can provide report parameter values that limit the rendered data to one month.
To add a new field to the query to use for filtering
In Data view, select the AdventureWorks dataset.
In the query, define an additional calculated column for the day of the week a sale occurred by adding the following command to the SELECT statement: DATENAME(weekday, S.OrderDate) as Weekday.
Replace the existing query with the following text:
SELECT S.OrderDate, DATENAME(weekday, S.OrderDate) as Weekday, S.SalesOrderNumber, S.TotalDue, C.FirstName, C.LastName FROM HumanResources.Employee E INNER JOIN Person.Contact C ON E.ContactID = C.ContactID INNER JOIN Sales.SalesOrderHeader S ON E.EmployeeID = S.SalesPersonID WHERE (S.OrderDate BETWEEN (@StartDate) AND (@EndDate))Click Run (!). You should see a new column labeled Weekday and the names of the days of the week.
In the Datasets window, verify that Weekday is a field.
Note
If the Datasets window is not visible, from the View menu, select Datasets.
You have now added an additional dataset field Weekday. When you manually create a report parameter and bind it to this field, users can filter report data by day of the week.
To add a new a report parameter
Click the Layout tab.
From the Report menu, choose Report Parameters. The Report Parameters dialog box opens.
Click Add. A new parameter, Report_Parameter_2, appears with default values.
In the Properties section, in the Name text box, replace Report_Parameter_2 with DayoftheWeek. Verify that the data type is String.
In Prompt, type Filter on which day of the week:.
Clear the Allow blank option.
In the Available values section, click Non-queried. Leave the entries blank.
In the Default values section, click Non-queried. In the text box, type Sunday.
Click OK.
To set a table filter with a parameter expression
In Layout view, select the table. The table appears with a grey outline.
Right-click on the table outline and select Properties. The Table Properties dialog box opens.
Click the Filters tab.
In the Expression column, from the drop-down list, select =Fields!Weekday.Value.
Verify that the Operator is the equal sign (=).
Click in the Value text box, and from the drop-down list, select <Expression>. The Edit Expression dialog box opens.
In the global variables pane, click Parameters. The current list of parameters appears in the opposite 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.
Click OK.
Click OK.
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 Sunday 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 Sunday.
Click Preview. Because all parameters have default values, the report automatically runs. The table shows only values that fall within the date range defined by StartDate and EndDate and that fall on a Sunday.
In the next optional procedure, you will add formatting to the table header.
(Optional) To set background and font color for the table header row
Click the Layout tab.
Click in the table. The table handles appear.
Click the table header row. From the layout toolbar, click the Background Color button. The Choose Color dialog box opens.
From the Web tab, choose DarkRed.
Click OK.
Next, choose the font color.
From the layout toolbar, click the Foreground Color button. The Choose Color dialog box opens.
From the Web tab, choose White.
Click OK.
Click Preview. The table renders with the colors chosen for the table header row.
Next Steps
You have successfully defined query parameters and report parameters, set default values for the parameters, and bound a report parameter to a calculated field. In the next lesson, you will learn to create an available values, or valid values, list for a parameter. See Lesson 3: Adding a Single Value Parameter with an Available Values List.
See Also
Tasks
Lesson 1: Creating a Report Server Project for the Parameter Tutorial