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.


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:

Icon of the Generic Query Designer button

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.


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.




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


Reporting Services Tutorials

Other Resources

Using Dynamic Queries
Query Design Tools in Reporting Services
Report Samples (Reporting Services)
Installing AdventureWorks Sample Databases and Samples

Help and Information

Getting SQL Server 2005 Assistance