Programmatically create SSRS report as PDF

  • juliannereid

    SSC Enthusiast

    Points: 115

    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: 80378

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

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

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply