Use Excel to pass a parameter to SSRS 2008 R2 and save the rendered report as a file.
I want to itterate through an Excel column and use the value in each cell as a parameter to be passed into an SSRS report. I can hancle the vba code to manage the looping but need to understand how to use the cell value to call SSRS. In addition, I want to create the report as a PDF file and save it to a folder on a network share or my hard drive. I do not need the report to be rendered to the screen. Just create and save to a file. Each file must have a unique name where the parameter passed is the portionof the name that makes it unique. Any ideas on how this could be done? Example code for calling SSRS and saving the file as a PDF would be appreciated.Robert Stroud
October 17th, 2011 9:43am
Hi MamaDSU73, From your description, there is a need to design a report collecting data from excel files. We can create a dataset retrieving values from excel, and then create the parameter to get available values from the dataset. A little attention here, the syntax of the SQL command uses different marks according to the connection type. For example, your SQL command here might like this: select [sheetname$].field1 from [sheetname$] More information about Using Excel as a Reporting Services datasource: http://weblogs.sqlteam.com/joew/archive/2008/08/22/60695.aspx One example code to save a report as PDF programmatically: Microsoft.Reporting.WebForms.ReportViewer rview = new Microsoft.Reporting.WebForms.ReportViewer(); rview.ServerReport.ReportServerUrl = new Uri("http://localhost:8080/ReportServer"); rview.ServerReport.ReportPath = "/Adventure Works/Report1"; string mimeType, encoding, extension; string streamids;Microsoft.Reporting.WebForms.Warning warnings; string format = "PDF"; byte bytes = rview.ServerReport.Render(format, "", out mimeType, out encoding, out extension, out streamids, out warnings); //save the pdf byte to the folder FileStream fs = new FileStream(Server.MapPath("report.pdf"), FileMode.Open); byte data = new byte[fs.Length]; fs.Write(bytes, 0, bytes.Length); fs.Close(); Another example: How to execute and Save SSRS report using C# Thanks, LolaPlease remember to mark the replies as answers if they help.
October 18th, 2011 10:43pm