Render SSRS Report with parameters using SOAP in Powershell

Question

I have been doing this for many years without much effort. Essentially, I'm trying to create a simple library for outputting SSRS reports using Powershell. I use Powershell in an attempt to facilitate development later (instead of coding a C # application for each project). This will mainly be used to plan various events with reports.

I have a report mainly working in Powershell. The only thing I can’t understand is to specify the report parameters before calling the rendering method. I found a lot of code related to C # and VB (which I used in other SSRS projects), however I cannot convert it to Powershell.

Since I'm fairly new to Powershell, I am not familiar with the correct way to do this. Here is the code I used:

$ReportExecutionURI = "http://glitas10//ReportServer//ReportExecution2005.asmx?wsdl"
$ReportPath = "/Financial/ExpenseReportStub"
$format = "PDF"

$deviceInfo = "<DeviceInfo><NoHeader>True</NoHeader></DeviceInfo>"
$extension = ""
$mimeType = ""
$encoding = ""
$warnings = $null
$streamIDs = $null

$Reports = New-WebServiceProxy -Uri $ReportExecutionURI -UseDefaultCredential

# Load the report 
$Report = $Reports.GetType().GetMethod("LoadReport").Invoke($Reports, @($ReportPath, $null))

# Render the report
$RenderOutput = $Reports.Render($format, $deviceInfo, [ref] $extension, [ref] $mimeType, [ref] $encoding, [ref] $warnings, [ref] $streamIDs)

This works great with reports that clearly don't require parameters.

Any ideas on what I need to do to instantiate the correct object and pass parameters?

Answer 1

Here is some information about the solution I used if someone else should do the same. It works very well.

, , DLL Powershell script. , . -, script DLL. -, DLL SSRS. , DLL.

-. , ParameterValue. :

# Create a proxy to the SSRS server and give it the namespace of 'RS' to use for
# instantiating objects later.  This class will also be used to create a report
# object.
$reportServerURI = "http://<SERVER>/ReportServer/ReportExecution2005.asmx?WSDL"
$RS = New-WebServiceProxy -Class 'RS' -NameSpace 'RS' -Uri $reportServerURI -UseDefaultCredential
$RS.Url = $reportServerURI

# Set up some variables to hold referenced results from Render
$deviceInfo = "<DeviceInfo><NoHeader>True</NoHeader></DeviceInfo>"
$extension = ""
$mimeType = ""
$encoding = ""
$warnings = $null
$streamIDs = $null

# Next we need to load the report. Since Powershell cannot pass a null string
# (it instead just passses ""), we have to use GetMethod / Invoke to call the
# function that returns the report object.  This will load the report in the
# report server object, as well as create a report object that can be used to
# discover information about the report.  It not used in this code, but it can
# be used to discover information about what parameters are needed to execute
# the report.
$reportPath = "/PathTo/Report"
$Report = $RS.GetType().GetMethod("LoadReport").Invoke($RS, @($reportPath, $null))

# Report parameters are handled by creating an array of ParameterValue objects.
$parameters = @()

$parameters += New-Object RS.ParameterValue
$parameters[0].Name  = "Parameter 1"
$parameters[0].Value = "Value"

$parameters += New-Object RS.ParameterValue
$parameters[1].Name  = "Parameter 2"
$parameters[1].Value = "Value"

# Add the parameter array to the service.  Note that this returns some
# information about the report that is about to be executed.
$RS.SetExecutionParameters($parameters, "en-us") > $null

# Render the report to a byte array.  The first argument is the report format.
# The formats I've tested are: PDF, XML, CSV, WORD (.doc), EXCEL (.xls),
# IMAGE (.tif), MHTML (.mhtml).
$RenderOutput = $RS.Render('PDF',
    $deviceInfo,
    [ref] $extension,
    [ref] $mimeType,
    [ref] $encoding,
    [ref] $warnings,
    [ref] $streamIDs
)

# Convert array bytes to file and write
$Stream = New-Object System.IO.FileStream("output.pdf"), Create, Write
$Stream.Write($RenderOutput, 0, $RenderOutput.Length)
$Stream.Close()

, . , , , SSRS. , . , , 20-30 .

Answer 2

, , MHT : , CDO.Message - , , MHTML . () VB , ; -)!

################## Send MHTML email ##############################
# use antiquated CDO to send mhtml as email body

$smtpServer = "my-mail-server"
$smtpSubject = "MHT file sent as body of email"
$smtpTo = "[e-mail]"
$smtpFrom = "[e-mail]"
$MHTMLfile = "my-MHT-File.mht
# e.g. from an SSRS.Render


$AdoDbStream = New-Object -ComObject ADODB.Stream
$AdoDbStream.Charset = "ascii"
$AdoDbStream.Open()
$AdoDbStream.LoadFromFile($MHTMLfile)
$CdoMessage = New-Object -ComObject CDO.Message
$CdoMessage.DataSource.OpenObject($AdoDbStream,"_Stream")

$SendUsingPort = 2
$smtpPort = 25

$cfg = "http://schemas.microsoft.com/cdo/configuration/"
$CdoMessage.Configuration.Fields.Item($cfg + "sendusing") =  $SendUsingPort
$CdoMessage.Configuration.Fields.Item($cfg + "smtpserver") = $SmtpServer
$CdoMessage.Configuration.Fields.Item($cfg + "smtpserverport") = $smtpPort 

$CdoMessage.To      = $smtpTo
$CdoMessage.From    = $smtpFrom
$CdoMessage.Subject = $smtpSubject

$CdoMessage.MimeFormatted = $true
$CdoMessage.Configuration.Fields.Update()

WRITE-HOST "Sending email"
$CdoMessage.Send()