Archive

Archive for November, 2011

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

November 4, 2011 3 comments

# 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

Categories: Powershell, SSRS

Search a string in a Long column

November 2, 2011 Leave a comment

You can not search LONG columns in Oracle.

Here is what you can do to search for alll views which has word “Hello” in it. Little scripting

declare

cursor c_dbv is select owner,view_name,text from all_views ;

txt varchar(32000);

searchstring varchar(100) := ‘Hello’;

begin

for ct in c_dbv loop

if instr(lower(ct.text),lower(searchstring)) > 0 then

dbms_output.put_line(ct.owner||’.’||ct.view_name);

end if;

end loop;

end;

Categories: Uncategorized