Published: 1/6/2005
Introduction
I recently ran into a need to run a report in SQL reporting services from the command line. The Report took four (4) input parameters and I had to export it to Microsoft® Excel and save it to disk. I had to rummage through the product documentation and the Microsoft® SQL Reporting newsgroup to get this right. For running reports from the command line, SQL Reporting services provide a utility called “rs utility”.
The rs Utility
The rs utility is a script host that processes script you provide in an input file. You can define scripts to administer a report server, copy report server database content to another database, publish reports, and so forth. The script must be written in Microsoft Visual Basic® .NET code, and stored in a Unicode or UTF-8 text file with a .rss file extension. You cannot debug scripts with the rs utility. To debug a script, run the code from within Visual Studio.
To run the tool, you must be a local administrator on the computer that has the report server instance you are running the script against. There are some script samples provided with the Reporting Services installation and the samples are located in the following directory in your Reporting Services installation: <Reporting Services>\Samples\Scripts
“Run Report” Script
The following script is meant to run the report by passing it parameters and saving the output to Excel. Out of the four (4) parameters required by the report, the values for three of the parameters are hard-coded in the script file. The fourth parameter expects a value to be provided at runtime during the running of the script.
' File: RunReport.rss Dim format as string = "Excel"Dim fileName as String = "C:\Export2.xls"Dim reportPath as String = "/SalesDashboard/Overview"Public Sub Main() ' 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 = "ms-excel" Dim warnings() AS Warning = Nothing Dim reportHistoryParameters() As ParameterValue = Nothing Dim streamIDs() as string = Nothing rs.Credentials = System.Net.CredentialCache.DefaultCredentials ' Report Parameters Dim parameters(3) As ParameterValue parameters(0) = New ParameterValue() parameters(0).Name = "SalesType" parameters(0).Value = "Auto" parameters(1) = New ParameterValue() parameters(1).Name = "Country" parameters(1).Value = "Japan" parameters(2) = New ParameterValue() parameters(2).Name = "Year" parameters(2).Value = "2004" parameters(3) = New ParameterValue() parameters(3).Name = "Month" parameters(3).Value = MonthParameter results = rs.Render(reportPath, format, _ Nothing, Nothing, parameters, _ 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'End of script
Running the Script
To run the script, and pass the fourth parameter (MonthParameter) at runtime, issue the following command at the command prompt –
rs –i RunReport.rss –s http://localhost/reportserver -v MonthParameter=”9”
The -v flag above declares a global variable called MonthParameter and initializes it with the value “9”. This global variable is used in the script to set value to the Month parameter as parameters(3).Value = MonthParameter