• Article

Filtering Report Data at the Source (Report Builder 2.0)

To filter data for a report dataset at the source, specify a query that includes only the data that you need for your report. Each data source type provides an associated query designer to help you build a query. You can build a query to include or exclude specific values, or you can create a parameterized query.

To filter data after it is retrieved for a report, you can create filter expressions for datasets, data regions, and groups. For more information, see Filtering Report Data in the Report (Report Builder 2.0).

For more information about query designers, see Query Designers (Report Builder 2.0).

Filtering Data in the Query

To filter data in the dataset query, you can include a restriction clause that limits the retrieved data by specifying values to include or exclude from the result set.

For Transact-SQL queries, in the relational query designer, you can add fields to the Filter pane to create automatically generate the restriction clause. In the text-based query designer, you can add a WHERE clause. For example, you can select sales within a specific territory or for products in a specific category. For more information, see "Filtering Rows by Using WHERE and HAVING" in the Reporting Services documentation in SQL Server Books Online.

For MDX queries, you can specify a filter in the Filter pane of the MDX query designer to specify dimensions and attribute hierarchies to include or exclude. For more information, see Analysis Services MDX Query Designer User Interface (Report Builder 2.0) and Creating Report Datasets from SQL Server Analysis Services (Report Builder 2.0).

Filtering Data by Using Parameters in the Query

When you include query parameters (also known as query variables) in a query, report parameters are automatically created and connected to the query parameters. This enables a user to select the data they want to see in the report.

You can create cascading parameters, where the user chooses a value for each parameter in a specific order, and each choice successively limits the values for the next parameter. Cascading parameters can reduce potentially thousands of choices to a manageable number. For example, a user chooses a product category, then chooses from the list of available products in that category, then chooses a size, and then a color. For more information, see How to: Add Cascading Parameters to a Report (Report Builder 2.0).

Use the query designer associated with a data source to help build a parameterized query.

For more information, see How to: Associate a Query Parameter with a Report Parameter (Report Builder 2.0).