Wednesday, November 7, 2007

Rendering SSRS 2005 Reports with the Web Service

Even though Report Manager is a decent way to deliver reports to your users, you may have the requirement to embed the report in an existing .NET application. This is very simple using the web services provided with SSRS 2005.

Here is a code sample that I use to show developers at my clients how to pull a report.


string historyID = null;
string deviceInfo = null;
string format = "Excel"; //Can be XML, NULL, CSV, IMAGE, PDF, HTML4.0, HTML3.2, MHTML, EXCEL, and HTMLOWC
Byte[] results;
string encoding = String.Empty;
string mimeType = String.Empty;
string extension = String.Empty;
ReportingExec.Warning[] warnings = null;
string[] streamIDs = null;


ReportExecutionService re = new ReportExecutionService();
re.Credentials = System.Net.CredentialCache.DefaultCredentials;

ExecutionInfo ei = re.LoadReport("/directory/ReportName”, historyID);

ReportingExec.ParameterValue[] rptParameters = new ReportingExec.ParameterValue[1];

rptParameters[0] = new ReportingExec.ParameterValue();
rptParameters[0].Name = "DateFormatID";
rptParameters[0].Value = "fr-FR";

re.SetExecutionParameters(rptParameters, "en-us");


results = re.Render(format, deviceInfo, out extension, out encoding, out mimeType, out warnings, out streamIDs);

FileStream stream = File.Create("C:\\report.xls", results.Length);
stream.Write(results, 0, results.Length);
stream.Close();

You will need to set up the reference in your project. The URL for this is: http://servername/reportserver/ReportService2005.asmx

This code, which I give to the developers in a web app, demonstrates the basic use of the web services. It loads a report, adds a parameter value, and then renders the report and saves it to disk. If you wanted to render it back to the user on a web page, you would add:

Response.AddHeader("content-disposition", "attachment; filename=File.xls");
Response.OutputStream.Write(results, 0, results.Length);

Response.End();

By taking this code a little further, you could easily send out an email with the report attached. Or another commonly requested feature for SSRS: emailing multiple reports on 1 email.

You could loop thru a database table, reading the reports to send a user, render that report, save the file, attach it to a system.mail email, and then move to the next report. When the looping for that user is complete, send the email, delete the files, and move on to the next user in the table.

I hope this helps clear up web services in SSRS 2005. Most developers that I work with are not familiar with this web service, and this always gets them going in the right direction. This is by far a VERY basic demonstration. There is a lot more to the web services than just this.

For more reading on the web services, visit:
http://msdn2.microsoft.com/en-us/library/ms155076.aspx


peace

BobP

9 comments:

Anonymous said...

Thanks! Bobp
It's good article. You guide me where to start.

Bradley said...

Awesome post thank you!

I needed this in VB so here it is for those who don't want to spend the time translating it. Also, I changed some things slighty and added a try catch statement.

Depending on your authentication method you could have connection issues, so catch it if it does.

Also, I couldn't put <> around Script so I used [] instead be sure to replace those so it will work.

Dim historyid As String = Nothing
Dim deviceInfo As String = Nothing
Dim format = "PDF" 'Can be XML, NULL, CSV, IMAGE, PDF, HTML4.0, HTML3.2, MHTML, EXCEL, and HTMLOWC
Dim results() As Byte
Dim encoding As String = String.Empty
Dim mimeType As String = String.Empty
Dim extension As String = String.Empty
Dim warnings() As RSExecution.Warning = Nothing
Dim streamIDs() As String = Nothing

Dim re As ReportExecutionService = New RSExecution.ReportExecutionService
re.Credentials = System.Net.CredentialCache.DefaultCredentials

Try
Dim ei As ExecutionInfo = re.LoadReport("/foldername/reportname", historyid)

Dim rptParameters(0) As ParameterValue

rptParameters(0) = New ParameterValue
rptParameters(0).Name = "BatchID"
rptParameters(0).Value = 101

re.SetExecutionParameters(rptParameters, "en-us")

results = re.Render(format, deviceInfo, extension, mimeType, encoding, warnings, streamIDs)

'File Download in the browser
Response.AddHeader("content-disposition", "attachment; filename=File.pdf")
Response.OutputStream.Write(results, 0, results.Length)
Response.End()

Catch ex As System.Web.Services.Protocols.SoapException
Dim msg As String
msg = "User is unauthenticated with SQL Server Reporting Services. Logging off and back on may correct this issue."
Response.Write("[script language=JavaScript]alert('" _
& msg & "')[/script]")
End Try

Bob Pearson said...

Bradley: Thanks for the conversion!!

Bradley said...

I did some additional testing and as I suspected I received different errors in my try catch, so while had wrapped them all into the most likely case, chances are they could be many others.

So here's my updated catch (I just display the message now since it's an internal app anyways)

Catch ex As System.Web.Services.Protocols.SoapException
Dim msg As String
msg = ex.Message
lblErrorMessage.Text = msg
End Try

Amit said...

This post helped me to render a report using web service.
When the
results = re.Render(format, deviceInfo, extension, mimeType, encoding, warnings, streamIDs)
statment is executing, is it possible to judge how much time ist will take to render the report.
In case of huge reports, I want to show some progress status showing the remaining pages to be rendered, so user will not keep staring to blank web page or to some static gif image.

Basically my question is, is it somehow possible to read the current status of the execution? like how much more time it will take to complete the rendering..

Anonymous said...

Will this work for dynamic report/drill down report?

Is it possible to enable dynamic report via web services?

Android app developers said...

Really your blog provide me good experience.Your blog basic information is really good.I like this post.Thanks for your support.Good.Keep it up.

libin said...

Awesome information.. thanks a lot! :D

Anonymous said...
This comment has been removed by a blog administrator.