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”.

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

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 

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”