Reporting is almost a universal requirement for new projects coming into DMC these days. As part of an HMI we developed for a customer we also installed SQL Server Reporting Services (SSRS) on a Siemens PLC. This is used to generate reports for production batches run on that PLC. The customer desired these reports to be in PDF format, which after some investigation was easy to do with SSRS. A little background on SSRS...
SSRS is a web application that generates nice graphical reports from SQL Server data sources. It is relatively lightweight and includes its own web server so it's not necessary to install IIS. We were able to install SSRS as part of a SQL Server 2012 Express installation on Windows Embedded Standard 7 without issue.
The SSRS reports are built in a version of Visual Studio 2010 with a limited feature set that is installed when you install SSRS. After deploying the reports and verifying their correctness, I had to figure out how to convert an online report into a PDF. Luckily it was as easy as specifying the correct URL when making a request to the SSRS web application. Generating this request and handling its response can be automated by a VB script.
Why VB? The HMI we are using allows for button callbacks to execute custom VB script. This is a feature of many Siemens PLCs.
The script to download an SSRS report as a PDF and save it to disk is below. Note that this script must be executed by a local administrator, and with elevated privileges, to access the SSRS report. Those wishing to use this script may add the code to their callback or execute a shell command from that callback to run the script. The executable to run VB scripts is "cscript".
The script expects one argument when executed in a shell, which is a unique identifier of the batch to generate the report for. Normally this would be entered via a web form when generating an SSRS report through a web browser, but it is possible to pass this parameter as a query string parameter and bypass this user interaction.
The heart of the script is the "urlFormatStr". You will need to change the path of the report, which comes immediately after the "?" parameter mark and uses "%2f" to encode forward slashes. For example, if your report is called "MyReport" and your project is called "MyProject", then the path to the report would be "%2fMyProject%2fMyReport".
You may also need to change your SSRS instance name, which in my case is "ReportServer_SQLEXPRESS". This is the default name when SSRS is installed as part of SQL Server Express.
You may append any query string parameters following rs:Command=Render and rs:Format=PDF, and in my case I pass a BatchID. These parameters will be automatically mapped to any report parameters you created with the same name.
So without any more ado, here is how to generate a PDF of an SSRS report from a Siemens PLC button callback.
option explicit
dim batchID
batchID = Wscript.Arguments(0) ' this must be passed from the callback somehow
Const urlFormatStr = "http://localhost/ReportServer_SQLEXPRESS/Pages/ReportViewer.aspx?%2fMyProject%2fMyReport&rs:Command=Render&rs:Format=PDF&BatchID={0}"
dim URL
URL = Replace(urlFormatStr, "{0}", batchID)
Const adTypeBinary = 1
Const adSaveCreateOverWrite = 2
' request the file over http
dim http: set http = CreateObject("MSXML2.XMLHTTP")
http.open "GET", URL, false
http.send
' write the response text to a binary file
dim stream: set stream = CreateObject("ADODB.Stream")
stream.type = adTypeBinary
stream.open
stream.write http.responseBody
stream.SaveToFile Replace("BatchReport{0}.pdf", "{0}", batchID), adSaveCreateOverWrite
stream.close
Learn more about DMC's web application development services.