Working with SQL Report Parameters
SQL Report Parameters are a cornerstone in database reporting since they offer both flexibility and a profound depth of analysis. Grasping this essential topic can significantly elevate your reporting capabilities and unlock a more tailored and interactive data experience.
What are report parameters?
Report parameters in SQL are dynamic values that can be accepted as input by the report from the end user. serve multiple functions: they can filter data directly within the report, modify its presentation, and link to associated reports.
In this article, we will show you how to create a parameter specifically designed for data filtering within a report. Using dbForge Studio for SQL Server, you have the flexibility to filter data both on the client side and directly at the database server level. We'll explore both methods in detail.
Client-side filtering
With help of SQL Report Builder, we created a report, showing information about employees.
Yet, the report ended up being extensive and somewhat challenging to navigate. To address this, we'll add a parameter to filter the data by Job Title.
Step 1: Add a report parameter in dbForge Studio for SQL Server
To start creating a parameter, return to the Designer mode. Then, navigate to Data Source, right-click on Parameters, and select Add Parameter.
In the Add New Parameter dialog that will appear, specify the parameter's Name, Description (which end-users will see in Print Preview), Type and Default Value. For our report, we name the parameter "Job" and select the String type.
Following that, in the Value Source dropdown, select Dynamic List to configure the values for the parameter. Then, specify the Data Source and Data Member associated with the parameter. The Value Member indicates the data field supplying the values to the parameter, while the Display Member pinpoints the data field that offers display names for those parameter values. For our scenario, both the Value Member and Display Member are identical.
Once you've finished creating parameter, it will appear in the Data Source panel.
Set up filtering by the created parameter
Now, to apply the created parameter to the report, click the hamburger icon situated in the top-left corner of the Report Designer. This will bring up the Report Tasks dialog.
In the Report Tasks dialog, click the plus icon and select Add Condition. Then click the first element in the condition pattern that appears and select the required column - in our case, it is JobTitle. Then click the second element and select the condition operator - we select Equals. And finally click the pencil icon twice to select the parameter. As we have only one parameter in the Data Source, it is automatically shown near the question icon. Once done, click OK.
Preview the created SQL Report
Switch to the Preview mode to see the result. In the Parameters pane, you should now see our newly-created parameter. Select a job title from the drop-down list and click Submit. The report will be filtered by the selected criterion.
Step 4: Save or print the SQL report
If satisfied with the previewed report, you can either print or save it. Simply click the appropriate button on dbForge Studio's toolbar.
