SSRS to multiple PDF based on input parameters

  • 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

  • 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.

  • 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!

  • 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

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

  • 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.

  • 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