Live Chat Icon For mobile
Live Chat Icon

Add Parameters to Your Report Using Bold Reports Designer

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.

sales report design
sales report design

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]
Create DataSet
Create DataSet

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.

Add new parameter
Add new parameter

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.

Add available value
Add available value

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.

Assign default value
Assign default value

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.

Edit data set
Edit data set

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 parameter
Add query parameter

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.

Report preview
Report preview

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.

Multi value parameter preview
Multi value parameter preview

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 TwitterFacebookLinkedInPinterest and Instagram pages for the new announcement and blog post updates.

Leave a Reply