Programmatically create SSRS report as PDF

• juliannereid

  SSC Enthusiast

  Points: 175

  More actions

  March 20, 2020 at 4:58 am

  #3735766

  I would appreciate some guidance on how to get vb code working in a SSRS report.  Please refer vb code at end of posting. The code has been added to Custom Code (Report Properties, Code) and the code is called from a Text Box expression in the report.  I have created a SSRS Data Source called "SSRSWebService" which is type XML in SSRS:

  <DataSource Name="SSRSWebService">

  <ConnectionProperties>

  <DataProvider>XML</DataProvider>

  <ConnectString>http://dwdev/ReportServer/ReportService2005.asmx</ConnectString&gt;

  <IntegratedSecurity>true</IntegratedSecurity>

  </ConnectionProperties>

  <rd:SecurityType>Integrated</rd:SecurityType>

  <rd:DataSourceID>e7b56561-0282-4c28-a733-aec414b949a3</rd:DataSourceID>

  </DataSource>

  I have created a SSRS Dataset called "SQLRS" that uses the "SSRSWebService" Data Source with the following XML which when executed returns the SSRS detail successfully:

  <Query>

  <Method Name="ListChildren"

  Namespace="http://schemas.microsoft.com/sqlserver/2005/06/30/reporting/reportingservices">

  <Parameters>

  <Parameter Name="Item">

  <DefaultValue>/</DefaultValue>

  </Parameter>

  </Parameters>

  </Method>

  <ElementPath IgnoreNamespaces="true">*</ElementPath>

  </Query>

  When I preview the SSRS report in Visual Studio the code is failing with the following error:

  There is an error on line 21 of custom code: [BC30002] Type 'SQLRS.DataSourceCredentials' is not defined.

  The code was derived from the following article but is limited on detail:

  https://stackoverflow.com/questions/8758964/generating-ssrs-and-converting-into-pdf-automatically

  I have tried labelling the Data Source as "SQLRS" as well as the Dataset but the Custom Code is not recognising either. Do I need to be set references to an assembly in the Report Properties instead? The article I derived the vb from does not specify what type of object the SQLRS is.

  Version of SQL:

  Microsoft SQL Server 2017 (RTM-CU19) (KB4535007) - 14.0.3281.6 (X64) Jan 23 2020 21:00:04 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

  The vb code used:

  Public Shared Function AutoPDFReport(ByVal s As String) As String

  Dim result() As Byte

  Dim report As String = "/Business Reporting/Board Reports/Operational Dashboard"

  Dim format As String = "PDF"

  Dim historyid As String = Nothing

  Dim devinfo As String = ""

  Dim credentials() As SQLRS.DataSourceCredentials = Nothing

  Dim showhide As String = Nothing

  Dim encoding As String

  Dim mimetype As String

  Dim warnings() As SQLRS.Warning = Nothing

  Dim reporthistoryparams As SQLRS.ParameterValue() = Nothing

  Dim streamid() As String = Nothing

  Dim sh As New SQLRS.SessionHeader

  ws.SessionHeaderValue = sh

  result = ws.Render(report, format, historyid, devinfo, parameters, credentials, showhide, encoding, mimetype, reporthistoryparams, warnings, streamid)

  Dim stream As FileStream = File.Create("C:\JR\Test.pdf", results.Length)

  stream.Write(results, 0, results.Length)

  stream.Close()

  End Function

   

• Site Owners

  SSC Guru

  Points: 80342

  More actions

  March 21, 2020 at 5:10 am

  #3735989

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.