Dataset Filter, Dataset Parameter, and Report Parameter

1.  When to Use Filters at the Dataset and When to Use Query Parameters?

Filters at the dataset are very similar to parameters because they affect the data that is presented to all regions. However, filters always occur after data has been retrieved from the data source and might not perform as well as parameters because the query pulls all the data, not just the specific data that the user needs. Thus, from a performance perspective, it is better to use parameters because they let SSRS pull filtered data from the data source. In contrast, when you use filters, the queries retrieve all data and then filter the information in an additional step.

The main advantage of filters is that information can be shared between multiple users if you configure the report to use report execution snapshots. In report execution snapshots, the first execution of the report pulls the data from the server, and successive executions use the snapshot information stored in the report server. So the best practice is not to use filters at the dataset unless you plan to use report snapshots.  In other words, use query parameters instead to filter information before it gets to the report.

Filters at the dataset can be configured as follows:

·       Select the Report Data window.

·       In the Report Data window, right-click the dataset you want to filter, and then select Dataset Properties.

·       In the Dataset Properties dialog box, click the Filters tab.

·       In the Change Filters pane, click Add.

·       Configure the filter expression, data type, operator, and value. If your filter expression is simple, such as a single data field, the data type is assigned automatically from the field data type.

Filters at data regions affect only the report item they are filtering, and different data regions can apply different filters to the same dataset. Additionally, filters can also be for a data grouping.

2.  When to Use Query parameters and When to Use Report parameters?

A query parameter, also called a dataset parameter, is a variable defined at the dataset level that affects the way SSRS queries the data source. The main purpose of a query parameter is to filter data in the data source.

A report parameter is a variable defined at the report level that allows the personalization of a report at run time. The main purpose of a report parameter is to add interactivity to your reports, letting users change the report behavior based on options they select. Report parameters are often mapped to query parameters. For example, the user can select a department and use the report parameter to assign the query parameter value and filter expenses by departments. In addition, if a data source does not support query parameters, you can use report parameters to give users the ability to filter data after it has been retrieved for the report.

Filtering datasets after the data has been retrieved is also useful if you are using report snapshots. With report snapshots, SSRS caches the data and does not retrieve it from the source again to satisfy a user rendering request. If you want to present only a subset of data to the user, you need to use report parameters and filter the data in the report dataset or in any report data region.

The Differences between Query and Report Parameters

	Query parameters	Report parameters
Parameter runs on	Database server	Report server
Primary purpose of parameter	To filter data	To interact with user
Parameter properties	Name and value (can be an expression)	Name, type, value, prompt, available values, default values, and so on
Parameter is managed through	Dataset	Report Parameters dialog box

3.  How to Create a Query Parameter or Dataset Parameter?

a.   Option 1 via Dataset Properties – Your Dataset (right-click)| Dataset Properties | Query or Parameter.

·       In BIDS, open a project and then open a report.

·       In the Report Designer, open the Report Data window by using the tab displayed on the left side of BIDS (where you also access the Toolbox window). If the Report Data tab is not there, use the BIDS View menu and select Report Data to open this window.

·       Change the query syntax to add the parameters. Right-click a dataset to which you want to add parameters, and select Dataset Properties. On the Query tab in the Dataset Properties dialog box, edit the query to add the parameters. The SQL Server client uses the at symbol (@) as the first character of the name to define the parameters in the query. You can use parameters in the WHERE clause of the T-SQL query.

·       In the Dataset Properties dialog box, click the Parameters tab.

·       On the Parameters tab, assign a value to each query parameter.

b.   Option 2 via Graphical Query Designer (works for MDX as well) – Your Dataset (right-click)| Query.

·       In the Report Designer, open the Report Data window.

·       Right-click the dataset, and then select Query.

4.  How to Create a Report Parameter?

a.   Option 1 – via the Parameter folder in the Report Data window

·       In the Report Designer, click the Design tab, if necessary.

·       In the Report Data window, right-click the Parameters folder, and then select Add Parameter.

·       If you expand the Parameters folder, you can right-click an already defined report parameter and delete it or edit its properties.

b.   Option 2 – via Query Parameters

By default, a report parameter with an equivalent name is created for each query parameter, and query parameter values are assigned to corresponding report parameters. End users can change report parameters when they run the report, and SSRS replaces query parameters with report parameter values when executing the query. This is usually what you need; nevertheless, you could assign a constant or an expression to a value of a query parameter.

5.  Working with Report Parameter

a.   How to Assign a List of Values That Users Can Select From - Binding a Dataset to a Parameter?

Your Report Parameter (right-click)| Report Parameter Properties | Available Values.

b.   How to Use Multivalued Parameters

Your Report Parameter (right-click)| Report Parameter Properties | General | Allow Multiple Values.

c.   How to Set Parameter Defaults?

Your Report Parameter (right-click)| Report Parameter Properties | Default Values.

d.   How to supply the parameter values through the URL used to run parameterized reports?

http://localhost/ReportServer?%2fLogistics%2fTracking+Numbers+by+Month&rs:Command=Render&Department=12

·       Report server URL: http://localhost/ReportServer? The report server name and protocol will reflect your specific installation. The question mark begins the parameter section.

·       Report path: %2fLogistics%2fTracking+Numbers+by+Month& The path includes the folder and report name; for your reports, replace the names appropriately. Use %2f to replace the slash mark (/) and %20 to replace spaces.

·       Command section: &rs:Command=Render The command section configures the action of the report. The & symbol at the beginning separates the command section from the report path.

·       Parameters section: &Department=1 Use the & symbol to separate parameters, and use a name and value pair to configure the parameter. If you have multivalued parameters, you need to repeat the name and value pair once for each parameter.

·       If you want to hide parameters in the report, add &rc:parameters=false to the URL. You can also hide the toolbar completely by adding &rc:toolbar=false.