SSRS - How to Create Dynamic Reports using Parameters

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.

Try it

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

2. Add a parameterised 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.

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.             

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!

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

1. Click OK to return to the report.

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

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

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

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

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

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