Discovering SSRS Report Parameters using PowerShell

We can use PowerShell to discover many SSRS properties, including the different parameters available in a report. This blog post is based on SQL Server 2014 and PowerShell V5.

Here is an example:

SSRS parameters in SSDT Preview
SSRS parameters in SSDT Preview

This particular report utilizes different variations of parameters. To review, here are the available data types for SSRS report parameters:

SSRS Parameter Data Types
SSRS Parameter Data Types

In the example above, Customer ID is an integer. Order Date From and To are dates. Online Flag is a boolean. Min Total Due is a float. Status is a multi-value text.

Additional properties can be found in the Report Parameter window, including whether the parameter is nullable, if it’s multivalued, or if it’s visible or hidden, etc.


SSRS Parameters Additional Properties
SSRS Parameters Additional Properties

In the SSRS parameter window side bar, you will also find options to see available values or default values for a parameter:

SSRS Available Values for Parameters
SSRS Available Values for Parameters

To do this in PowerShell, the first thing to do is to add the assembly that will allow us to create the ReportViewer object. Note that the version and public key token are specified, so check the version you have on your machine to ensure the accuracy.

# Add assembly
Add-Type -AssemblyName "Microsoft.ReportViewer.WinForms, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

Next we need to create a report viewer object:

# Create report viewer object
$rv = New-Object Microsoft.Reporting.WinForms.ReportViewer

Once we have the report viewer object, we can set the Report Server URL and the report we want to use:

# Report Server Properties
$rv.ServerReport.ReportServerUrl = "http://localhost/ReportServer"
$rv.ServerReport.ReportPath = "/Sales Reports/Sales Report"

To get a list of all the discoverable parameters and their values, we can simply use the ServerReport.GetParameters() method:

# Get parameters 

This will give us a result like this for every parameter in our report:

SSRS Parameters in PowerShell using ServerReport.GetParameters()
SSRS Parameters in PowerShell using ServerReport.GetParameters()

However if you want it to be more readable or formatted, we can target only the specific parameters we want to see, and display them in a more easily digestible manner. To do this, we can pipe the parameters to a Foreach-Object cmdlet, and utilize a PowerShell PSCustomObject to save our selected fields and their formatted values. In the example below, I also extract the valid values for each parameter, if they exist, and concatenate them into a single string

$rv.ServerReport.GetParameters() |
ForEach-Object {
    $param = $_
    $validValues = ""
    if ($param.ValidValues -ne $null)
        $validValues = [string]::Join(",", ($param.ValidValues |
                       ForEach-Object {$_.Value}))

    $item = [PSCustomObject] @{
        ParamName = $param.Name
        DataType = $param.DataType
        Nullable = $param.Nullable
        MultiValue = $param.MultiValue
        ValidValues = $validValues

    #display current object
} |
Format-Table -AutoSize

Result will look like this:

Discovering SSRS Report Parameters using PowerShell
Discovering SSRS Report Parameters using PowerShell

This is just the start. You can use this script to discover parameters for multiple reports in a folder, and email you (or your report developers) of a comprehensive list for those reports.

Pretty neat!

2 thoughts on “Discovering SSRS Report Parameters using PowerShell

Leave a Reply

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

You are commenting using your 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 )

Connecting to %s