Previous Next

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. dbForge Studio for SQL Server enables to perform filtering on the client side and on the database sever side. We will consider both ways.

Client-Side Filtering

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.

Add Paramerer Option

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, Data Adapter 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.

Add New Parameter Dialog Box

After you finished creating parameter, it will appear in Data Source.

New Parameter displayed 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 Dots Button next to Filter String.

Report Tasks

Click Plus Button and keep on selecting until we get to Parameters. In our case we will use the Job parameter.

FilterString Editor

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...

Edit Parameters

The Parameter Collection Editor dialog box will appear, where you can make proper adjustments to the parameter.

Parameters Collection Editor

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.

Syntax of a Query-Based Report

To filter data of the ready report, right-click the Query node in Data Source and select Edit Parameters...

Edit Parameters of a Query

The Edit Parameters dialog box will appear. Type a value for the @job parameter. In our case, it is a certain job title.

Edit Parameters Dialog Box

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.