Du möchtest ein parametrisierter Reports in allen Varianten zur Archivierung in einem Format wie Excel exportieren?
Das wollte ich auch und habe es folgendermaßen gelöst!
Zunächst habe ich versucht alles zu Parallelisieren um den Export zu beschleunigen.
Das hat auch gut funktioniert, jedoch mit dem Nachteil, dass ich die aufgetretenen Fehler in den Background Tasks nicht gesehen habe.
Hinzukommt, dass der SSRS anscheinend bei Parallele Abfragen vom selben Power Shell auf seltsame Ideen kommt, so wurden die Parameter nicht immer sauber beibehalten.
Schlussendlich bin ich dann doch auf die Serielle Verarbeitung zurück gegangen.
Dadurch sind dann zwar aus 12 Minuten Verarbeitungszeit 12 Stunden geworden, da es sich allerdings um eine einmalige Archivierung handelte war das annehmbar.
Im Netz habe ich viele Beispiele gefunden um so einen Export zu bewerkstelligen, jedoch alle ein wenig veraltet und nicht ganz rund.
Ein fieser Fehler auf den ich gestoßen war, war ein Timeout beim Rendern des Reports.
Nachdem ich zunächst am SSRS und anschließend am SSAS die Timeouts hochgestellt hatte, bin ich nach etwas Recherche darauf gestoßen, dass der SSRS WebProxy ebenfalls einen Timeout hat, welcher standardmäßig bei 100000 Millisekunden liegt.
Für manch einen größeren Report etwas zu wenig, vor allem wenn ich bedenke, dass die Timeouts der anderen Server (SSRS, SSAS) bei rund 360000 Millisekunden liegen.
Gefunden habe ich den Hinweis hier: Rendering timeout for SSRS
Und eingestellt habe ich es folgendermaßen.
$RS.Timeout = 360000
Ein weiterer unangenehmer Fehler war die Maximale Anzahl von Zeilen in Excel wenn ich den Report im ‘EXCEL’ Format exportiere, denn Excel ist nicht gleich Excel.
Während das Format ‘EXCEL’ in den Beiträgen welche ich gefunden habe genutzt wird, stieß ich auf das Problem, dass maximal 65536 Zeilen genutzt werden können.
Das liegt daran, dass es sich um Excel 2003 handelt. Umgehen kann ich das, indem ich Excel 2007-2010 durch das Format ‘EXCELOPENXML’ nutze.
Die Fehlerbeschreibung ist hier zu finden: Maximum rows: 65536
Und gelöst wird das im Skript folgendermaßen.
$format = 'EXCELOPENXML'
Nun zum Schluss das komplette Skript um Reports mit bis zu drei Parametern in beliebiger Kombination zu archivieren.
Die Parameter Anzahl lässt sich auch beliebig erweitern.
$StopWatch = [diagnostics.stopwatch]::StartNew()
<#
Syntax -ExecutionPolicy Policy A new execution policy for the shell.
Bypass Nothing is blocked and there are no warnings or prompts.
-Force Suppress all prompts.
By default, Set-ExecutionPolicy displays a warning whenever the execution policy is changed.
-Scope ExecutionPolicyScope The scope of the execution policy.
Process Affect only the current PowerShell process.
#>
Set-ExecutionPolicy -Scope Process -ExecutionPolicy Bypass -Force
# --------------------------------------------------
#region - Setup custom functions
# --------------------------------------------------
# Create custom function to import into runspace session state
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.
$reportServerURI = 'https://{0}/{1}/ReportExecution2005.asmx?WSDL' -f $server, $instance
$RS = New-WebServiceProxy -Class 'RS' -Namespace 'RS' -Uri $reportServerURI -UseDefaultCredential
$RS.Url = $reportServerURI
$RS.Timeout = 360000
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 is not used in this code, but it can
# be used to discover information about what parameters are needed to execute
# the report.
$Report = $RS.GetType().GetMethod('LoadReport').Invoke($RS, @($reportPath, $null))
# initialise empty parameter holder
$parameters = @()
$RS.SetExecutionParameters($parameters, 'de-de') > $null
return $Report
}
function AddParameter($params, $name, $val = $null)
{
$par = New-Object -TypeName RS.ParameterValue
$par.Name = $name
$par.Value = $val
$params += $par
return ,$params
}
function GetReportInFormat($RS, $Report, $params, $outputpath, $prefix, $format)
{
# Set up some variables to hold referenced results from Render
$deviceInfo = '<DeviceInfo><NoHeader>True</NoHeader></DeviceInfo>'
$extension = ''
$mimeType = ''
$encoding = ''
$warnings = $null
$streamIDs = $null
# Determine file name
$parts = $Report.ReportPath.Split('/')
$filename = $parts[-1] + $prefix + '.'
switch($format)
{
'EXCEL' #Excel 2003
{
$filename = $filename + 'xls'
}
'EXCELOPENXML' #Excel 2007 - 2010
{
$filename = $filename + 'xlsx'
}
'WORD'
{
$filename = $filename + 'doc'
}
'IMAGE'
{
$filename = $filename + 'tif'
}
default
{
$filename = $filename + $format
}
}
if($outputpath.EndsWith('\\'))
{
$filename = $outputpath + $filename
}
else
{
$filename = $outputpath + '\\' + $filename
}
$filename # Return filename
# 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, 'de-de') > $null
# Render the report to a byte array. The first argument is the report format.
$RenderOutput = $RS.Render($format,
$deviceInfo,
[ref] $extension,
[ref] $mimeType,
[ref] $encoding,
[ref] $warnings,
[ref] $streamIDs
)
# Convert array bytes to file and write
$Stream = New-Object -TypeName System.IO.FileStream -ArgumentList ($filename), Create, Write
$Stream.Write($RenderOutput, 0, $RenderOutput.Length)
$Stream.Close()
}
# --------------------------------------------------
#endregion - Setup custom functions
# --------------------------------------------------
$server = 'myssrsserver.ownserver'
$instance = 'ReportServer'
# Report paths could also be dynamic
$ReportPaths = @('/SSRS/FirstFolder/FirstReport', '/SSRS/FirstFolder/SecondReport', '/SSRS/FirstFolder/ThirdReport', '/SSRS/SecondFolder/FirstReport', '/SSRS/SecondFolder/SecondReport', '/SSRS/SecondFolder/ThirdReport')
$RS = GetRSConnection -server $server -instance $instance
# --------------------------------------------------
#region - Perform export Tasks
# --------------------------------------------------
$ExportRootPath = '{0}\TMP\ALL' -f $env:HOMEDRIVE
New-Item -ItemType Directory -Force -Path $ExportRootPath
foreach($reportPath in $ReportPaths)
{
$Report = $null
$Report = GetReport -RS $RS -reportPath $reportPath
if($Report.Parameters.Count -eq 1)
{
foreach($FirstValue in $Report.Parameters[0].ValidValues.Value)
{
$parts = $Report.ReportPath.Split('/')
$filename = $parts[-1]
$ExportPath = '{0}\{1}' -f $ExportRootPath, $filename
$null = New-Item -ItemType Directory -Force -Path ('{0}' -f $ExportPath)
$params = @()
$params = AddParameter -params $params -name $Report.Parameters[0].Name -val $FirstValue
GetReportInFormat -RS $RS -report $Report -params $params -outputpath $ExportPath -prefix ('_{0}' -f $FirstValue) -format 'EXCELOPENXML'
}
}
elseif($Report.Parameters.Count -eq 2)
{
foreach($FirstValue in $Report.Parameters[0].ValidValues.Value)
{
foreach($SecondValue in $Report.Parameters[1].ValidValues.Value)
{
$parts = $Report.ReportPath.Split('/')
$filename = $parts[-1]
$ExportPath = '{0}\{1}' -f $ExportRootPath, $filename
$null = New-Item -ItemType Directory -Force -Path ('{0}' -f $ExportPath)
$params = @()
$params = AddParameter -params $params -name $Report.Parameters[0].Name -val $FirstValue
$params = AddParameter -params $params -name $Report.Parameters[1].Name -val $SecondValue
GetReportInFormat -RS $RS -report $Report -params $params -outputpath $ExportPath -prefix ('_{0}_{1}' -f $FirstValue, $SecondValue) -format 'EXCELOPENXML'
}
}
}
elseif($Report.Parameters.Count -eq 3)
{
foreach($FirstValue in $Report.Parameters[0].ValidValues.Value)
{
foreach($SecondValue in $Report.Parameters[1].ValidValues.Value)
{
foreach($ThirdValue in $Report.Parameters[2].ValidValues.Value)
{
$parts = $Report.ReportPath.Split('/')
$filename = $parts[-1]
$ExportPath = '{0}\{1}' -f $ExportRootPath, $filename
$null = New-Item -ItemType Directory -Force -Path ('{0}' -f $ExportPath)
$params = @()
$params = AddParameter -params $params -name $Report.Parameters[0].Name -val $FirstValue
$params = AddParameter -params $params -name $Report.Parameters[1].Name -val $SecondValue
$params = AddParameter -params $params -name $Report.Parameters[2].Name -val $ThirdValue
GetReportInFormat -RS $RS -report $Report -params $params -outputpath $ExportPath -prefix ('_{0}_{1}_{2}' -f $FirstValue, $SecondValue, $ThirdValue) -format 'EXCELOPENXML'
}
}
}
}
}
# --------------------------------------------------
#endregion - Perform export Tasks
# --------------------------------------------------
$StopWatch.ElapsedMilliseconds