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