Lesson 6: Updating the Employees Dataset with a Dynamic Query 

  • Article

 

The report should now function correctly using the static query in the Employees dataset. The following steps will replace the static query with a dynamic query.

Dynamic queries are evaluated as expressions. When a query is constructed as an expression, you cannot use the Run (!) command in Query Designer to verify the syntax and confirm that it returns the results you expect. If you want to check the syntax, preview the report to ensure that the processed report includes data.

To add a dynamic query to a report definition

  1. Click the Data tab.

  2. Select the Employees dataset, and then use the generic query designer to replace the original query with the following expression:

    ="SELECT c.firstname, c.lastname, e.title, d.departmentID " &
"From HumanResources.EmployeeDepartmentHistory D " &
"INNER JOIN HumanResources.Employee E " &
"ON D.EmployeeID = E.EmployeeID " &
"INNER JOIN Person.Contact C " &
"ON E.ContactID = C.ContactID " &
Iif(Parameters!Department.Value = 0, "",  "WHERE D.DepartmentID = " & Parameters!Department.Value) & 
"ORDER BY C.LastName"

    This expression results in a query that includes a WHERE clause only if All is not selected. The WHERE clause includes the value from the Department parameter. You must use the generic query designer to create an expression.

  3. Save the report project. On the File menu, click Save All.

  4. Click the Preview tab. By default, all employees are displayed. When you select a specific department and view the report, employees from that department are displayed.

    Note   Each time you run the report, a query string is built using the expression in the Employees data set. When you select All (internally, a value of 0 for DepartmentID), the query is built without a WHERE clause, which results in all employees being retrieved. When you select a department, the query is constructed using the selected department's DepartmentID in the WHERE clause, limiting the data to that department.

Next Lesson

This step concludes the tutorial. For more information about other Reporting Services tutorials, see Reporting Services Tutorials.

See Also

Concepts

Tutorial: Using a Dynamic Query in a Report

Other Resources

Using Dynamic Queries
Retrieving Data from a Data Source

Help and Information

Getting SQL Server 2005 Assistance