How to create a parameterised report in ssrs

Fri May 24, 2024

Creating a parameterized report in SQL Server Reporting Services (SSRS) allows you to generate customized reports based on user input.

How to create a parameterized report in SSRS:

Step 1: Launch SQL Server Data Tools (SSDT)

Open SQL Server Data Tools (formerly known as Business Intelligence Development Studio) or SQL Server Data Tools for Visual Studio, depending on your SQL Server version. This is the environment in which you design your SSRS reports.

Step 2: Create a New SSRS Report Project

In SSDT, create a new Reporting Services project if you don't have one already. This project will contain your report.
Right-click on the "Reports" folder in the Solution Explorer and select "Add" -> "New Item."
Choose "Report" and give it a name, then click "Add."

Step 3: Design Your Report
In the Report Data pane, right-click on "Parameters" and select "Add Parameter." This opens the Parameter Properties dialog.

In the Parameter Properties dialog, configure the following settings:

Name: Provide a unique name for your parameter.

Data type: Choose the appropriate data type for your parameter.
Prompt: Enter a user-friendly prompt to instruct users what the parameter represents.
Available Values: Define how users will select values for the parameter. You can choose "None," "From Query," "From Query (Multi-Value)," "From Query (Single-Value)," or "Specify Values" depending on your needs.
In the "Default Values" tab, you can specify default values for your parameter if needed.

Click "OK" to save your parameter settings.

Step 4: Create the Report Dataset
In the Report Data pane, right-click on "Datasets" and select "Add Dataset."

Configure your dataset by specifying the data source and query to retrieve the data you want to display in your report. Use your parameter in the query, where necessary, to filter data.

Step 5: Design the Report Layout
Drag and drop the fields from your dataset onto the report layout to design how the report will look.

To use the parameter in your report, you can reference it using the following syntax: @ParameterName.

Set up visibility and formatting based on your parameter values as needed.

Step 6: Preview and Test the Report
Save your report.

Right-click on the report in the Solution Explorer and select "Preview." This allows you to test the report with different parameter values to ensure it behaves as expected.

Step 7: Deploy the ReportOnce you are satisfied with your parameterized report, deploy it to your SSRS server. Right-click on your project in Solution Explorer and choose "Deploy."

Step 8: Access the Report
Your parameterized report is now accessible via the SSRS web portal or any application that integrates with SSRS. Users can input their parameter values to generate customized reports.
Creating parameterized reports in SSRS provides flexibility and empowers users to retrieve data that is specifically relevant to their needs. It's a valuable feature for reporting in various business scenarios.

Vijay Kashyap
SSRS Basics

How to create a parameterised report in ssrs

You may also be interested in