PowerShell export SSRS report to PDF

On occasion, I need to produce a report at the end of a production run or for a morning emailed summary. If I had access to SQL Server Enterprise Edition, then the later task of the morning summary may have been accomplished using a subscription.

My solution leverages Microsoft.ReportViewer.WinForms.dll.

To find out if you have the version referenced by the script, you can run the following PowerShell command.

1

[System.Reflection.Assembly]::Load('Microsoft.ReportViewer.WinForms, Version=15.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91')

output from reflection load

If you search the computer for Microsoft.ReportViewer.WinForms.dll and then you need to find out the associated information, you can run the following PowerShell command. The output of this PowerShell statement will provide the Version, Culture and PublicKeyToken.

1

([System.Reflection.Assembly]::LoadFile('c:\temp\Microsoft.ReportViewer.WinForms.dll')).FullName

output from reflection load file

Solution

My solution presented here uses PowerShell to generate a report and write it to a file.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89


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

#--------------------------------------------------
# function GenerateReport
#--------------------------------------------------
Function GenerateReport {
  Param (
    [Parameter(Mandatory = $true)][String] $reportServerUrl,
    [Parameter(Mandatory = $true)][String] $reportPath,
    [Parameter(Mandatory = $true)][String] $renderOutputType,
    [Parameter(Mandatory = $true)][String] $outputFileNameAndPath,
    [Parameter(Mandatory = $true)][Microsoft.Reporting.WinForms.ReportParameter[]] $reportParameters
  )
  try
  {
    #--------------------------------------------------
    # report server properties
    #--------------------------------------------------
    $rv = New-Object Microsoft.Reporting.WinForms.ReportViewer

    $rv.ServerReport.ReportServerUrl = $reportServerUrl
    $rv.ServerReport.ReportPath = $reportPath
    $rv.ProcessingMode = 'Remote'
    $rv.ServerReport.SetParameters($reportParameters)

    #--------------------------------------------------
    # render the SSRS report as the specified type (PDF, EXCELOPENXML, etc)
    #--------------------------------------------------    
    $bytes = $null
    $bytes = $rv.ServerReport.Render($renderOutputType)

    #--------------------------------------------------
    # save the report to a file
    #--------------------------------------------------
    $fileStream = New-Object System.IO.FileStream($outputFileNameAndPath, [System.IO.FileMode]::OpenOrCreate)
    $fileStream.Write($bytes, 0, $bytes.Length)
    $fileStream.Close();
    $fileStream.Dispose();

    #--------------------------------------------------
    # dispose
    #--------------------------------------------------
    $rv.Dispose()

    return $true
  }
  catch
  {
    Write-Host $_.Exception.Message
    $Error.Clear()
    return $false
  }
}

#--------------------------------------------------
# configure report parameters
#--------------------------------------------------
$startDate = "{0:MM/dd/yyyy} 00:00" -f (Get-Date).AddDays(-1)
$endDate = "{0:MM/dd/yyyy} 00:00" -f (Get-Date)

$multiValueParam = New-Object System.Collections.Specialized.StringCollection
$multiValueParam.Add("Thing01")
$multiValueParam.Add("Thing03")
$multiValueParam.Add("Thing05")

$reportParameters = New-Object 'Microsoft.Reporting.WinForms.ReportParameter[]' 4
$reportParamters[0] = New-Object Microsoft.Reporting.WinForms.ReportParamter('StartDate', $startDate)
$reportParamters[1] = New-Object Microsoft.Reporting.WinForms.ReportParamter('EndDate', $endDate)
$reportParamters[2] = New-Object Microsoft.Reporting.WinForms.ReportParamter('Things', $multiValueParam)
$reportParamters[3] = New-Object Microsoft.Reporting.WinForms.ReportParamter('SomeId', '123456789')

#--------------------------------------------------
# call function
#--------------------------------------------------
If (
  GenerateReport `
    -ReportServerUrl 'http://localhost/ReportServer' `
    -ReportPath '/FolderIfApplicable/MySampleExportReport' `
    -RenderOutputType 'PDF' `
    -OutputFileNameAndPath 'c:\MySampleExportReport.pdf' `
    -ReportParameters $reportParameters
) {
  Write-Host 'report generated'
} Else {
  Write-Host 'oops, something did not go as planned'
}

Contents

Solution