SQL Server Reporting Services (SSRS) is a handy tool for generating reports quickly. However, like most reporting packages, making the reports dynamic rather than simple static canned reports takes a bit of effort. For example, you may want to let the users of your report choose from a list of options. You could then apply these choices to the report results in various ways such as using the user selections to filter the results, or to hide and show results columns. In general, adding a dynamic, customizable aspect to your reports adds value for your users beyond spitting back a simple canned set of results.
In this example I will be working with a SQL Server 2008 SSRS set-up, but the general concepts hold true for related versions.
What SSRS Can and Cannot Do
SSRS does, to some extent, support dynamic parameters in your report. But keep in mind that your options are significantly limited compared with a .NET WebForm, so you will need to weigh the pros and cons of using either technology to develop your report. With SSRS you can add a new report parameter that you assign specific values to. This is then rendered as a drop-down list that contains a user-selectable list of options that you can use to dynamically build your report output. See the image at the start of this article for an example of how a simple report filter might look that allows users to filter by an imaginary product name, or sale price.
Seting Up your Report and Parameters
For starters, finish the basics of setting up your SSRS report: hook up your Datasource and point your DataSet to your Stored Procedure Then add a ToolBox component such as a Table to display your output.
At this point you should see an auto-populated list of the parameters that your procedure accepts on the left hand side-bar. In order to add a select-able list to your report, you will need to add a new parameter. You can do so by right clicking the parameters root folder and selecting the option to “add parameter“.
In the new Report Parameter Properties popup you will be able to:
- Enter the Name of your parameter (I like to use something descriptive for the Name field such as: ‘ShowColumn’ or ‘FilterColumn’ based on what you are intending to use the parameter list for). The Name you choose will be what you will later use in your scripts to check what options the user has chosen.
- In the Prompt text area you should enter what you want the label beside the dropdown list of parameters to look like, so for example for a filter field I would enter something like ‘Filter Report By:’.
- Leave your Data Type as Text, and check the checkbox option below to ‘Allow Multiple Values‘
Then click on the Available Values option on the left hand side-bar. This is where you will be able to add the list of options that you will allow the user to select. Set the radio button option to generate the list of options to ‘Specify Values‘.
In the grid below you will now need to enter the list of values that the user will have to choose from, which includes the Label to show the user, and the value that you will handle behind the scenes. If you want the value option to be related to the parameters that your Stored Procedure accepts, just write the parameter field name directly into the value fields.
Points to Keep in Mind
Don’t forget to update the visibility status of the auto-generated parameters for your report, especially if your dynamic parameters are replacing the functionality of the auto-generated parameters. If the parameters are not necessary then right-click on the parameter and under the General section check the ‘Allow null value‘ option. If you don’t do this, then the parameter is considered required (even if it is hidden) and the users of your report will get popup warnings that they have not provided required fields.
Also, if you are handling the parameter logic behind the scenes, change the parameter visibility from ‘Visible‘ to ‘Internal‘ in the ‘Select parameter visibility‘ section.
At this point you have a way of allowing the user to select from a range of input parameters that shows in a drop down list on the report. You can use this to give control over various aspects of your report to the user, making the system more interactive and tailored to the user’s needs. For example, you can now pass the user’s preferred parameters to a stored procedure, or alternately you can hide or show columns in the output table based on the user’s selections. I will cover these topics in more detail in subsequent articles.
Justin Cooney 