March 20, 2020 at 2:33 am
I would really 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>
<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
March 20, 2020 at 10:24 pm
I don't have a solution to the problem as stated, but as a workaround, my suggestion/question would be:
Could you just create a subscription with the report rendered as a PDF? Then you would just need to define a file share on the SSRS server & give access to either the file share user or a specified user.
Even if parameters are dynamic, but derivable (e.g. by time, or driven by data), then, if you have control over the data set, you might be able to either
March 25, 2020 at 3:04 am
Hi Rick
Thank you kindly for your suggestion. I kept on searching and found the following solution using visual basic in ETL script:
https://msbiblog.wordpress.com/2009/05/19/run-and-export-ssrs-reports-from-ssis-sql-server-2005/
I have developed a solution based on the above article which is working perfectly.
Regards, Julianne
March 25, 2020 at 9:59 am
Glad you found your solution. Thanks for posting it
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply