SSRS Reporting in Java

So we are in the final testing stages of our SSRS reporting interface in a Java web front-end.

The results are really fantastic! However, going thru this process has taught me a lot about how SSRS works internally, things that I took for granted when using report manager to render reports. These things will probably help my report design in the future, so I thought I would write them down and pay it forward!

First, a bit about how we implemented this:

In our reports, we force exact pagination by having a caption at the top of the report that reads:  This report contains xxx pages.  Since the report wants this number, SSRS will go ahead and process the report to calculate the page count.  If we had left this out, SSRS would render the first page and not worry about how many subsequent pages were coming.

In the front end, we render page 1 of the report, get the total page count, and then display the first page in a viewer similar to report manager. Note that we are NOT using the report viewer control since this is a Java application. We render all pages to HTML4.0 and display as is on the web page. Since SSRS embeds all of the formatting, the front end is responsible for none of that.

When the user clicks to go to the next page, we again call render with the page number in the device info XML.

In parallel, I created a small C# app that calls a report and renders it much the same way.  I did this so I could make sure I fully understood the process and could assist the Java developers.

We ran into issues when our reports took much longer to render in our front end than it did in report manager or my C# app.  So I started watching stats using the ExecutionLog3 view on the report database.  I paid special attention to TimeDataRetrieval, TimeProcessing, and TimeRendering.

From my app I was seeing the data retrieval occurring on the first render, and some time processing and then some time rendering.  These numbers were all pretty much in-line with what I would have expected. For a 110 page report, I had 3000-3500ms for data, 750-1000ms for processing, and 1000-1500 for rendering. On this first page, the Source field was Live.

On moving to the next page though, the Source field was Session, I was seeing no data retrieval at all, and minimal processing and rendering: 0ms for data, 35-40ms for processing, and 100ms for rendering. Very acceptable numbers for a reporting platform! So this tells me that SSRS processes the dataset all at once, does a bit of pre-rendering to get the page count, and fully renders the first page.

From our front end however, I saw something completely different! Every request had a Source of Live, and each request was pulling data and rendering, sometimes taking up to 15-20 seconds to run the report.

I put WireShark on the SSRS server and started watching network packets. I was able to watch every command that came into the SSRS server, and the response from the server.  It quickly became apparent what was happening.  The front end was not sending the page number for the first Render call, and then calling SetExecutionParameters between each page. The first Render call was causing the server to fully render the report for the first page. The SetExecutionParameters call was invalidating the session and causing the report to be full re-rendered, including data retrieval, for each page. Obviously this was the cause behind the huge difference in execution times!

So I worked with the Java developer and was able to remove any extraneous SSRS calls, and now the performance is amazing!

Our report template it pretty complex, we have a lot of background features that require processing, so to have execution times in the low 100ms range is great!

Speaking of our report template, we have a parameter called Date Range.  A user can select a value in this drop down and select ranges such as last month, last year, etc. When the user selects one of these, we call the SQL Server to return a start and end date based on the selection. In report manager, once you select one date range, and then try to select a second, the start and end dates will not update.  This is a “feature” according to Microsoft. Since we wrote our own front end, we were able to work around this. So you can select Last 2 Years and the start and end dates will populate, and then you can change to rolling 13 months and the start and end dates will update accordingly!

That’s all for now… I welcome any and all questions…

peace

SSRS Reporting in a Java Web Site

For the last 6 months or so, I have been working on a project with our web application development team to integrate SSRS reporting into our client facing Java web site.

When I first proposed this project, I knew that in theory this should work fine. But, it did take a bit of research and a lot of work to bring it to fruition.

We are moving into the testing phase this week, and I am happy to say that it works very well! The Java developer working on this is doing a great job of translating all of my Microsoft talk to java :)

We are using the Web Services provided by SSRS with a custom security extension that authenticates against our web site security.

Since we are only rendering one page at a time, rendering is pretty quick, taking less than a second a page to render.

Once we go thru testing and release, I am going to write more on this, including some details.

Timeouts in SSRS 2005 Web Service and Report Viewer Control

One of the questions that I get asked a lot is how to prevent timeouts when running or exporting large reports using the SSRS web service or the .NET report viewer control.

With the report viewer control, this is fairly simple; the problem is that the property is hiding in the property tab. In the property tab, expand the ServerReport property, and set the timeout value to a large number or to -1 (infinite timeout)

For the web services, the ReportExecutionService instance contains the property Timeout.

In my previous post showing how to use the web service, I neglected to add the timeout property.

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

Also, when working with the web service in SSRS 2005, you have to keep the IIS timeouts in mind. For example, the session timeout by default is 20 minutes. If you have reports taking longer than that, you would need to increase this setting in IIS. However, if your reports are taking that long, I would consider using SSIS to pre-process the report data on a schedule and then run the report against the pre processed data. That is a much more elegant solution.

peace

ReportingService2005 Web Service

Well, I know it’s been a while, but I am finally back after the holidays!

The next phase of our reporting project is about to take off, and I had a little down time while waiting on requirements.

So, using the ReportingService2005 web service, I created a report deployment application in C#.

It allows me to select the directory of my RDL/RDS files, and then select my report server and any folder on that server.

Then it deploys the selected reports to the report server with the one button click.

I can then change the report server, and deploy the same reports to that server. This is helpful if you have multiple systems that should stay in sync. Using dynamic web service connections allows me to change the target server within code.


Working with the web service was rather straightforward, and allowed me to do everything I needed to do as far as deploying reports and data sources.
However, what I wanted to write about was a catch that had me stumped for a few minutes.

When you use the CreateReport method of the web service, the data sources are not linked to the server data sources. So what I did was use the GetItemDataSources method to get the report data sources, built a new data source in code, representing the server data source, and then used the SetItemDataSources method to update the report data sources.

DataSource[] dataSources = rs.GetItemDataSources(TargetFolder + "/" + ReportName);

foreach (DataSource ds in dataSources)
{
if (ds.Item.GetType() == typeof(InvalidDataSourceReference))
{
string dsName = ds.Name.ToString();

DataSource serverDS = new DataSource();
DataSourceReference serverDSRef = new DataSourceReference();
serverDSRef.Reference = "/Data Sources/" + dsName;
serverDS.Item = serverDSRef;
serverDS.Name = dsName;
DataSource[] serverDataSources = new DataSource[] { serverDS };
rs.SetItemDataSources(TargetFolder + "/" + ReportName, serverDataSources);
}
}

This nicely updated the report data sources to use the server shared data sources.

All in all, the 1½ days I spent writing this app were well worth it. I learned about the deployment methods of the web service, and kept my coding skills up, plus I have a nice deployment utility for reports!

peace

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