Render SSRS Report with parameters using SOAP in Powershell

Question

I've been toying with this for days with no luck. Essentially I'm trying to build a simple library to render SSRS reports using Powershell. I'm using Powershell in an attempt to ease development later on (Instead of coding a C# app for each project). Mostly this will be used to schedule various things with reports.

I've got report rendering mostly working in Powershell. The one thing I can't figure out is how to supply parameters to the report before calling the render method. I've found plenty of code pertaining to C# and VB (which I've used in other SSRS projects), however I'm unable to convert this to Powershell.

As I'm fairly new to Powershell, I'm unfamiliar with the proper way to do this. Here's the code I've been using:

$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)

That works fine on reports that don't require parameters, obviously.

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

Linette

Why can't you call LoadReport directly on $Reports? What do you see when you execute this $Reports | Get-Member? Doesn't LoadReport show up? If so, what signature does it have? Also, see if this post helps - social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/… – Bort

I tried that, but it seems to not like the arguments I supply. For some reason using Invoke works. However, that part works, and shouldn't interfere with supplying parameters. – Linette

The add to my previous comment, it appears using $Reports.LoadReport will not accept a null historyID, which is why the code I found was using Invoke. – Linette

I finally succeeded by taking a slightly different approach. I'll write up an answer and post the code later today. – Linette

Answer 1

Here's some information on the solution that I ended up using, in case anyone else needs to do the same. It works really well.

The first approach that worked was building a DLL to use by the Powershell script. This worked fine, but it causes two problems. First, your script had to tote around a DLL. Second, this DLL was tied to a specific SSRS server. In order to access another server, you had to use multiple DLLs.

Eventually, I moved back to using a web proxy. The key here is to use namespaces so that you can instantiate a ParameterValue object. Here's the code:

# 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's 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()

It seems rather easy, and it is. This method works exceptionally well and is the method I'm using now to render and email scheduled reports, as it provides much more flexibility than the built in SSRS scheduling. In addition, it's relatively fast. One of the scripts I'm using to mail out reports can render and send out about 20-30 reports a minute.

Linette

any solution with full source code for render, generate to file and mail it ? – Contrived

Nice one, the way I tried was $webclient.DownloadFile($url, $file) ,then send out by email. – Lowrie

In a more recent version of powershell, the constant [NullString]::Value was added to avoid the problem where a $null gets coerced to the empty string when being passed to a method as a string. Pass this constant instead of $null and you can avoid the need to use reflection. If [NullString] is not available as an accelerator, [System.Management.Automation.Internal.AutomationNull]::Value is equivalent (but slightly unwieldy). – Ferd

Answer 2

I had a similar issue. It took time to figure out the issue. You should not "revoke" the report without parameters if needed Therefore the code should look like this:

try {
    <# Despose and clear resources if open #>
    if ($RS) { $RS.Dispose() }
    if ($Stream) { $Stream.Close() }

    <# Create Report Service #>
    [string]$reportServerURI = "<SSRS Service URL>"
    $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

    <# Initial Report #>
    $reportPath = "<Full path/URL to rdl file>"
    ## Do not revoke the report ## $Report = $RS.GetType().GetMethod("LoadReport").Invoke($RS, @($reportPath, $null))
    <# Initial Report Parameters Array #> 
    $Parameters = $RS.GetType().GetMethod("LoadReport").Invoke($RS, @($reportPath, $null)).Parameters
    
    <# Populate Report Parameters values #>
    $Params = @()
    Foreach ($Parameter in $Parameters ) {
        $par1 = New-Object RS.ParameterValue;
        $Par1.Name = $Parameter.Name;
        $Par1.Label = $Parameter.Name;
        switch ($Par1.Name) {
                "<1st Param Name>"      { $par1.Value = <1st Param Value>; break }
                "<2nd Param Name>"      { $par1.Value = <2nd Param Value>; break }
                ...
                "<#n Param Name>"      { $par1.Value = <#n Param Value>; break }
            }
        $Params += $Par1;
        }

    <# Execute/invoke the report with the parameters #>
    $RS.SetExecutionParameters($Params, "en-us") > $null
    
    <# Set report render output format#>
    [string]$format = <"PDF","Excel" etc.>

    <# Eecute Report render #>
    try { $RenderOutput = $RS.Render($format,
    $deviceInfo,
    [ref] $extension,
    [ref] $mimeType,
    [ref] $encoding,
    [ref] $warnings,
    [ref] $streamIDs)       
    } catch { Log-Message -message "Unable to render or save the report due to an error." -IsError $true; throw
    }
    
    <# Convert array bytes to file and write #>
    $Stream = New-Object System.IO.FileStream(<Final Report Output File), Create, Write
    $Stream.Write($RenderOutput, 0, $RenderOutput.Length)
    $Stream.Close()
    if ($RS) { $RS.Dispose() }
    }catch{ Log-Message -message "Error in Execute-Report, could not stream or other error." -IsError $true; throw }

Problem solved.

Energid

Answer 3

Had the same issue, furthermore wanted to send the generated MHT file as an email body: The following was found to work The old CDO.Message is the only thing I found that allows sending a MHTML file as an email body. Below is a (working) translation of a VB program Old but simple ;-)!

################## 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 = "[email protected]"
$smtpFrom = "[email protected]"
$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()

Unprincipled