#==============================================================
#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
#--------------------------------------------------------------