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:

[caption id="attachment_2904" align="aligncenter" width="783"] SSRS parameters in SSDT Preview[/caption]

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

[caption id="attachment_2905" align="aligncenter" width="215"] SSRS Parameter Data Types[/caption]

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.

 

[caption id="attachment_2907" align="aligncenter" width="214"] SSRS Parameters Additional Properties[/caption]

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

[caption id="attachment_2908" align="aligncenter" width="860"] SSRS Available Values for Parameters[/caption]

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.

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

```

Next we need to create a report viewer object:

```powershell
# 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:

```powershell
# Report Server Properties
$rv.ServerReport.ReportServerUrl = "https://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:
```powershell
# Get parameters
$rv.ServerReport.GetParameters()

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

[caption id="attachment_2911" align="aligncenter" width="646"] SSRS Parameters in PowerShell using ServerReport.GetParameters()[/caption]

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

```powershell
$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
$item
} |
Format-Table -AutoSize
```

Result will look like this:

[caption id="attachment_2898" align="aligncenter" width="860"] Discovering SSRS Report Parameters using PowerShell[/caption]

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!