Generating reports from command line prompt
I will be presenting “SQL Server 2000 Reporting Services Programming” at the upcoming DevTeach conference in Montreal. On Memorial Day I did a dry run of the session at the SQL Server Montreal User Group meeting. Just as I expected, it was very well received. Reporting Services presentations are an easy sell. So far I did RS presentations to about five different types of audience (developers, DBAs, upper management, midle management, system architects) and it seems like there is something for everyone. What I find very amusing is that the demo that got the best response was not the one that I spent five hours coding. It was something I put together in 15 minutes - simple RSS script executed with the RS script host. The script calls the RS web service, renders a report into Excel format and saves it to a file. You can execute the script from the command line like this:
rs -i "C:\RS Script\MyScript.rss" -s http://myserver/reportserver
Here is the code from MyScript.rss. It renders the Product Line Sales report, it's one of the sample reports that comes with RS:
Public Sub Main()
Dim format as string = "EXCEL"
Dim fileName as String = "C:\RS Script\Product Line Sales.xls"
Dim reportPath as String = "/SampleReports/Product Line Sales"
' Prepare Render arguments
Dim historyID as string = Nothing
Dim deviceInfo as string = Nothing
Dim showHide as string = Nothing
Dim results() as Byte
Dim encoding as string
Dim mimeType as string
Dim warnings() AS Warning = Nothing
Dim reportHistoryParameters() As ParameterValue = Nothing
Dim streamIDs() as string = Nothing
results = rs.Render(reportPath, format, _
Nothing, Nothing, Nothing, _
Nothing, Nothing, encoding, mimeType, _
reportHistoryParameters, warnings, streamIDs)
' Open a file stream and write out the report
Dim stream As FileStream = File.OpenWrite(fileName)
stream.Write(results, 0, results.Length)
stream.Close()
End Sub