Walkthrough - Using a Dynamic Query in a Report
This advanced tutorial is designed to help you understand how you can use dynamic queries in report design. In this walkthrough, you will create a project, create two datasets, add a table and parameters, and alter one of the queries to use a dynamic query.
Your system must have the following installed to use this tutorial:
- Microsoft SQL Server 2000 Reporting Services.
- SQL Server 2000 with the AdventureWorks2000 OLTP database.
- Microsoft Internet Explorer 6.0 or later.
- Microsoft Visual Studio® .NET 2003 with Report Designer.
You must also have permissions to retrieve data from the AdventureWorks2000 database. This walkthrough assumes that you know how to create reports, datasets, tables, and parameters, and how to use the generic query designer. For information about these features, see the documentation about these features.
Using a dynamic query, you can create a tabular report that lists all employees for a particular department, or all employees in the company, depending on a parameter value. Although a static query can filter employees through a query parameter, it cannot alter the structure of the query (in this case, remove the WHERE clause in order to display all employees). A dynamic query can.
The following walkthrough is based on the AdventureWorks database. To create a report that uses a dynamic query, do the following:
- Create a blank report.
- In Data view, create a dataset named Employees that uses an AdventureWorks data source. This dataset is used by the main table in the report. In this dataset, type the following query:
SELECT FirstName, LastName, Title FROM Employee WHERE (DepartmentID = 1) ORDER BY LastName
This query will be changed later to an expression; creating a basic query first will allow Report Designer to automatically create a fields list. If you begin by writing an expression, you have to manually update the fields list.
Note If there are no other data sources in the report or the project, the first data source you create is named after the database you selected. To change the name of the dataset, click the Edit Selected Dataset (...) button on the toolbar and then, in Name, type Employees.
- Create a dataset named Departments that uses an AdventureWorks data source. This dataset is used by the parameter list. In this dataset, type the following query:
SELECT 0 AS DepartmentID, 'All' AS Name UNION SELECT DepartmentID, Name FROM Department ORDER BY Name
This UNION query creates a list of departments that includes the word 'All' at the top of the list.
Note The graphical query designer does not support UNION queries. You must use the generic query designer to edit UNION queries.
- In Layout view, add a table, and then place the fields from the Employees dataset (FirstName, LastName, and Title) into the detail cells of the table.
- Create a report parameter and then do the following:
- For Name and Prompt, type Department.
- For Data type, select String.
- Clear Allow null value and Allow blank value.
- For Available values, select From query.
- For Dataset, select Departments. For Value field, select DepartmentID. For Label field, select Name.
- Preview the report. The table should display a limited list of employees based on the static query. The parameter does not filter data at this point.
- In Data view, select the Employees dataset, and then use the generic query designer to replace the original query with the following expression:
="SELECT FirstName, LastName, Title FROM Employee" & IIf(Parameters!Department.Value = 0,""," WHERE (DepartmentID = " & Parameters!Department.Value & ")") & " ORDER BY 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.
Note The expression must be a single line. If the query was formatted by the graphical query designer, remove the carriage returns and extra spaces.
- Preview the report. When you select All, all employees are displayed. When you select a specific department, employees from that department are displayed.