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.
