Schedule 1 Report with multiple parameter values

Question

I have a report in SSRS, which has a parameter in it. For each possibility in the parameter, I need an Excel file. This comes down to 50 Excel files. the only way I know to schedule a report is to go to the reporting services home page, go to my report, click manage, click subscriptions > New subscription and to enter a file name, path, user name, password, schedule, parameter and ultimately press OK.

Is there a quicker way to do this, or is there a way which allows me to create the 50 reports more quickly, like copying a subscription or something like that?

I think you need a specific edition, which we don't have. I only have the choices "Delete" and "New Subscription" — user1261104, Aug 20 '15 at 09:49

score 0 · Answer 1 · answered Aug 20 '15 at 15:49

0

try creating a ssis package and running the report for all values of the parameter. i had seen someone do this in my previous company.

data driven subscriptions are available only in enterprise and developer editions - yours could be standard.

answered Aug 20 '15 at 15:49

SQLDev

45
8

score 0 · Answer 2 · edited May 23 '17 at 11:52

You could also write a script in PowerShell or write an app in C#/VB. Here is an example done in PowerShell. Here is an example done in C#. Using either of these approaches, you could programmatically render the reports as you see fit. You can also create subscriptions this way as well.

PowerShell solution to the OP:

# 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 = "http://<SERVER>/ReportServer/ReportExecution2005.asmx?WSDL"
$RS = New-WebServiceProxy -Class 'RS' -NameSpace 'RS' -Uri $reportServerURI -UseDefaultCredential
$RS.Url = $reportServerURI

# Set up some variables to hold referenced results from Render
$deviceInfo = "<DeviceInfo><NoHeader>True</NoHeader></DeviceInfo>"
$extension = ""
$mimeType = ""
$encoding = ""
$warnings = $null
$streamIDs = $null

# Next we need to load the report. Since Powershell cannot pass a null string
# (it instead just passes ""), 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 = "/PathTo/Report"
$Report = $RS.GetType().GetMethod("LoadReport").Invoke($RS, @($reportPath, $null))

# Report parameters are handled by creating an array of ParameterValue objects.
# $excelInput: either pass in as a parameter and run 50 times, or reset
# this value and run it each time with the updated excel file
$excelInput = "<ExcelFile>"; 
$parameters = @()

$parameters += New-Object RS.ParameterValue
$parameters[0].Name  = "Excel Input File"
$parameters[0].Value = $excelInput

# 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

# 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('PDF',
    $deviceInfo,
    [ref] $extension,
    [ref] $mimeType,
    [ref] $encoding,
    [ref] $warnings,
    [ref] $streamIDs
)

# Convert array bytes to file and write
$OutputFile = $excelInput + ".pdf"
$Stream = New-Object System.IO.FileStream($OutputFile), Create, Write
$Stream.Write($RenderOutput, 0, $RenderOutput.Length)
$Stream.Close()

Has anyone had a chance to validate the above power shell script? Would an example in a different format work better? — Matt Jones MSFT, Jan 06 '16 at 18:11

Schedule 1 Report with multiple parameter values

2 Answers2