Home  >  Blog  >   SSRS

SSRS - How to Create Dynamic Reports using Parameters

Rating: 5
  
 
10590

Using Parameters to make Dynamic Reports

Fixed reports that show current data are very useful, but even more useful is the ability to build reports that allow the end user to change parts of the query to get at the information they really want. By adding parameters to a report, you can request information from the user when the report first opens, and then fetch the corresponding data.

As an example, you might specify that the user can type in a year to get data for that period only, or the user could select a category from a drop down list and the report would run using that selection.

If you would like to become an SSRS (SQL Server Reporting Services) certified professional, then visit Mindmajix - A Global online training platform: "SSRS Training and Certification Course". This course will help you to achieve excellence in this domain.

Try it

1. In the Report Data panel, right-click on the SalesSummary dataset and choose Dataset Properties.

2. Add a parameterized WHERE clause to the query so that it matches the following query. Do not click OK yet.

SELECT
      MONTH(OrderDate) AS Month,
      SUM(TotalDue) AS Total
FROM
      Sales.SalesOrderHeader
WHERE
      YEAR(OrderDate) = @Year
GROUP BY
      MONTH(OrderDate)
ORDER BY
      Month

3. Click OK. Notice that you can now expand the Parameters folder in the Report Data panel and check that the parameter is there.

4. Preview the report by clicking on the Preview tab, and when the report has loaded, enter 2006 in the Year text box at the top left of the report, then click View Report on the right.

5. Enter 2007 in the Year text box and click View Report again to see that the data has changed.

Be aware that because we have not specified otherwise, this text box will accept anything you type into it, and if it’s not a year, you may get an error.

Sales summary

SSRS Dynamic Parameter lists

To restrict the values that can be entered for a parameter, you can configure values that will be displayed as a drop down list. One option is to supply a fixed list of values that the reader can select from. This might be useful for a parameter that will only ever have a fixed set of options such as a list of the continents of the world, or a ‘male/female’ selection.

                                Frequently Asked SSRS Interview Questions & Answers

In some cases it would make sense to let the reader select from a dynamic list. This would be useful when selecting a year. Instead of manually adding the latest year to the list on January 1st every year, you could supply a query that will fetch a list of all the years that are present in the data.

In the Try it for this section, you will query the database to get a list of the years. If you’re not familiar with SQL, note that the DISTINCT word is used to only get unique values. If it was left out, you’d get a very long list of values; one for each order in the table!

Parameter properties

                                                (Related Page: How To Create A Report Dataset Reporting Services - SSRS)

MindMajix YouTube Channel

Try it

1. In the Summary Sales report, right click on the Datasets folder in Report Data and choose Add Dataset…

2. Name the new dataset Years, select the AdventureWorks data source and select Use a dataset embedded in my report. Enter the query text as shown:

SELECT DISTINCT
      YEAR(OrderDate) AS Year
FROM
      Sales.SalesOrderHeader
ORDER BY
      Year

3. Click OK to return to the report.

4. Expand the Parameters folder in the Report Data panel, right-click on the @Year parameter and choose Parameter Properties.

5. In the Report Parameter Properties window, open the Available Values page.

6. Select Get values from a query and choose the Years dataset you just created.

7. Set the both the value field and the label field to be Year.

8. Click OK and Preview the report to see the list of years.

9. Select a year from the list and click the View Report button.

Sales Summary_month

Explore SSRS Sample Resumes! Download & Edit, Get Noticed by Top Employers! Download Now!

List of Related Microsoft Certification Courses:

 SSIS Power BI
 SSAS SQL Server
 SCCM SQL Server DBA
 SharePoint BizTalk Server
 Team Foundation Server BizTalk Server Administrator

 

Join our newsletter
inbox

Stay updated with our newsletter, packed with Tutorials, Interview Questions, How-to's, Tips & Tricks, Latest Trends & Updates, and more ➤ Straight to your inbox!

Course Schedule
NameDates
SSRS TrainingMar 23 to Apr 07View Details
SSRS TrainingMar 26 to Apr 10View Details
SSRS TrainingMar 30 to Apr 14View Details
SSRS TrainingApr 02 to Apr 17View Details
Last updated: 03 Apr 2023
About Author

Ravindra Savaram is a Technical Lead at Mindmajix.com. His passion lies in writing articles on the most popular IT platforms including Machine learning, DevOps, Data Science, Artificial Intelligence, RPA, Deep Learning, and so on. You can stay up to date on all these technologies by following him on LinkedIn and Twitter.

read more
Recommended Courses

1 / 15