ssrs run all parameter options

Question

I had developed a report with three different parameters in it for users to log in ad hoc and select what they wanted and run the report. Adding up all the different combinations of parameter selections and there are 150 different reports.

Now the requirements have changed so that we need all 150 reports done in excel and saved out to the network each month. How can I schedule all 150 options to run and save in excel (without having 150 subscriptions). Thanks

Stacia Stacia · Accepted Answer · 2012-06-08T13:50:44

Are you using Enterprise Edition? If so, you could create a data-driven subscription. You would need to set up a table that has 150 rows in it, with at least 3 columns representing each parameter. For flexibility, I'd add a column for render format (EXCEL) and for target folder. Then you can create one subscription.

Otherwise, if you have only Standard Edition, your only choice is to create 150 subscriptions. In that case, I'd be inclined to write a script to use with RS.EXE to generate the 150 subscriptions.

Here's a sample script that produces an Excel file. I re-read your post and decided you didn't really need the subscription aspect of this. You just need to render the report, and then to schedule this, just create a batch file for the command line and schedule it with SQL Server Agent.

This code is adapted from the sample at http://msdn.microsoft.com/en-us/library/reportexecution2005.reportexecutionservice.render(v=sql.100).aspx#Y1258

You type this in using Notepad and then save the file as renderExcel.rss. The file extension is important - don't save it as txt file. Of course, change the file path, parameter names, and values. I just had these to execute on my dev machine and make sure it actually works!

Public Sub Main()

  rs.Credentials = System.Net.CredentialCache.DefaultCredentials

  ' Render arguments
  Dim result As Byte() = Nothing
  Dim reportPath As String = "/Sales/Reseller Sales Cumulative Sales"
  Dim format As String = "EXCEL"
  Dim historyID As String = Nothing
  Dim devInfo As String = Nothing


  ' Prepare report parameter.
  Dim parameters(2) As ParameterValue
  parameters(0) = New ParameterValue()
  parameters(0).Name = "SalesTerritoryGroup"
  parameters(0).Value = "Europe"
  parameters(1) = New ParameterValue()
  parameters(1).Name = "Year"
  parameters(1).Value = "2007" 
  parameters(2) = New ParameterValue()
  parameters(2).Name = "Quarter"
  parameters(2).Value = "1"

  Dim credentials As DataSourceCredentials() = Nothing
  Dim showHideToggle As String = Nothing
  Dim encoding As String
  Dim mimeType As String
  Dim warnings As Warning() = Nothing
  Dim reportHistoryParameters As ParameterValue() = Nothing
  Dim streamIDs As String() = Nothing

  Dim execInfo As New ExecutionInfo
    Dim execHeader As New ExecutionHeader()
    Dim SessionId As String
    Dim extension As String = ""

  rs.ExecutionHeaderValue = execHeader

    execInfo = rs.LoadReport(reportPath, historyID)

    rs.SetExecutionParameters(parameters, "en-us")

    SessionId = rs.ExecutionHeaderValue.ExecutionID
    Console.WriteLine("SessionID: {0}", rs.ExecutionHeaderValue.ExecutionID)


  Try
     result = rs.Render(format, devInfo, extension, _
           encoding, mimeType, warnings, streamIDs)


  Catch e As SoapException
     Console.WriteLine(e.Detail.OuterXml)
  End Try
  ' Write the contents of the report to an Excel file.
  Try
     Dim stream As FileStream = File.Create("myReport.xls", result.Length)
     Console.WriteLine("File created.")
     stream.Write(result, 0, result.Length)
     Console.WriteLine("Result written to the file.")
     stream.Close()
  Catch e As Exception
     Console.WriteLine(e.Message)
  End Try
 End Sub

To execute it, you need to run this at the command line:

rs -i renderExcel.rss -s http://localhost/reportserver -e Exec2005

This code only creates one file. You need to add a way to loop through it to set your parameters for each parameter combination and then render.

ssrs run all parameter options

1 Answers