Download SSRS Parameterized Reports in PDF with PowerShell

Written by belle
Posted October 11, 2011 at 11:47 pm

Here’s a short PowerShell script that :
1. Connects to your report server
2. Loops through a set of parameters stored in an array
3. Saves the PDF version of the report to a local folder, with appropriate names

Here’s a snippet of code to set your SSRS parameters programmatically using PowerShell

  $params = $null;
 
  #set parameters
  #here's a sample usage http://msdn.microsoft.com/en-us/library/microsoft.reporting.winforms.reportparameterinfo(v=vs.80).aspx
  $params = new-object 'Microsoft.Reporting.WinForms.ReportParameter[]' 3
  $params[0] = new-Object Microsoft.Reporting.WinForms.ReportParameter("FISCALYEAR", $fiscalyear, $false);
  $params[1] = new-Object Microsoft.Reporting.WinForms.ReportParameter("MONTHENDDATE", $monthenddate, $false);
  $params[2] = new-Object Microsoft.Reporting.WinForms.ReportParameter("SALESGROUP", $salesgroup, $false);
 
  $rv.ServerReport.SetParameters($params);

Of course this is just a sample, you can definitely extend this by using SMO to automatically pull parameter values, or use values stored in a file (among a million other things you can do with ubercool PowerShell)

#==============================================================
#PowerShell and SSRS
#http://www.sqlmusings.com / http://www.twitter.com/sqlbelle
#==============================================================
 
#I am qualifying this because I have more than one version in my system
[void] [System.Reflection.Assembly]::Load("Microsoft.ReportViewer.WinForms, `
	Version=10.0.0.0, Culture=neutral, `
	PublicKeyToken=b03f5f7f11d50a3a")
#If you need webforms, use Microsoft.ReportViewer.WebForms
 
#Windows.Forms for viewing
[void][System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")
#System.Diagnostics because I want to open Acrobat
[void][System.Reflection.Assembly]::LoadWithPartialName("System.Diagnostics")
 
$rv = New-Object Microsoft.Reporting.WinForms.ReportViewer;
$rv.ProcessingMode = "Remote";
$rv.ServerReport.ReportServerUrl = "http://yourserver.com/ReportServer";
$rv.ServerReport.ReportPath = "/path/to/Financial Report";
 
#--------------------------------------------------------------
#PDF
#--------------------------------------------------------------
#need these variables for PDF rendering
#http://www.csharpcourses.com/2008/06/how-to-pass-parameters-to-reporting.html
$mimeType = $null;
$encoding = $null;
$extension = $null;
$streamids = $null;
$warnings = $null;
 
$salesgroups = @("Red Team", "Blue Team", "Orange Team", "Yellow Team");
$monthname = "September2011";
$monthenddate = "9/30/2011 12:00:00 AM";
$fiscalyear = "2012";
 
foreach ($salesgroup in $salesgroups)
{
  $params = $null;
 
  #set parameters
  #here's a sample usage http://msdn.microsoft.com/en-us/library/microsoft.reporting.winforms.reportparameterinfo(v=vs.80).aspx
  $params = new-object 'Microsoft.Reporting.WinForms.ReportParameter[]' 3
  $params[0] = new-Object Microsoft.Reporting.WinForms.ReportParameter("FISCALYEAR", $fiscalyear, $false);
  $params[1] = new-Object Microsoft.Reporting.WinForms.ReportParameter("MONTHENDDATE", $monthenddate, $false);
  $params[2] = new-Object Microsoft.Reporting.WinForms.ReportParameter("SALESGROUP", $salesgroup, $false);
 
  $rv.ServerReport.SetParameters($params);
  $rv.ProcessingMode = [Microsoft.Reporting.WinForms.ProcessingMode]::Remote;
 
  $rv.ShowParameterPrompts = $false;
  $rv.ServerReport.Refresh();
 
  $bytes = $null;	
  $bytes = $rv.ServerReport.Render("PDF", $null, 
				[ref] $mimeType, 
				[ref] $encoding, 
				[ref] $extension, 
				[ref] $streamids, 
				[ref] $warnings);
  $file = "C:FinancialGroupFinancial_" + $salesgroup + "_" + $monthname + ".pdf";
  $fileStream = New-Object System.IO.FileStream($file2, [System.IO.FileMode]::OpenOrCreate);
  $fileStream.Write($bytes, 0, $bytes.Length);
  $fileStream.Close();
 
  #if you want to open the PDF automatically, can uncomment the following
  #	[System.Diagnostics.Process]::Start($file);
 
}