Parameters are one of the most widely used functionalities of interactive reporting tools, giving us more flexibility and providing an enhanced user experience. In this blog, we are going to walk you through how to add parameters to your report with use cases.
The most common use of parameters is to filter the report data by specify the data set query parameter in report. In this scenario we use a sales order report in which users are prompted for values when they run the report, and the data set query retrieves only the data that is requested in the parameter from the user.
Requirements
As a first step, launch the Bold Reports On-Premise Report Server site. Now we need the sales report file to get started with this. Let’s download the sales report .rdl file and upload it to the Report Server. Then we’ll edit the report in the Bold Report Designer IDE.
Note: For step-by-step instructions on how to create the sales report, please refer to Create First Report in Bold Reports in the blog series.
Create data set for parameters
Create a data set named CountryList to populate the drop-down list of available values for the parameter. Paste the following query into the query designer. The given query runs against the Northwind sample database.
SELECT [dbo].[Orders].[ShipCountry] FROM [dbo].[Orders] WHERE [dbo].[Orders].[ShipCountry] IS NOT NULL GROUP BY [ShipCountry]
Click Finish to create the data set.
Create parameter
Click the Parameter pane to create a new parameter, then specify the parameter name as ShipCountry and the set the prompt value to Ship Country. Select Allow multiple values for the parameter, which will enable users to input multiple values.
Click Assign Values in the Parameter pane. Select Query Value under the Available Value tab then choose CountryList from the data set drop-down. Select ShipCountry in Value field and Label field from the drop-downs.
Note: The parameter Available Value option varies based on the use case. Let’s say the user wants to type the country name manually and doesn’t want a list of available country names from the data set in the sales report parameter. Then we can choose the None option. Or if a use case needs specific, predefined available values then we can make use of the Specify option.
Now, specify a default value for the parameter so the report runs automatically in report preview. Let’s click Default Values > Specify > Add to define default values for the parameter. We’ll specify the default value as UK and click OK.
Successfully we have configured the parameter properties to specify which data set and which field to use to populate the parameter drop-down with a default value on the Report Viewer toolbar.
Add query parameter
If you’re viewing the report, it will show all sales order details in the report view. But say you want to show only some countries’ order details alone in the report view. We can use the Query Parameter option to filter the data of the reports.
First, click the data sets icon in the report. Right-click SalesOrderDetails, and then click Edit.
Add the following Transact-SQL WHERE statement as the last line in the query to define query parameters. The WHERE statement limits the retrieved data based on the specified value from the parameter @ShipCountry.
WHERE [dbo].[Orders].[ShipCountry] IN (@ShipCountry)
Add query parameterNow click Finish. The Define Query Parameters dialog box opens and prompts for a value for the query parameter @ShipCountry. Let’s input UK as the value and click OK.
Preview
We can preview the report by toggling from design to preview. The Sales Order details report will look like the following in Report Viewer.
You can see the Ship Country parameter on the left side of the report with the default value UK. You can choose parameters with multiple values to input the report filter as you want.
Conclusion
Congratulations! You have successfully created a parameter for a report with the most common use of parameters.
I hope in this blog, you have learned how to add parameters to simple report and display parameter filter data in a report using Bold Report Designer. To explore further, I recommend you go through our sample reports and documentation. I have also attached the generated .rdl file for further reference.
If you have any questions about this feature, let us know in the comments section below. You can also contact us through the Bold Reports contact page or, if you have an account already, you can log in and submit your questions. Stay tuned to our official Twitter, Facebook, LinkedIn, Pinterest and Instagram pages for the new announcement and blog post updates.
