SSRS to multiple PDF based on input parameters

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Reporting Services » Reporting Services » SSRS to multiple PDF based on input...

SSRS to multiple PDF based on input parameters

Rate Topic

Display Mode

Topic Options

Author

Message

vijayraj105

Posted Monday, August 03, 2009 5:05 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, March 17, 2011 1:39 AM
Points: 14, 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

Post #763975

Michelle Perrin

Posted Tuesday, August 04, 2009 5:10 PM

SSC Veteran

Group: General Forum Members
Last Login: Sunday, November 04, 2012 4:15 PM
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.

Post #765176

Sebastian Zeits

Posted Monday, August 20, 2012 9:14 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, September 07, 2012 5:32 PM
Points: 3, Visits: 18

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!

Post #1347543

Michelle Perrin

Posted Tuesday, August 21, 2012 3:24 PM

SSC Veteran

Group: General Forum Members
Last Login: Sunday, November 04, 2012 4:15 PM
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

Post #1348114

Sebastian Zeits

Posted Wednesday, August 22, 2012 8:21 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, September 07, 2012 5:32 PM
Points: 3, Visits: 18

this is great, thanks so much for taking the time!

Post #1348442

« Prev Topic | Next Topic »

Permissions