PowerShell – Retrieve a SSRS Report in a .NET GUI

May 5, 2019

Ever wanted to create you Offline Reporting Engine based on SSRS using PowerShell !

The objective is to run the RDL reports server side and display in .NET GUI client side.

PREREQUISITES :

You first need to download the .NET Assemblies using the SSRS redistributables.

Download here :

2008 / 2010 / 2012 or 2015 for a newer version see here

SOLUTION :

The solution is based on .Net Scripting in PowerShell using a GUI.

REMOTE processing mode :

CLS
# [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.ReportViewer.WinForms”)
Add-Type -Path "C:\WINDOWS\assembly\GAC_MSIL\Microsoft.ReportViewer.WinForms\12.0.0.0__89845dcd8080cc91\Microsoft.ReportViewer.WinForms.dll"

#Windows.Forms for viewing dialog box
# [void][System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")
Add-Type -AssemblyName "System.Windows.Forms"

# for credentials, if needed
# [void][System.Reflection.Assembly]::LoadWithPartialName("System.Net")
Add-Type -AssemblyName System.Net

$credential = Get-Credential

$rv = New-Object Microsoft.Reporting.WinForms.ReportViewer;
$rv.ServerReport.ReportServerCredentials.NetworkCredentials = $credential

$rv.ProcessingMode = "Remote"

$rv.ServerReport.ReportServerUrl = "http://YourServer/Reportserver"
$rv.ServerReport.ReportPath = "/YourReportName”

$rv.Height = 600;
$rv.Width = 800;
$rv.RefreshReport();

#---------------------
# Show as Dialog Using Windows Form
#---------------------
# Create a new form
$form = New-Object Windows.Forms.Form;

# Form Properties
$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:\Temp\SampleReport.xls”;
$fileStream = New-Object System.IO.FileStream($file,
[System.IO.FileMode]::OpenOrCreate);
$fileStream.Write($bytes, 0, $bytes.Length);
$fileStream.Close();

#l 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:\Temp\SampleReport.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)
image

The result is the report in your own GUI that runs locally and is processed centrally, when you use REMOTE

If the report has Parameters it will prompt you to enter them just like in the Web Form.

image

After the GUI is close the script will be EXPORT the content to EXCEL and PDF too.

You will have the options to EXPORT to all supported formats, or add your own extension if you are working server side.

image

LOCAL processing mode :

Most interesting is that you can run the Report Viewer Control in LOCAL processing mode !

Which means that you can create a Local Reporting Engine that runs the RDL reports from you client in a PowerShell GUI

image

And additionally you can add any kind of .Net support Data Source, like in this case an SQLite database !

Which is not supported by server side SSRS…

How cool is that ! Surprised smile

For more info see here

Happy Scripting Smile

Enjoy!

Advertisements