THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Roman Rehak

SSRS report rendering from command prompt

A while ago I posted on what now seems to be a defunct blog site, SQL Junkies, a blog about how you can use the RS scripting utility to generate SQL Server Reporting Services reports from command line prompt. In preparation for the DevTeach conference I decided to resurrect it for a demo and since a few things changed between SSRS 2000 and SSRS 2008, it required some work and digging through books online. One of the changes was that starting with SQL Server 2005, Reporting Services provides two separate web service endpoints. The ReportService2005 service is used for management and administrative functions, and the ReportExecution2005 service contains functions related to report rendering. By default, RSS script connects to the management endpoint but in this case since I wanted to use the render function, I needed to connect to the other endpoint. So, the trick here is to use the new endpoint (–e) switch parameter and specify RsExec2005 to use the execution endpoint instead.

 

Here is a code sample showing rendering from the command prompt, the following DOS command runs the utility against specified report server (-s parameter) and instructs it to execute a file that follows the –I parameter. The third parameter specifies the execution endpoint:

 

rs -i "c:\temp\reports\Render Report.rss" -s http://rrehak/ReportServer_Katmai -e Exec2005

 

The RSS utility will render a report (I am using one of the sample Adventure Works reports) and save the rendered file in MS Excel format. The contents of the Render Report.rss file look like this, you may need to modify to match your path to the report:

 

Public Sub Main()

  Dim format as string = "EXCEL"

  Dim fileName as String = "C:\temp\reports\Product Line Sales.xls"

  Dim reportPath as String = "/AdventureWorks 2008 Sample Reports/Product Line Sales 2008"

 

  ' Prepare Render arguments

  Dim historyID as string = Nothing

  Dim deviceInfo as string = Nothing

  Dim extension as string = Nothing

  Dim encoding as string

  Dim mimeType as string

  Dim warnings() AS Warning = Nothing

  Dim streamIDs() as string = Nothing

  Dim results() as Byte

 

  rs.LoadReport(reportPath, historyID)

 

  results = rs.Render(format,  deviceInfo, extension, _

   mimeType, encoding,  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

 

Published Friday, June 12, 2009 10:53 PM by roman

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

PP said:

I hope it will over-write the excel file if it already exists, if not is there a way to handle that within "RS" or we will write custom code within "Public Sub Main()"?

June 15, 2009 2:40 PM
 

AG said:

How can I use the same function to render the report in Pdf format?

June 20, 2009 11:12 AM
 

KSY said:

Thinks, this is perfect !

July 1, 2009 3:08 PM
 

Arjun said:

Hello,

      I am forwarding you the details of the Issue. I am actually trying to automate the generation of an error report from an SSIS Package which has been done succesfully. We deployed the report to the Report Server and then in the report server we created a subscription and we are using the subscription ID to create an RSS file.

sub Main()

Try

rs.FireEvent("TimedSubscription","034C4CFC-1C1E-46FD-8E0E-EB1E3F1F15B7")

catch ex as exception

console.writeline(ex.message)

end try

end sub

After Creating the Rss file like shown above, we are trying to run the rss file from SSIS using an execute process task by giving the

executable:  C:\Program Files\Microsoft SQL Server\90\Tools\Binn\rs.exe

Arguments: -i C:\Execute.rss -s http://dude/reportserver

in the fields of the execute process task. Now the problem is that I am taking an input as Acct Nbr from an input box when the SSIS package is run and then I am storing it as a user variable. I need to send this user variable, system variables such as package name and package run time to the SSRS report as parameters so that whenever there are errors from different packages the error report gets generated from the error table as we are doing a simple select from error table where Acct Nbr= @Acct Nbr, Pkg Name = @ Pkg Name, Pkg Run Time= @ Pkg Run Time.

We are giving the three specific parameters for the error report to make sure that if the SSIS package is run by different developers at the same time it gives different error reports to different developers based on the where clause in SSRS report where we are passing parameters from the SSIS PAckage.

Please Do let me know if you need any further details about this Issue.

Thank You,

Arv

December 13, 2009 10:33 AM
 

Simon D said:

Great article - really got me started in the right direction.

Many thanks.

December 14, 2009 6:15 PM
 

Mahesh said:

thanks. workded good for me. Can we runs subscription in similar way?

December 22, 2009 2:58 PM
 

SteveM said:

Almost exactly what I need.  How can the script be changed to send an e-mail instead of saving a file?

March 5, 2010 1:45 PM
 

Kevin G said:

Great article. I was able to implement this and save as a PDF in SQL2005.  I am now trying to implement this on SQL2008 R2 running in Sharepoint integrated mode but not having very much luck. Wondering if you have any suggestions.  Thanks

June 3, 2010 10:13 AM
 

ToddRod72 said:

I am grateful for the info and glad I found it!  Thank you for posting.

I have a report that has parameter ReportDrill=1 as the default and the script works marvelous.  But I would like to pass ReportDrill=2, or ReportDrill=3, etc to the same report.

Is there a way to pass a report parameter to a SSRS report in the script?

I am working up a way to take rendered reports and emailing with my VB program

July 13, 2010 11:05 AM
 

Hari said:

Thanks for the code. It worked fine for me.

September 15, 2010 3:58 PM
 

Geoff said:

Thanks for this post. I am using a variation on this script as part of a solution for providing "subscription" style report delivery for people using SQL Express edition. By coupling this script with a batch with the rs command and then scheduling the batch file to run via Windows Tasks, even a SQL Express user can get automated report rendering.

February 17, 2011 8:46 AM
 

GavYo said:

Many Thanks for this code. It works for me with PDF as well.  FYI, I'm using SQL 2008 R2 Express Advanced edition.

January 8, 2012 8:04 PM
 

Lisa said:

Is there a way after the reports are run that the file is not overwritten?

July 26, 2012 1:22 PM
 

e said:

w

September 18, 2012 12:01 AM
 

Sudhir said:

Hi There,

Can I run the prompt based reports (I have a prompt to be answered in the report before it runs)

Can you please help me with the Run Command for this please.

Or optional parameter key to be used.

June 8, 2013 2:07 PM
 

Ganesh said:

I am using the same process and it works fine. But when i call the rs.exe it takes 7 to 8 second to invoke the report and makes the database call.

Is this normal ? or is there a way to avoid that.

February 10, 2014 5:28 PM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement