Home > Powershell, SSRS > Automate SSRS reports using Powershell – (Loop through reports, specify timeout, call a query to get values)

Automate SSRS reports using Powershell – (Loop through reports, specify timeout, call a query to get values)

# Functions to Call a SQL Query
function exec-query ($SqlQuery)
{
$SQLServer = “10.100.XX.XX\SQLEXPRESS” #use Server\Instance for named SQL instances!
$SQLDBName = “ReportServer$SQLEXPRESS”
$ReportArray = @()

$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = “Server = $SQLServer; Integrated Security = True”
$Connection.Open()

$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Command.CommandText = $SqlQuery

$Reader = $Command.ExecuteReader()
$Counter = $Reader.FieldCount

while ($Reader.Read())
{
for ($i = 0; $i -lt $Counter; $i++)
{
$ReportArray+=$Reader.GetValue($i)
}
}
$Connection.Close()
Return $ReportArray
}
# End of exec-query

# Main starts here

#REPORT SERVER
$URI = “http://dev.XXX.com/ReportServer_SQLEXPRESS/ReportExecution2005.asmx?WSDL”

# Report render format
$format = “EXCEL”
$deviceinfo = “”
$extention = “”
$mimeType = “”
$encoding = “”
$warnings = $null
$streamIDs = $null

# Report Credentials
$username = ‘XX’
$password = ‘XXXX’

$credentials = New-Object System.Management.Automation.PSCredential -ArgumentList @($username,(ConvertTo-SecureString -String $password -AsPlainText -Force))
$Reports = New-WebServiceProxy -Uri $URI –Credential $credentials -namespace “ReportExecution2005”
$rsExec = new-object ReportExecution2005.ReportExecutionService
$rsExec.Credentials = New-Object System.Management.Automation.PSCredential -ArgumentList @($username,(ConvertTo-SecureString -String $password -AsPlainText -Force))
# Specify Report timeout to 30 minutes
$rsExec.Timeout = 1800000;

# Set Variables to get Parameter Values for Reports
$GetStartDate = “select cast(DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) as date) as START_OF_YEAR”
$p_start_date=exec-query($GetStartDate)

$GetEndDate = “SELECT cast(DATEADD(dd,-(DAY(getdate())),getdate()) as date) as END_OF_PREV_MONTH”
$p_end_date=exec-query($GetEndDate)

$GetSetOfVal = “select distinct CO from XXX.XXX order by 1”
$p_mgmt_co=exec-query($GetSetOfVal)

#List reports which you want to render
$ReportList = (“REPORT_1″,”REPORT_2”)

foreach ($ReportPath in $ReportList)
{
$ObjectList = New-Object ‘System.Collections.Generic.List[System.Object]’
$ObjectList.Add($ReportPath)
$ObjectList.Add($List)

#Load the selected report.
$rsExec.GetType().GetMethod(“LoadReport”).Invoke($rsExec, $ObjectList) | out-null

$param1 = new-object ReportExecution2005.ParameterValue
$param1.Name = “start”
$param1.Value = (get-variable $param1.Name).value

$param2 = new-object ReportExecution2005.ParameterValue
$param2.Name = “end”
$param2.Value = (get-variable $param2.Name).value

$param4 = new-object ReportExecution2005.ParameterValue
$param4.Name = “prop”
#Not specifying value for company name

$param3 = new-object ReportExecution2005.ParameterValue
$param3.Name = “com”

$SetOfValArr = (get-variable $param3.Name).value

# Loop through set of values for

for ($i = 0; $i -lt $SetOfValArr.length; $i++)
{
$param3.Value = $SetOfValArr[$i]

$parameters = [ReportExecution2005.ParameterValue[]] ($param1,$param2,$param3, $param4)

$RepSplit = $ReportPath.Split(“/”)
Set-Location “C:\temp”

$dr = 1
while($dr -le $RepSplit.length-2)
{
IF (Test-Path $RepSplit[$dr])
{
Set-Location $RepSplit[$dr]
}
ElSE
{
New-Item $RepSplit[$dr] -type directory
Set-Location $RepSplit[$dr]
}
$dr++
}

$StagFileNameExt = $param3.Value

$StagFileNameExtFix = $StagFileNameExt.Replace(“/”,”-“)

$StagFileName = $RepSplit[$dr]
$CurDir = get-location

$OutputRepName = “$CurDir\$StagFileNameExtFix`_$StagFileName.xls”

IF (Test-Path $OutputRepName)
{
Write “Report $StagFileName already generated for $StagFileNameExtFix”
}
ELSE
{
Write “Running Report $StagFileName For $StagFileNameExtFix”

#Set ExecutionParameters
$ExecParams = $rsExec.SetExecutionParameters($parameters, “en-us”);
$Result = $rsExec.Render($format, $deviceInfo,[ref] $extention, [ref] $mimeType,[ref] $encoding, [ref] $warnings, [ref] $streamIDs)
#Write the report
[System.IO.File]::WriteAllBytes($OutputRepName,$Result)
}

}
}

# End of Main

Advertisements
Categories: Powershell, SSRS
  1. kiquenet
    June 27, 2012 at 1:29 pm

    Where is [ReportExecution2005.ReportExec
    utionService] type?, not found in my PS script…

    thx

  2. June 27, 2012 at 4:14 pm

    Hi Kiquenet,
    Please search into your SQL Server report service folder for asmx file.
    You might have to change the code based on which version you are using and file you find.
    I am using SQL server 2008 express edition.

    Here is where mine is.
    C:\Program Files\Microsoft SQL Server\MSRS10.SQLEXPRESS\Reporting Services\ReportServer

    I hope this helps.
    Good luck.
    Kalpesh

  3. kiquenet
    June 28, 2012 at 1:45 pm

    Thx, I do this:

    wsdl https://myserver.domain.net/ReportServer/ReportService2005.asmx?WSDL /namespace:ReportExecution2005

    csc /t:library ReportingService2005.cs

    I copy dll to same folder where I copy ps1 file.

    In my ps1 script

    $ReportingService2005 = (Join-Path $ScriptDirectory ReportingService2005.dll)
    $void=[Reflection.Assembly]::LoadFrom($ReportingService2005)

    $Reports = New-WebServiceProxy -Uri $webServiceSSRSRDL -UseDefaultCredential -namespace “ReportExecution2005”

    $rsExec = new-object ReportExecution2005.ReportingService2005
    $rsExec.Credentials = [System.Net.CredentialCache]::DefaultCredentials

    #List reports which you want to render
    $ReportList = (“/xxx/PuestosTipo”, “/xxx/Roles”)

    foreach ($ReportPath in $ReportList)
    {
    $ObjectList = New-Object “System.Collections.Generic.List[System.Object]”
    $ObjectList.Add($ReportPath)
    $ObjectList.Add($List)

    #Load the selected report.
    $rsExec.GetType().GetMethod(“LoadReport”).Invoke($rsExec, $ObjectList) | out-null
    }

    I get this error:
    You cannot call a method on a null-valued expression.
    . StackTrace: en System.Management.Automation.ParserOps.CallMethod(Token token, Object target, String methodName, Object[] paramArray, Boolean callStatic, Object valueToSet)
    en System.Management.Automation.MethodCallNode.InvokeMethod(Object target, Object[] arguments, Object value)
    en System.Management.Automation.MethodCallNode.Execute(Array input, Pipe outputPipe, ExecutionContext context)
    en System.Management.Automation.PipelineNode.Execute(Array input, Pipe outputPipe, ArrayList& resultList, ExecutionContext context)
    en System.Management.Automation.StatementListNode.ExecuteStatement(ParseTreeNode statement, Array input, Pipe outputPipe, ArrayList& resultList, ExecutionContext context)

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: