SSRS – Parameter Sets

When a report has multiple parameters, it can be handy for the user to be able to shortcut their way to making a selection in each of them, particularly when there’s a certain combination of parameter values which they frequently run. This is where the concept of a parameter set comes in. Essentially it is a way of grouping together a set of parameter values and being able to select that group from a report dropdown. Then the normal parameter dropdowns can get their default selection from that. The user is then free to tweak parameter selections if needed.

Parameters
Parameters in report preview

To make it all work, take these steps:

  1. Create a ParameterSet database table (needs at least ParameterSetID and ParameterSetName).
  2.  Create a ParameterSetDefault database table (needs at least ParameterSetDefaultID, ParameterSetID, ParameterName and ParameterValue)

    ParameterSetTables
    ParameterSet tables
  3. Fill the tables with data for each parameter set you want. You might want a ‘None’ parameter set which sets all the dropdowns to None or some other default.
  4. Create a GetParameterItems_ParameterSet stored procedure. This will feed your main dropdown.
    SELECT ParameterSetID    Value
           ,ParameterSetName Label
    FROM Control_ParameterSet
  5. Create a GetParameterDefaults stored procedure. This will take your selected parameterSetID and a particular parameterName and return you the default value.
    CREATE PROCEDURE GetParameterDefaults
    @parameterSetID INT
    ,@parameterName VARCHAR(100)
    
    AS
    
    SELECT ParameterValue
    FROM Control_ParameterSetDefault
    WHERE ParameterSetID = @parameterSetID
    AND  ParameterName = @parameterName
  6. In your master report, create a ParameterItems_ParameterSet dataset.

    NewDatasets
    New datasets
  7. Create a ParameterSet parameter to display the available options.

    ParameterSet parameter
    ParameterSet parameter
  8. Create a ParameterDefaults_<parameterName> dataset for each of your proper parameters. It sends selected parameterSetID and appropriate parameterName to the database and returns a value.

    ParameterDefaults dataset
    ParameterDefaults dataset example
  9. Edit your normal parameters to get their default values from the new ParameterDefaults datasets.

    Parameter Defaults
    Normal parameter defaults example

If your parameters are multi-select, you can specify more than one default value per parameter in the database and they will all get selected when you choose your parameter set.

There is one snag though. Due to an SSRS bug, if you choose a parameter set (other parameters get filled in) then change it to another, the other parameters don’t update. This is because the available items for the parameters haven’t changed and the defaults don’t get refreshed. The workaround is to make sure the lists of available items do change when you change parameter set, by appending the parameter set ID to the other parameter values. But I’ll demonstrate this in another blog post!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s