- Written by
belle
- Posted August 21, 2009 at 12:30 am
To view your SQL Server (or SSRS) reports using PowerShell, you can either use
- plain browser + URL string combo
- Report Viewer
- Web Services
The script below shows the first two options.
If you are going to go with Report Viewer (and personally, that’s my preference), you will need to download the ReportViewer redistributable package
- Report Viewer Redistributable 2005 SP1
- Report Viewer Redistributable 2008 SP1
One of these days I will hammer through the Web Services option, and also play more with what can be done with Report Viewer. And when I have good demos, I definitely will post them here 
#==============================================================
#VANPASS August 2009
#PowerShell and SSRS (SQL Server Reporting Services)
#Donabel Santos
#http://www.sqlmusings.com / http://www.twitter.com/sqlbelle
#==============================================================
#--------------------------------------------------------------
#Approach 1: simplistic approach - accessing report via URL
#--------------------------------------------------------------
Set-Alias ie "$env:programfilesInternet Exploreriexplore.exe"
#note you can control how the report is rendered via the parameters in the URL string
ie "http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fReports%2fSample+Report&rs:Command=Render"
#--------------------------------------------------------------
#Approach 2: using the ReportViewer
#You need to download the ReportViewer redistributable package
#--------------------------------------------------------------
#I am qualifying this because I have more than one version in my system
#If you need webforms, use Microsoft.ReportViewer.WebForms
[void] [System.Reflection.Assembly]::Load("Microsoft.ReportViewer.WinForms, `
Version=9.0.0.0, Culture=neutral, `
PublicKeyToken=b03f5f7f11d50a3a")
#Windows.Forms for viewing dialog box
[void][System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")
#System.IO because I want to export to Excel
[void][System.Reflection.Assembly]::LoadWithPartialName("System.IO")
#System.IO because I want to open Acrobat
[void][System.Reflection.Assembly]::LoadWithPartialName("System.Diagnostics")
#for credentials, if needed
[void][System.Reflection.Assembly]::LoadWithPartialName("System.Net")
$rv = New-Object Microsoft.Reporting.WinForms.ReportViewer;
$rv.ProcessingMode = "Remote";
$rv.ServerReport.ReportServerUrl = "http://localhost/reportserver";
$rv.ServerReport.ReportPath = "/Reports/Sample Report";
#if you need to provide basic credentials, use the following
#$rv.ServerReport.ReportServerCredentials.NetworkCredentials=
# New-Object System.Net.NetworkCredential("myuser", "mypassword");
$rv.Height = 600;
$rv.Width = 800;
$rv.RefreshReport();
#--------------------------------------------------------------
#Show as Dialog Using Windows Form
#--------------------------------------------------------------
#create a new form
$form = New-Object Windows.Forms.Form;
#we're going to make it just slightly bigger than
$form.Height = 610;
$form.Width= 810;
$form.Controls.Add($rv);
$rv.Show();
$form.ShowDialog();
#--------------------------------------------------------------
#Export to Excel
#--------------------------------------------------------------
#now let's try exporting to Excel
$mimeType = $null;
$encoding = $null;
$extension = $null;
$streamids = $null;
$warnings = $null;
$bytes = $rv.ServerReport.Render("Excel", $null,
[ref] $mimeType,
[ref] $encoding,
[ref] $extension,
[ref] $streamids,
[ref] $warnings);
$file = "C:VANPASSsamplereport.xls";
$fileStream = New-Object System.IO.FileStream($file,
[System.IO.FileMode]::OpenOrCreate);
$fileStream.Write($bytes, 0, $bytes.Length);
$fileStream.Close();
#let's open up our excel application
$excel = New-Object -comObject Excel.Application
$excel.visible = $true;
$excel.Workbooks.Open($file) | Out-Null;
#--------------------------------------------------------------
#Export to PDF
#--------------------------------------------------------------
$bytes = $rv.ServerReport.Render("PDF", $null,
[ref] $mimeType,
[ref] $encoding,
[ref] $extension,
[ref] $streamids,
[ref] $warnings);
$file2 = "C:VANPASSsamplereport.pdf";
$fileStream = New-Object System.IO.FileStream($file2,
[System.IO.FileMode]::OpenOrCreate);
$fileStream.Write($bytes, 0, $bytes.Length);
$fileStream.Close();
[System.Diagnostics.Process]::Start($file2)
#--------------------------------------------------------------
#Other ways to render or access your reports:
#SSRS Web Services
#-------------------------------------------------------------- |
VN:F [1.9.22_1171]
Rating: 10.0/10 (12 votes cast)
VN:F [1.9.22_1171]
