Unanswered: Programatically save SSRS report to Excel and passing Parameters
I have a SSRS Report which due to volume of data and what is required in the report takes roughly 1 minute to generate. The users then export this report to Excel anyway every time they access the report.
My question is this. Since the data is only updated once a month is there any way I can through Script such as VB, generate the Excel file from the SSRS report and pass through the parameters all in one script?
My biggest problem I foresee is that the parameters are not statis from one month to the next so would have to create a recordset and loop through everything generating the Excel spreadsheet and naming the spreadsheet DateCreated_Parameter.xls
Is this possible, can anyone help? I have Visual Studio 2003 and then old school VB6. Please help me.
I'm not sure I understand why the parameters would change from month-to-month? Do you wish to use VB Scripting in an SSIS package or a VB subroutine? I can do what you require using SSIS packages with a script task, or as shown below in VB.NET, or as ASP.NET page.
Public Shared Sub Get_Summary_Page(ByVal dtInvoiceDate As Date, ByVal Account_Id As String, ByRef rvInvoices As Microsoft.Reporting.WinForms.ReportViewer)
' Async application
'Set Processing Mode
rvInvoices.ProcessingMode = ProcessingMode.Remote
' Supply creditials
' rvInvoices.ServerReport.ReportServerCredentials.NetworkCredentials = New System.Net.NetworkCredential("username", "password!*", "domain")
' Set report server and report path
rvInvoices.ServerReport.ReportServerUrl = _
rvInvoices.ServerReport.ReportPath = _
Dim pInfo As ReportParameterInfoCollection
Dim paramList As New Generic.List(Of ReportParameter)
paramList.Add(New ReportParameter("account_id", Account_Id, False))
paramList.Add(New ReportParameter("invoice_date", dtInvoiceDate, False))
pInfo = rvInvoices.ServerReport.GetParameters()
' Assign parameters and values to report
' Process and render the report
Catch ex As ReportServerException
MsgBox("Error: Unhandled exception. Contact Administrator for help." & ex.Message, MsgBoxStyle.OkOnly)