August 3, 2009 at 5:05 am
hello,
i need to generate multiple pdf reports based on input parameters using ssrs. suppose there are 1000 branches then it should generate 1000 pdf files one for each branch data. how can i achieve this using Sql Server 2005 and .net framework 1.1 or 3.5
thanks,
vijayraj bhosale
August 4, 2009 at 5:10 pm
Option 1 :
You could create a data driven subscription that selects the Branch IDs and passes them as parameters to the report.
The benefit of this approach is that your can schedule the reports to produced periodically and automatically.
OR
Option 2:
You could select the Branches into a DataReader in .Net for each row returned you can use the ReportExecutionService to generate and render a report to PDF format.
We use option 2 very sucessfully. It allows us to generate the reports when we are ready for them and we then attach each report to an email and send them to the appropriate recipient.
August 20, 2012 at 9:14 pm
hi Michelle, do you have any sample code i can start with? i'm looking to create multiple pdf's from one paramaterized report. we don't have enterprise edition of ssrs, so i think reportexecution service would work perfectly for us.
any assistance would be much appreciated.
thanks!
August 21, 2012 at 3:24 pm
This is the basics of the code that I use. I would have built from other code that I found on the internet. I will post the original link if I can find it.
Private Function Generate_CC_Mgr_Report(ByVal reportPath As String, _
ByVal Period As Integer, _
ByVal DTU_Key As Integer, _
ByVal strFilename As String) As Windows.Forms.DialogResult
' strReport = The full path of the report on the server e.g. /FolderName1/FolderName2/rpt_TA_CC_Manager_Summary
' Period = Report parameter
' DTU_Key = Report parameter
' strFilename = The name that you want to call teh saved report e.g. C:\MyReports\CC_MGR_Report_<Period>_<DTU_Key>.pdf
Generate_CC_Mgr_Report = Windows.Forms.DialogResult.Yes
' Render Formats
'XML : XML file with report data
'CSV : CSV (comma delimited)
'IMAGE: TIFF(file)
'PDF : Acrobat (PDF) file
'RGDI: Remote(GDI + file)
'HTML4.0 : Web page for IE 5.0 or later (.htm)
'HTML3.2 : Web page for most web browsers (.htm)
'MHTML: Web(archive)
'EXCEL: Excel()
Dim OldCursor As Cursor = Me.Cursor
Dim rs As New ReportExecutionService()
Dim result As Byte() = Nothing
Dim format As String = "PDF"
Dim historyID As String = Nothing
Dim devInfo As String = "<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>"
Dim Parameters() As ParameterValue
Dim credentials As DataSourceCredentials() = Nothing
Dim showHideToggle As String = Nothing
Dim encoding As String = ""
Dim mimeType As String = ""
Dim warnings As Warning() = Nothing
Dim reportHistoryParameters As ParameterValue() = Nothing
Dim streamIDs As String() = Nothing
Dim execInfo As New ExecutionInfo
Dim execHeader As New ExecutionHeader()
Dim SessionId As String
Dim extension As String = "PDF"
My.Application.DoEvents()
Me.Cursor = Cursors.WaitCursor
Try
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
' Location of the report Execution asmx file on the Report Server
rs.Url = "http://<REPORTSERVERNAME>/reportserver/ReportExecution2005.asmx"
rs.Timeout = 600000 ' 10 minutes
' Prepare report parameters.
ReDim Parameters(1)
Parameters(0) = New ParameterValue()
Parameters(0).Name = "Period"
Parameters(0).Value = Period
Parameters(1) = New ParameterValue()
Parameters(1).Name = "DTU_Key"
Parameters(1).Value = DTU_Key
rs.ExecutionHeaderValue = execHeader
execInfo = rs.LoadReport(reportPath, historyID)
rs.SetExecutionParameters(Parameters, "")
SessionId = rs.ExecutionHeaderValue.ExecutionID
' Render report to selected format
result = rs.Render(format, devInfo, extension, encoding, mimeType, warnings, streamIDs)
' Write the contents of the report to a file.
Dim stream As FileStream = File.Create(strFilename, result.Length)
stream.Write(result, 0, result.Length)
stream.Close()
Catch ex As Exception
Generate_CC_Mgr_Report = MyMessageBox(Me.Name, "Error Running Report '" & reportPath & "'" & vbCrLf & ex.Message & vbCrLf & vbCrLf & "Click YES to Continue, NO to Abort", "Error Running Report", MessageBoxIcon.Error, MessageBoxButtons.YesNo)
Finally
Me.Cursor = OldCursor
End Try
End Function
August 22, 2012 at 8:21 am
this is great, thanks so much for taking the time!
February 25, 2016 at 3:08 pm
Hi All,
i have same requirement of creating multiple pdf files based on each parameter value from a single report.I have heard of doing this through c#code.Could anyone post a complete code with example report .This wud help me a lot.
Also ,please show where to write this code in report.
Thank you.
February 26, 2016 at 6:29 am
Regarding option 1, this might help:
http://www.sqlservercentral.com/Forums/Topic1749995-150-1.aspx#bm1762267
- Damian
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply