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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy