SSRS and PowerShell: Get report as Excel

Question

I'm trying to make PowerShell send a web request to our SSRS server and capture the results. I've hit a wall using the rs:FORMAT=EXCEL parameter in the SSRS url string. I have the following:

First, init the credentials:

$User = "MYDOMAIN\MyUser"
$PWord = ConvertTo-SecureString -String "WooHooStringP$W0rd" -AsPlainText -Force
$c = New-Object –TypeName System.Management.Automation.PSCredential –ArgumentList $User, $PWord

Now, request a report:

Invoke-WebRequest `
-UserAgent ([Microsoft.PowerShell.Commands.PSUserAgent]::InternetExplorer) `
-Credential $c `
-Uri "http://myserver/ReportServer_DEV/Pages/ReportViewer.aspx?/folder+path/report+name"

This works fine. I can even grab the results (enclosing this request and using ().Content). Then, specify a format instead of plain rendering:

Invoke-WebRequest `
-UserAgent ([Microsoft.PowerShell.Commands.PSUserAgent]::InternetExplorer) `
-Credential $c `
-Uri "http://myserver/ReportServer_DEV/Pages/ReportViewer.aspx?/folder+path/report+name&rs:format=HTML4.0"

Note the rs:Format specification? Works like a charm.

Then, for the grande finale, give me an Excel file:

Invoke-WebRequest `
-UserAgent ([Microsoft.PowerShell.Commands.PSUserAgent]::InternetExplorer) `
-Credential $c `
-Uri "http://myserver/ReportServer_DEV/Pages/ReportViewer.aspx?/folder+path/report+name&rs:format=EXCEL"

No can do, bud:

Invoke-WebRequest : The remote server returned an error: (401) Unauthorized.
At line:1 char:11
+ $bytez = (Invoke-WebRequest `
+           ~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-WebRequest], WebException
    + FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeWebRequestCommand

Why does the rs:format=EXCEL option throw an Unauthorised exception where all the other URLs are served by SSRS?

Brancusi

Answer 1

I've figured it out! I went about this the wrong way: SSRS offers access through a webservice that PowerShell can consume without the need to hack the URL and capture a response. I found a script that did this and modified it to suit my purpose:

function GetRSConnection($server, $instance)
{
    #   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.

    $User = "DOMAIN\Username"
    $PWord = ConvertTo-SecureString -String "Pa$$w0rd" -AsPlainText -Force
    $c = New-Object –TypeName System.Management.Automation.PSCredential –ArgumentList $User, $PWord

    $reportServerURI = "http://" + $server + "/" + $instance + "/ReportExecution2005.asmx?WSDL"

    $RS = New-WebServiceProxy -Class 'RS' -NameSpace 'RS' -Uri $reportServerURI -Credential $c
    $RS.Url = $reportServerURI
    return $RS
}

function GetReport($RS, $reportPath)
{
    #   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 = "/" + $reportPath
    $Report = $RS.GetType().GetMethod("LoadReport").Invoke($RS, @($reportPath, $null))

    # initialise empty parameter holder
    $parameters = @()
    $RS.SetExecutionParameters($parameters, "nl-nl") > $null
    return $report
}

function AddParameter($params, $name, $val)
{
    $par = New-Object RS.ParameterValue
    $par.Name = $name
    $par.Value = $val
    $params += $par
    return ,$params
}

function GetReportInFormat($RS, $report, $params, $outputpath, $format)
{
    #   Set up some variables to hold referenced results from Render
    $deviceInfo = "<DeviceInfo><NoHeader>True</NoHeader></DeviceInfo>"
    $extension = ""
    $mimeType = ""
    $encoding = ""
    $warnings = $null
    $streamIDs = $null

    #   Report parameters are handled by creating an array of ParameterValue objects.
    #   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
    $RS.SetExecutionParameters($params, "nl-nl") > $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($format,
        $deviceInfo,
        [ref] $extension,
        [ref] $mimeType,
        [ref] $encoding,
        [ref] $warnings,
        [ref] $streamIDs
    )

    #   Determine file name
    $parts = $report.ReportPath.Split("/")
    $filename = $parts[-1] + "."
    switch($format)
    {
        "EXCEL" { $filename = $filename + "xls" } 
        "WORD" { $filename = $filename + "doc" }
        "IMAGE" { $filename = $filename + "tif" }
        default { $filename = $filename + $format }
    }

    if($outputpath.EndsWith("\\"))
    {
        $filename = $outputpath + $filename
    } else
    {
        $filename = $outputpath + "\" + $filename
    }

    $filename

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

$RS = GetRSConnection -server "DEVBOX" -instance "ReportServer_DEV"
$report = GetReport -RS $RS -reportPath "folder name/report name"

$params = @()
$params = AddParameter -params $params -name "Month" -val "201311"

GetReportInformat -RS $RS -report $report -params $params -outputpath "i:\test" -format "EXCEL"

Brancusi

Thank you for sharing this. It came in handy for me today! It works very well. – Willena

Answer 2

Using web request:

[string]$Domain = "DomainUsername"
[string]$Username = "Username"
[string]$Password = "Password"
[string]$ReportServer = "http://ssrsreportserver/ReportServer/ReportExecution2005.asmx" #Report Server
[string]$ReportLocation = "/Report Location/Report Name" #Report Location ON SSRS
$ReportLocation = $ReportLocation.Replace("/", "%2f")
$ReportLocation = $ReportLocation.Replace(" ", "+")
[string]$outputFile = $PSScriptRoot + '\Report.xlsx' #Save location for the file

#If the report has any parameters
[string]$ParamString = "";
$ParamString += "&param1=paramvalue"
$ParamString += "&param2=paramvalue"

[string]$URL = $ReportServer + "?" + $ReportLocation + "&rs:Command=Render&rs:Format=" + "EXCELOPENXML" + "&rs:ParameterLanguage=en-GB" + $ParamString
Write-Host $URL
$Req = [System.Net.WebRequest]::Create($URL);
$Req.Credentials = new-object System.Net.NetworkCredential($Username, $Password, $Domain)
$Req.Timeout = 30000;

$WebStream = $Req.GetResponse().GetResponseStream();

$MemStream = New-Object System.IO.MemoryStream
$WebStream.CopyTo($MemStream);
[long]$Len = $MemStream.Length;

[byte[]]$outBytes = [System.Byte[]]::CreateInstance([System.Byte], $Len)
$MemStream.Seek(0, [System.IO.SeekOrigin]::Begin);
$MemStream.Read($outBytes, 0, [int]$Len);
$WebStream.Close();
$MemStream.Close();
$MemStream.Dispose();

$Stream = New-Object System.IO.FileStream($outputFile), Create, Write
$Stream.Write($outBytes, 0, $outBytes.Length)
$Stream.Close()
Invoke-Item $outputFile

Scraper