July 23, 2015 at 1:23 pm
Comments posted to this topic are about the item Stored Procedure to export report output to a file to any location
August 17, 2015 at 9:52 am
Great post! The params weren't being passed for me though in SQL/SSRS 2008R2, added the code below to the rss file to handle the params.
if (Values.Length > 0)
Dim i As Integer
i = Values.Split(";"c).Length - 1
Dim parameters(i) As ParameterValue
For j As Integer = 0 To i
parameters(j) = New ParameterValue()
parameters(j).Name = Values.Split(";"c)(j).Split("="c)(0)
parameters(j).Value = Values.Split(";"c)(j).Split("="c)(1)
Next
rs.SetExecutionParameters(parameters, "en-us")
end if
Edit:
Found an improved version of what I wrote that writes the params to the output, full revised script below:
Public Sub Main()
TRY
DIM historyID as string = Nothing
DIM deviceInfo as string = Nothing
DIM extension as string = Nothing
DIM encoding as string
DIM mimeType as string = "application/Excel"
DIM warnings() AS Warning = Nothing
DIM streamIDs() as string = Nothing
DIM results() as Byte
dim i as Integer
Dim ParmPair as string
Dim ParmName as string
Dim ParmValue as String
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
rs.LoadReport(REPORTSERVER_FOLDER, historyID)
if Values.contains("=") then
'Count how many parameter/value pairs there are (separated by ';')
i=Values.split(";"c).length-1
Console.writeline((i+1).tostring + " parameters:")
Dim parameters(i) as ParameterValue
' Go through all the parameter pairs, and set the parameters for the report
For j as integer=0 to i
ParmPair=Values.Split(";"c)(j)
'Parameter Name and value must be separated by '='
ParmName=ParmPair.Split("="c)(0)
ParmValue=ParmPair.Split("="c)(1)
Console.writeline(ParmName + "=" + ParmValue)
parameters(j) = New ParameterValue()
parameters(j).Name = ParmName
parameters(j).Value = ParmValue
Next j
'Apply the parameters to the report
rs.SetExecutionParameters(parameters, "en-us")
end if
results = rs.Render(FORMAT, deviceInfo, extension, mimeType, encoding, warnings, streamIDs)
DIM stream As FileStream = File.OpenWrite(FILENAME)
stream.Write(results, 0, results.Length)
stream.Close()
Catch e As IOException
Console.WriteLine(e.Message)
End Try
End Sub
August 18, 2015 at 8:14 am
Thanks for the script.
August 25, 2015 at 7:42 am
@mitchell.ross - Great idea with the upgrades to the rss file. Seems to work well. The next step for me is to create a sproc that gets called after data has been populated in our database. Kind of like a data driven subscription that automatically creates a bunch of PDF report files using different input parameters, dynamically, as soon as the data becomes available.
March 10, 2016 at 4:32 pm
Thanks for the script.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply