none
Rendering SQL Server Reporting Services from PowerShell

    Question

  • Hi,

    I'm trying to render reports from SQL Reporting Services using the PowerShell New-WebServiceProxy Cmdlet, but that is failing on the historyID parameter for the LoadReport method.

    This is what I've done so far:
    1. Searched on MSDN for info on rendering reports. http://msdn.microsoft.com/en-us/library/reportexecution2005.reportexecutionservice.loadreport.aspx and http://msdn.microsoft.com/en-us/library/reportexecution2005.reportexecutionservice.render.aspx
    2. As you see in the example you need a historyid parameter (and if there is no snapshot this historyid needs to be empty)
    3. So I tried to use different values for this (empty) historyID parameter but they all failed.

    Example PowerShell Script:

    $URI = "http://localhost/ReportServer//ReportExecution2005.asmx?wsdl"
    $ReportPath = "/My Reports/Licenses"
    $historyID = "null"
    $Reports = New-WebServiceProxy -Uri $URI -UseDefaultCredential
    $Reports.LoadReport($ReportPath, $historyID)

    If I create a snapshot and use that date it works.

    But when there is no snapshot it does not work. Error:

    Exception calling "LoadReport" with "2" argument(s): "System.Web.Services.Proto
    cols.SoapException: The parameter value provided for 'snapshotID' does not matc
    h the parameter type. ---> Microsoft.ReportingServices.Diagnostics.Utilities.Pa
    rameterTypeMismatchException: The parameter value provided for 'snapshotID' doe
    s not match the parameter type. ---> System.FormatException: String was not rec
    ognized as a valid DateTime.
      at Microsoft.ReportingServices.WebServer.ReportExecution2005Impl.LoadReport(
    String Report, String HistoryID, ExecutionInfo2& executionInfo)
      at Microsoft.ReportingServices.WebServer.ReportExecutionService.LoadReport(S
    tring Report, String HistoryID, ExecutionInfo& executionInfo)"
    At C:\Users\username\AppData\Local\Temp\2\aad1e933-6350-43a6-a511-cdce0b9a271b.
    ps1:5 char:20
    + $Reports.LoadReport <<<< ($ReportPath, $historyID) | Tee-Object -FilePath d:\
    Temp\rendering.txt
      + CategoryInfo     : NotSpecified: (:) [], MethodInvocationException
      + FullyQualifiedErrorId : DotNetMethodException

    What do I need to use for the historyID variable if there is no snapshot? Tried the next ones and those didnot work:
    "null", "empty", [datetime]($historyID), " "

    Regards,

    Stefan Stranger

     

     

     


    Regards, Stefan Stranger Microsoft Services
    Wednesday, April 21, 2010 7:57 AM

Answers

  • Found the solution myself (with some help)

    $URI = "http://localhost//ReportServer//ReportExecution2005.asmx?wsdl"
    $ReportPath = "/My Reports/Licenses"
    $format = "CSV"
    $deviceinfo = "<DeviceInfo><NoHeader>True</NoHeader></DeviceInfo>"
    $extention = ""
    $mimeType = ""
    $encoding = "UTF-8"
    $warnings = $null
    $streamIDs = $null
    $Reports = New-WebServiceProxy -Uri $URI -UseDefaultCredential
    
    $params = @($ReportPath, $null)
    
    #Load the selected report.
    $Report = $Reports.GetType().GetMethod("LoadReport").Invoke($Reports, $params)
    
    #Render Report
    $render = $Reports.Render($format, $deviceInfo,[ref] $extention, [ref] $mimeType,[ref] $encoding, [ref] $warnings, [ref] $streamIDs)
    
    #Convert Array Bytes to string
    $Result = [text.encoding]::ascii.getString($render)
    $Result
    

    Regards, Stefan Stranger Microsoft Services
    Wednesday, April 21, 2010 7:58 PM

All replies

  • Found the solution myself (with some help)

    $URI = "http://localhost//ReportServer//ReportExecution2005.asmx?wsdl"
    $ReportPath = "/My Reports/Licenses"
    $format = "CSV"
    $deviceinfo = "<DeviceInfo><NoHeader>True</NoHeader></DeviceInfo>"
    $extention = ""
    $mimeType = ""
    $encoding = "UTF-8"
    $warnings = $null
    $streamIDs = $null
    $Reports = New-WebServiceProxy -Uri $URI -UseDefaultCredential
    
    $params = @($ReportPath, $null)
    
    #Load the selected report.
    $Report = $Reports.GetType().GetMethod("LoadReport").Invoke($Reports, $params)
    
    #Render Report
    $render = $Reports.Render($format, $deviceInfo,[ref] $extention, [ref] $mimeType,[ref] $encoding, [ref] $warnings, [ref] $streamIDs)
    
    #Convert Array Bytes to string
    $Result = [text.encoding]::ascii.getString($render)
    $Result
    

    Regards, Stefan Stranger Microsoft Services
    Wednesday, April 21, 2010 7:58 PM
  • I have report without parameters, 

    I get   InvokeMethodOnNull exception when I call 

    $Report = $Reports.GetType().GetMethod("LoadReport").Invoke($Reports, $params)

    my code

      

    Write-Host "Testing..."
    $ReportServerUri=$webServiceSSRSRDL
    $proxy = New-WebServiceProxy -Uri $ReportServerUri -UseDefaultCredential ;

    $catalogitemsarray = $proxy.ListChildren("/", $true);

    $reportpath = "/$reportFolder/$reportName";
    Write-Host "Report $reportpath " `r`n

    $report = $null;
    foreach ($catalogitem in $catalogitemsarray)
    {
    Write-Host "Report $catalogitem " `r`n
    }

    $ReportPath = "/Reale/Roles"
    $params = @($ReportPath, $null)

    #Load the selected report.
    $Report = $proxy.GetType().GetMethod("LoadReport").Invoke($proxy, $params)


    www.kiquenet.com/profesional

    Wednesday, June 27, 2012 1:21 PM