Preface:
The following is a series on integrating your custom web applications with SQL Server Reporting Services. It is intended to provide useful information for other developers posed with the same problem of integrating these two seamlessly.
For the situation and notes, please view Part 1 of this series.
Utilizing Reporting Services API:
In Part 1 of this series we discussed setting up authentication on your custom web application, reporting services, and the Windows server / Active Directory. Part 2 will be discussing how to utilize the Reporting Services API exposed by Web Services. This API is intended for use by developers for integration scenarios just like this, and it is very important to understand how to actually use it. The entire API is very extensive and exhaustive, and in reality, you will only use 5%. For more information on the API, visit the MSDN documentation.
As the documentation states, the Reporting Services API can be accessed via Web Services at the URL http://<SERVER>/ReportServer/ReportService.asmx. Using Visual Studio .NET (or if you prefer the command line WSDL EXE), you should create a proxy for use in your custom web application. If you're using Visual Studio .NET to create the proxy, you should see the listing of all the classes, methods, and properties that it exposes...which I've subtly named "everything under the sun except for the kitchen sink." Just glancing through the listing, you will want to note the most important class of them all "ReportingService." This class exposes everything that you would really want in just a basic application.
So, enough looking, and add that puppy so we can start working!
The most important item that must be done prior to using the Reporting Services API, is that you need to pass credentials with any call to the API. To do this, the class ReportingService inherits a property called Credentials. And, as you guessed it, is where you will add your credentials. So, exactly how do the credentials fit in, and how do I access them? Well, that was the whole idea behind Part 1 of this series, setting your application up properly so that this daunting task is super easy.
Remembering back to Part 1, we're using impersonation of the currently logged in user within the current thread context of the web application when it is running. What this means is that our web application is running as if it my user, thus enabling me to pass the credentials of the web application onto the API. By simply calling a single line of code, we've now setup our instance of the Reporting Services API to be executed as if I were going to it directly via URL:
// create an instance of the reporting service
ReportingService rs = new ReportingService();
// set the URL of our reporting server
rs.Url = http://rptsrv/ReportServer/ReportingService.asmx
// set the credentials
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
So, now our credentials are being set - which means that we can now start calling methods to do certain functionality. In the application we built, it was necessary to retrieve the reports dynamically based on the users' role and supply these reports in a drop down list for the user to select and run. To accomplish this task, we needed to look at the method ListChildren which takes in a folder path and boolean value indicating if a recursive search should be performed. This method returns an array of CatalogItem classes that allow us to determine if the item returned was a report, and if it was, the path, name, and other important information. Here's an example of retrieving the reports for a given path:
// retrieve the catalog items for the demo reports folder
ReportingServices.CatalogItem[] items = rs.ListChildren("/Demo Reports", false);
// cycle through the items returned and add them to the drop down list if it is a report
foreach(CatalogItem item in items) {
if(item.Type == ItemTypeEnum.Report) {
ddlReports.Items.Add(new ListItem(item.Name, item.Path));
}
}
So, now we have a listing of reports in the Demo Reports folder. Pretty simple right? You betcha. It's now time to take a selected report and view that report in a separate browser window (code which you can figure out!). So, in a button's OnClick method, we open a new window and supply a URL to the reporting server that will automatically display the report. Sounds simple enough right? Well, kinda - it takes some know-how on how to construct the URL properly.
The great thing is that you can pass just about everything in the URL by Query String parameters to control how the report will be rendered to the user. If you're familiar with viewing the reports currently, you'll remember that there are two toolbar items. One being the Toolbar that displays the paging and export interface, and the other being the toolbar containing all custom report properties that can be searched on. By passing just a few parameters in the querystring, you can control if those are displayed or not. Another very important query string parameter handles how the report will be rendered (HTML, PDF, CSV, etc.) when first accessed. So, what exactly are these parameters?
- rs:Toolbar - The toolbar for the paging / export functionality. It is a boolean value indicating if it is visible or not. Supplying True, Default, or leaving it off will commonly display it.
- rs:Parameters - The toolbar displaying the report parameters. It, like the above, is a boolean value indicating if it is visible or not. Supplying True, Default, or leaving it off will commonly display it. In general, we'll be passing the report parameters values by querystring from our application, so I suggest setting this parameter to False.
- rs:Format - The format the report will originally be rendered as when first viewed. If left off, the report will be rendered as HTML. Other values include PDF, HTML4.0, etc. More information on the allowed values can be retrieved from the documentation.
A caveat I found out is that Gecko based browsers (yes, this includes Firefox, Mozilla, and Netscape) don't like to render the Toolbar / Parameter bar properly, basically rendering it useless. So, when developing your URL, I advise that you check to see if it is a Gecko based browser, and render it as PDF. Sure, you loose a lot of functionality of the report (drill downs, etc), but my guess is that these wouldn't have worked in the first place with the Gecko based browser.
Previously, I mentioned that commonly we'll be supplying our own user interface for parameter selection rather than relying on the rudimentary parameter selection interface from Reporting Services. To pass these values, you need only to add additional query string parameters. The name of the parameter being the name of which field it should map to in the report and obviously, the value being what you want to pass. So, that's enough talking about building the URL, I guess you're probably getting ants-y and wanting to see how to do it:
string url = "http://rptsrv/ReportServer/?" + ddlReports.SelectedValue;
if(Request.UserAgent.IndexOf("Gecko") >= 0)
// this is a gecko based browser, render the report as PDF
url += "&rs:Command=Render&rs:Format=PDF&rc:Toolbar=False&rc:Parameters=False";
else
// this is a browser that is supported for paging / exporting, render as HTML
url += "&rs:Command=Render&rc:Parameters=False";
// add any custom parameters
url += "&year=2004";
// redirect to the report
Response.Redirect(url);
Okay, so assuming you're on a single server environment or using Active Directory as your authentication, you should (upon submitting the form to view the report) be seamlessly passed directly to the rendered report.
Other Rendering Capabilities:
There are other methods of rendering using the Reporting Services API, however these are outside of the scope of this article. If you would like more information on using these methods of rendering, I'd suggest reading this great article on 15 Seconds, as well as checking out the Render method of the API. In a nutshell, the afformentioned method will allow you to stream the report and save / display it in any manner you wish, which could be very beneficial in creating custom reporting solutions for windows applications.
Secondly, apart of the Reporting Services samples installed on your development machine (which oddly enough I didn't install), is a compiled assembly called "ReportViewer.dll" that is a ASP.NET Custom Control for rendering reports directly within a webpage. If you visit the previously mentioned 15 Seconds article (In part 2) they describe how you can utilize that control. After playing with it though, I realized that it generated an IFrame, which as most of you know is BAD BAD BAD! Thats why I went the alternate route and did the redirection.
So, this currently ends my 2 part series on integrating Custom Web Applications and Reporting Services. I hope you found both parts extremely beneficial, and hope that they provided you with as much information that I lacked when trying to figure this out. Lastly, I did want to give props to my good buddy Ryan Rinaldi who helped me figure some of the authentication issues!
8d9ef02c-6aaf-41d9-94b4-b5423dd1ff61|0|.0