• Article

Filtering Report Data at the Source

To filter data for a report dataset at the source, specify a query that includes only the data that you need for your report. For each data source type, Reporting Services provides a 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 datasets, data regions, and groups. For more information, see Filtering Report Data in the Report.

For more information about query designers, see Reporting Services Query Designers.

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, you can add a WHERE clause to filter values. 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.

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 and Creating Report Datasets from SQL Server Analysis Services.

Filtering Data by Using Parameters in the Query

When you include query parameters (also known as query variables) in a query, Reporting Services automatically creates report parameters that are 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 (Reporting Services).

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

For Transact-SQL queries, different data sources support different syntax for parameters. Support ranges from parameters that are identified in the query by position or by name. For more information, see Using Query Parameters with Specific Data Sources (Reporting Services).

For MDX queries, you must select the parameter option for a filter to create a parameterized query. For more information, see How to: Define Parameters in the MDX Query Designer for Analysis Services.