Tutorial: 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 tutorial, you will create a project, create two datasets, add a table and parameters, and alter one of the queries to use a dynamic query.
The report in this tutorial contains a table that lists all employees for a particular department, or all employees in the company, depending on a parameter value. The query used by the table will change dynamically through an expression. 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, expression-based query can.
Requirements
This tutorial requires that you use the generic query designer to create the queries. The generic query designer is the default query design tool for SQL Server data sources. The generic query designer is enabled when you toggle the generic query designer button on the Report Designer toolbar:
.gif)
Your system must have the following installed to use this tutorial:
- SQL Server 2005 Reporting Services.
- SQL Server 2005 with the AdventureWorks OLTP database.
- SQL Server 2005 Business Intelligence Development Studio.
You must also have permissions to retrieve data from the AdventureWorks database.
Note
When reviewing tutorials it is recommended you add next and previous buttons to the document viewer toolbar. For more information, see Adding Next and Previous Buttons to Help.
Tasks
Lesson 1: Creating a New Report
Lesson 2: Creating the Employees Dataset
Lesson 3: Creating the Departments Dataset
Lesson 4: Defining a Report Layout
Lesson 5: Adding a Report Parameter
Lesson 6: Updating the Employees Dataset with a Dynamic Query
See Also
Concepts
Other Resources
Using Dynamic Queries
Query Design Tools in Reporting Services
Report Samples (Reporting Services)
Installing AdventureWorks Sample Databases and Samples