Automate SSRS Report Generation using PowerShell

This blog post is tested on SQL Server 2014 and PowerShell V5.

PowerShell has become more feature-rich in every version. We are now looking at PowerShell V5 (currently can be downloaded as part of Windows Management Framework v5 April 2015 Preview).

The support landscape with SQL Server hasn’t changed much. There isn’t a drastic increase in SQL Server cmdlets. However, the language and feature improvements in PowerShell in general improve how we can work with SQL Server.

One area where we can use PowerShell is with SQL Server Reporting Services (SSRS). I blogged about this a while back, but it’s time to revisit and expand on how we can use PowerShell to automate report generation.

In this blog post I will focus on generating PDF reports via scripting. Let’s tackle this piece by piece first, and we’ll put everything in a nice little script at the end of the post.

First, we need to add the ReportViewer assembly to the script. For this I am using the WinForms report viewer assembly. There is similar report viewer assembly for web forms.

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

Once we have loaded the assemblies, we can create a ReportViewer object

$rv = New-Object Microsoft.Reporting.WinForms.ReportViewer

Next we have to start building the report properties, including the Report Server URL and the path to the report we want to render. Remote processing mode just specifies that work will be done on the server.

$rv.ServerReport.ReportServerUrl = "http://localhost/ReportServer"
$rv.ServerReport.ReportPath = "/Sales Reports/Sales Report"
$rv.ProcessingMode = "Remote"

Next is we need to understand what parameters we need to send to the report. We can discover this by looking at the report visually.

SSRS parameters as seen from Report Server

Alternatively we can use PowerShell to discover the parameters (which is what makes using PowerShell fun! Most of the tasks you want to do with SQL Server can be scripted!).

Discovering SSRS Report Parameters using PowerShell

One way to send the parameters to the script is through a PowerShell hash table. Notice too that we can pass the multivalue parameter as a string[].

$inputParams = @{
   "CustomerID"    = 29982;
   "LastName"      = 'Marple';
   "OnlineFlag"    = $false;
   "MinTotalDue"   = 100.00;
   "OrderDateFrom" = '01/01/2001'
   "OrderDateTo"   = '01/01/2006'
   "Status"        = [string[]]('Sent to Warehouse', 'Shipped')
}

Since there are multiple parameters, we will need to create an array of Report Parameters. The number of items in the array can be derived from the hash table count:

#create an array based on how many incoming parameters
$params = New-Object 'Microsoft.Reporting.WinForms.ReportParameter[]' $inputParams.Count

To populate, we can use a loop to get all the items from our hash table:

$i = 0
foreach ($p in $inputParams.GetEnumerator())
{
   $params[$i] = New-Object Microsoft.Reporting.WinForms.ReportParameter($p.Name, $p.Value, $false)
   $i++
}

To set the parameters, we can use the SetParameters method:

$rv.ServerReport.SetParameters($params)

Once the parameters are set, we can start creating our PDF report. To do this, we will need to call the Render method, which accepts parameters that define the output, including the format, device info, mime type, encoding etc. This is the syntax for Render:

Here is how we can set these parameters using PowerShell:

$mimeType = $null
$encoding = $null
$extension = $null
$streamids = $null
$warnings = $null

$bytes = $null
$bytes = $rv.ServerReport.Render("PDF", 
                                $null, 
                                [ref] $mimeType, 
                                [ref] $encoding, 
                                [ref] $extension, 
                                [ref] $streamids, 
                                [ref] $warnings)

To save the report into a file, we can use the System.IO.FileStream class which has a Write method:

$fileName = $baseFolder + "Sales Report.pdf"
$fileStream = New-Object System.IO.FileStream($fileName, [System.IO.FileMode]::OpenOrCreate)
$fileStream.Write($bytes, 0, $bytes.Length)
$fileStream.Close()

Here is the complete script, with a few enhancements to record start time, end time, and duration:

#--------------------------------------------------------------
# report parameters 
#--------------------------------------------------------------
$inputParams = @{
   "CustomerID"    = 29982;
   "LastName"      = 'Marple';
   "OnlineFlag"    = $false;
   "MinTotalDue"   = 100.00;
   "OrderDateFrom" = '01/01/2001'
   "OrderDateTo"   = '01/01/2006'
   "Status"        = [string[]]('Sent to Warehouse', 'Shipped')
}

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

#--------------------------------------------------------------
# display calculated month end date
#--------------------------------------------------------------
$startTime = Get-Date
Write-Host ("=" * 80) 
Write-Host "Generating Sales Report"
Write-Host "Start Time: $startTime"
Write-Host ("=" * 80) 

#--------------------------------------------------------------
# create timestamped folder
# where we will save our report 
#--------------------------------------------------------------
$dt = Get-Date -Format "yyyy-MMM-dd hhmmtt"
$baseFolder = "C:Sales Reports$($dt)"

# if the path exists, will error silently and continue
New-Item -ItemType Directory -Path $baseFolder -ErrorAction SilentlyContinue | Out-Null

$rv = New-Object Microsoft.Reporting.WinForms.ReportViewer

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

#--------------------------------------------------------------
# set up report parameters 
#--------------------------------------------------------------
$params = $null

#create an array based on how many incoming parameters
$params =  New-Object 'Microsoft.Reporting.WinForms.ReportParameter[]' $inputParams.Count

$i = 0 
foreach ($p in $inputParams.GetEnumerator())
{
   $params[$i] = New-Object Microsoft.Reporting.WinForms.ReportParameter($p.Name, $p.Value, $false)
   $i++
}
# set the parameters 
$rv.ServerReport.SetParameters($params)
$rv.ShowParameterPrompts = $false
$rv.RefreshReport()
$rv.ServerReport.Refresh()

#--------------------------------------------------------------
# set rendering parameters      
#--------------------------------------------------------------
$mimeType = $null
$encoding = $null
$extension = $null
$streamids = $null
$warnings = $null

#--------------------------------------------------------------
# render the SSRS report in PDF
#--------------------------------------------------------------
$bytes = $null
$bytes = $rv.ServerReport.Render("PDF", 
                                $null, 
                                [ref] $mimeType, 
                                [ref] $encoding, 
                                [ref] $extension, 
                                [ref] $streamids, 
                                [ref] $warnings)

#--------------------------------------------------------------
# save the report to a file  
#--------------------------------------------------------------
$fileName = $baseFolder + "Sales Report.pdf"
$fileStream = New-Object System.IO.FileStream($fileName, [System.IO.FileMode]::OpenOrCreate)
$fileStream.Write($bytes, 0, $bytes.Length)
$fileStream.Close()

#--------------------------------------------------------------
# calculate end time and duration 
#--------------------------------------------------------------
$endTime = Get-Date
$duration = New-TimeSpan -Start $startTime -End $endTime 
Write-Host ("=" * 80) 
Write-Host "End Time: $endTime"
Write-Host "Duration: $duration "
Write-Host ("=" * 80) 

#--------------------------------------------------------------
# show folder
#--------------------------------------------------------------
explorer $baseFolder

When the script is done, it will show a result similar to below (just a good visual check of how long the process took), as well as open Windows explorer and show the generated report (I am lazy that way).

Using PowerShell to Generate SSRS Report – duration

Where to go from here? This is just the basic script. This script can be wrapped in a function or module to make the code blocks easier to use and manage.