Working with SQL Report Parameters
In an SQL report, parameters are used to filter data at the report's level, vary appearance, and connect to related reports.
In this article we will show how to create a parameter to filter data from a report.
for SQL Server
enables to perform filtering on the client side and on the database sever side. We will consider both ways.
With help of SQL Report Builder, we created a report, showing information about employees.
However, the report turned out to be quite large and a hard to navigate through. That is why we
decided to create a paremeter, that would filter data by Job Title.
To start creating a parameter, return to the Designer mode.
Next, go to Data Source, right-click Parameters and select Add Parameter.
The Add New Parameter dialog box will appear. Specify the parameter's Name, Description
(that will be available to end-users in Print Preview), Type and Default Value.
We entitled our parameter as Job and selected the String type.
Now select the Supports the collection of standard values check box to set up values
for the parameter. On the Dynamic values tab, specify Data Source,
and Data Member for the parameter. Value Member defines a data field
that provides values to the parameter. Display Member defines a data field that
provides display names for parameter values.
In our case, the Values Member and Display Member values coincide.
After you finished creating parameter, it will appear in Data Source.
Now, to apply the created parameter to the report, click the top-left corner of the Report Designer to invoke
the report's smart tag and click next to Filter String.
Click and keep on selecting until we get to Parameters.
In our case we will use the Job parameter.
Switch to the Preview mode to see the result. The created Job parameter is located within
the Parameters window on the left. Select a job title from the drop-down list and click Submit.
Now the report is filtered by the selected criterion.
If something went wrong or you decide to change the created parameter, switch back to the Designer view,
go to Data Source, right-click Parameters and select Edit Parameters...
The Parameter Collection Editor dialog box will appear, where you can make proper adjustments to the parameter.
Database Server-Side Filtering
To illustrate the feature, we created a query-based report, with the same conditions, as we used during the client-side filtering.
The following screen-shot shows a syntax example.
To filter data of the ready report, right-click the Query node in Data Source and select Edit Parameters...
The Edit Parameters dialog box will appear. Type a value for the @job parameter.
In our case, it is a certain job title.
Now, we may switch to the Preview mode to see the result.
As you can see, the result is identical to the one we received during the client-side filtering.
The Parameter feature, established in dbForge Studio for SQL Server, considerably enhances the functionality of
data reporting and makes it more effective by controlling a report's data on both — client side and database server
side. Utilizing parameters, you can pass data of a certain type to a report (e.g., to pass a specific value to its
filter string), without writing any code.