Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSRS to multiple PDF based on input parameters


SSRS to multiple PDF based on input parameters

Author
Message
vijayraj105
vijayraj105
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 50
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
Michelle Perrin
Michelle Perrin
SSC Veteran
SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)

Group: General Forum Members
Points: 292 Visits: 333
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.
Sebastian Zeits
Sebastian Zeits
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 20
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!
Michelle Perrin
Michelle Perrin
SSC Veteran
SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)

Group: General Forum Members
Points: 292 Visits: 333
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
Sebastian Zeits
Sebastian Zeits
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 20
this is great, thanks so much for taking the time!
sarwar.ali490
sarwar.ali490
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 40
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.
Damian
Damian
Mr or Mrs. 500
Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)

Group: General Forum Members
Points: 535 Visits: 1122
Regarding option 1, this might help:

http://www.sqlservercentral.com/Forums/Topic1749995-150-1.aspx#bm1762267

- Damian
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search