Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSRS to multiple PDF based on input parameters Expand / Collapse
Author
Message
Posted Monday, August 3, 2009 5:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Tuesday, August 4, 2009 5:10 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, November 4, 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
Posted Monday, August 20, 2012 9:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 10:16 AM
Points: 3, Visits: 19
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
Posted Tuesday, August 21, 2012 3:24 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, November 4, 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
Posted Wednesday, August 22, 2012 8:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 10:16 AM
Points: 3, Visits: 19
this is great, thanks so much for taking the time!
Post #1348442
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse