By Adnan Masood
- Part 1
- Introduction
- Reporting Services Installation
- Programming Reporting Services
- Report Manager
- Part 2
- Parameterized Reports
- Calling Stored Procedures from Reports
- The Report Viewer Control
- Part 3
- Using Reporting Server Web Services
- Using Custom Code in Reports
- Subscription Scheduling in Reporting Server
- Caching and Rendering Preference
- Conclusion
- References and Further Readings
- About The Author
Using Reporting Server Web Services
Reporting Services exposes web methods via XML web services to facilitate cross platform reports management and delivery. It’s a simple SOAP based API makes it very easy to add full functionality of reporting services in your application. It provides a set of rendering and viewing functions as well as complete set of management routines. Reporting services web service is the one stop shop for subscription, management, publishing, scheduling and rendering reports.
In remaining section, I’ll demonstrate how can you use this web service in your web application. Reporting services WSDL can be located at
http://localhost/reportserver/reportservice.asmx or
http://[Machinename]/reportserver/reportservice.asmx in your machine. The web reference addition process is similar to adding any other XML web service in Visual Studio.NET. As shown in the figure below, you can add the web reference to your existing application. Complete list of web service methods can be seen here at MSDN:
Reporting Services Web Service Library.
Figure: Adding web reference to the application.
Figure: Reporting services web service properties window
Our first goal is to display the list of reports available the reporting services catalog. Using web services, ListChildren web method returns a list of CatalogItem which can be traversed to list the reports. Following code snippet demonstrates use of this web method.
private void Page_Load(object sender, System.EventArgs e)
{
if (!Page.IsPostBack)
{
this.objRS.Credentials = System.Net.CredentialCache.DefaultCredentials;
RSWebServiceXS.RSWebService.CatalogItem[] items = this.objRS.ListChildren("/", false);
for (int cnt=0; cnt<items.Length; cnt++)
this.cmbReports.Items.Add (items[cnt].Name.ToString());
}
}
Listing: index.aspx.cs :: Reading the report catalog in the combo box.
Figure: Web based console shows the report catalog entries iterated via code in comparison to those shown via report manager.
After viewing the catalogue, we might actually want to render the report. Yes, this is also possible by using Reporting Services web service. Render method takes a bunch of parameters and return the byte array as binary stream which can be written to a response object to view the report. Following is the signature of this web method.
public Byte[] Render( string Report, //The full path name of the report. string Format, //The format in which to render the report. string HistoryID, string DeviceInfo, // Device information settings [Namespace].ParameterValue[] Parameters, [Namespace].DataSourceCredentials[] Credentials, string ShowHideToggle, out string Encoding, out string MimeType, out [Namespace].ParameterValue[] ParametersUsed, out [Namespace].Warning[] Warnings out string[] StreamIds); Member of [Namespace].ReportingService
Following code segment and figure below shows how to utilize this web method to render the report in HTML.
private void Page_Load(object sender, System.EventArgs e)
{
ReportingService rs = new ReportingService();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
byte[] ResultStream; // bytearray for result stream
string[] StreamIdentifiers; // string array for stream idenfiers
string OptionalParam = null; // string out param for optional parameters
ParameterValue[] optionalParams = null; // parametervalue array for optional parameters
Warning[] optionalWarnings = null; // warning array for optional warnings
ResultStream = rs.Render("/Northwind Customers", "HTML4.0", null,
"<DeviceInfo><StreamRoot>/RSWebServiceXS/</StreamRoot></DeviceInfo>",null,null,
null, out OptionalParam, out OptionalParam, out optionalParams,
out optionalWarnings, out StreamIdentifiers);
// Write the report to Response
Response.BinaryWrite(ResultStream);
}
Listing: Reportform.aspx.cs
Figure: Rendering a report using Reporting Service web service.
Since the report is returned as a byte stream, it can be as easily written to a file as to a browser response. Reporting web services can be used to save the reports as rendered files in different formats. By using simple I/O operations on the returned stream, physical writing can easily be accomplished. In the listing below, I’m opening a file stream object, calling its write method and passing it Resultant Stream to write on disk. Interestingly, please note that this time the rendered stream is converted to a PDF and therefore System.IO will be writing a PDF file on media.
// Creating a verbatim string.
FileStream stream = File.OpenWrite(@"C:\Articles\SQL Server Reporting
Services\SourceCode\RSWebServiceXS\NorthwindCustomers\" + filename);
stream.Write(ResultStream, 0, ResultStream.Length);
stream.Close();
Following listing and screenshot shows how saving a report can be accomplished by Reporting Services web service.
private void btnSaveReport_Click(object sender, System.EventArgs e)
{
ReportingService rs = new ReportingService();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
byte[] ResultStream;
string[] StreamIdentifiers;
string OptionalParam = null, filename="NorthwindCustomers.pdf";
ParameterValue[] optionalParams = null;
Warning[] optionalWarnings = null;
ResultStream = rs.Render("/Northwind Customers", "PDF", null,
"<DeviceInfo><StreamRoot>/RSWebServiceXS/</StreamRoot></DeviceInfo>",null,null,
null, out OptionalParam, out OptionalParam, out optionalParams,
out optionalWarnings, out StreamIdentifiers);
// Creating a verbatim string.
FileStream stream = File.OpenWrite(@"C:\Articles\SQL Server Reporting
Services\SourceCode\RSWebServiceXS\NorthwindCustomers\" + filename);
stream.Write(ResultStream, 0, ResultStream.Length);
stream.Close();
Listing: index.aspx.cs: Saving the report as PDF file
Figure: Reporting Service Rendering PDF on the media
Using reporting services web service is a wide topic and a lot could be done using this powerful feature of Reporting services. In Wrox’s Professional SQL server reporting services, authors have designed a desktop subscription manager using reporting services web services which is a good exercise for anyone interested in extensive use of this technology.
Using Custom Code in Reports
To offer developers flexibility and power of using their own routines in reports, SQL server reporting Services offer the facility of writing custom code in the reports. In the report properties menu option, there is a tab for code where you can write your visual basic code to be called from report which can be seen in the figure below.
Figure: Reporting Services code window.
In this example I am adding custom code to render different hyperlinks in the report. To display a map of the location,
mapping service will be selected by country and the appropriate URL with country name will be placed in the Report Matrix.
When there is a US address, the displaylink function will use the Mapquest url and
for European maps, it will use Multimap.
The function, as it appears in the RDL file could be seen below.
<Code>
Public Function DisplayLink(ByVal Country, ByVal ShipPostalCode) As String
Dim strMapLink As String
Dim _PostalCode As String = ShipPostalCode.ToString()
Dim _Country As String = Country.ToString()
Select Case _Country
Case "USA"
strMapLink = "http://www.mapquest.com/maps/map.adp?country=US&
countryid=250&addtohistory=&searchtab=address&
searchtype=address&address=&city=&state=&zipcode="
& _PostalCode & "&fakeTab.x=0&fakeTab.y=0"
Case "France"
strMapLink = "http://www.multimap.com/map/browse.cgi?client=public
&GridE=4.04012807456334&GridN=49.2517559269884&
lon=4.04012807456334&lat=49.2517559269884&db=FR&
cname=Great+Britain&pc=" & _PostalCode
& "&overviewmap=&scale=50000&lang=&
client=public&cidr_client=none&advanced=&addr2=&
place=" & _PostalCode & "addr3="
Case "Germany"
strMapLink = "http://www.multimap.com/map/browse.cgi?client=public
&GridE=6.9220402870057&GridN=50.9799486648947&
lon=6.9220402870057&lat=50.9799486648947&db=DE&
cname=Great+Britain&pc=" & _PostalCode
& "&overviewmap=&scale=50000&lang=&
client=public&cidr_client=none&advanced=&addr2=&
place=" & _PostalCode & "&addr3="
Case "UK"
strMapLink = "http://www.multimap.com/map/browse.cgi?client=public
&db=pc&addr1=&client=public&addr2=&advanced=&
amp;addr3=&pc=" & _PostalCode
Case Else
strMapLink = "" ‘// TODO - add more mappings
End Select
Return strMapLink
End Function
</Code>
This code will be mapped to advanced text box properties as shown in the figure below. This will dynamically provide URL to navigate for appropriate map.
In the report RDL file, the hyperlink action will appear as follows.
<Action>
<Hyperlink>=Code.DisplayLink(Fields!ShipCountry.Value, Fields!ShipPostalCode.Value)</Hyperlink>
</Action>
As a result of this user defined function in report, different map services can be requested depending on geographic location.
For the US shipping product address, URL is rendered to show map via mapquest and for a Germany based address, map was brought
via Mapquest map services.
Subscription Scheduling in Reporting Server
Your client wants a XML based transaction summary report of merchandise sold in all of their 300 outlets by midnight on an FTP server. This report is then picked up by an external process to determine the sales threshold and stock needs. Developer Nightmare with custom reporting solution? Not anymore with SQL server reporting services. Without any external process or extra code, reporting services can do the scheduling and deliver the reports in various different file formats, including email (SMTP delivery) and ftp (file share).
The email configuration can be set via during installation via set up screen or later by editing RSReportServer.config configuration file which can be found at %Program Files%\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer
RSReportServer.Config
<Configuration>
<RSEmailDPConfiguration>
<SMTPServer>smtp.mycompany.com</SMTPServer>
<SMTPServerPort></SMTPServerPort>
<SMTPAccountName></SMTPAccountName>
<SMTPConnectionTimeout></SMTPConnectionTimeout>
<SMTPServerPickupDirectory></SMTPServerPickupDirectory>
<SMTPUseSSL></SMTPUseSSL>
<SendUsing></SendUsing>
<SMTPAuthenticate></SMTPAuthenticate>
<From> ReportingDeamon@mycompany.com </From>
<EmbeddedRenderFormats>
<RenderingExtension>MHTML</RenderingExtension>
</EmbeddedRenderFormats>
<PrivilegedUserRenderFormats></PrivilegedUserRenderFormats>
<ExcludedRenderFormats>
<RenderingExtension>HTMLOWC</RenderingExtension>
<RenderingExtension>NULL</RenderingExtension>
</ExcludedRenderFormats>
<SendEmailToUserAlias>True</SendEmailToUserAlias>
<DefaultHostName></DefaultHostName>
<PermittedHosts></PermittedHosts>
</RSEmailDPConfiguration>
</Configuration>
The scheduling console, as seen in the figure below is used for report scheduling. A report delivery can be scheduled for delivery on monthly, weekly, daily and ad-hoc basis. There is a wide variety of options available to setup the report scheduling including start and end date.
Figure: Shared Scheduling
Also, in the figure below, it shows how can you configure your report delivery hourly vs. daily schedule.
Figure: Hourly vs Daily Scheduling
While setting up delivery schedule, one can also setup the delivery format via this console as shown in figure below. I’m selecting the rendering format set to PDF.
If you have security concerns for report delivery, you can chose to only send the URL via email. This URL of your reporting server would be an https (SSL based) and can require authentication before showing the report. Currently, there is no password protected excel report delivery option available in reporting services however its in the wish list for future releases.
Figure: Individual Scheduling
After setting up a subscription for list, following entry will appear in the report’s subscription list.
Figure: Subscription Details
Caching and Rendering Preference
Robust caching and rendering is among the most exciting features provided by SQL server reporting services. This save round-trips for frequently accessed reports and can be programmatically controlled. To help conserve system resources, reporting services provide us three different ways of caching control; snap shots, history and cached instances. These methods are discussed in detail on MSDN
Report Caching in Reporting Services.
In the figure below, you can see the options provided for caching.
Figure: Caching details for report.
URL based rendering empowers the HTTP-GET based url access to support a wide variety of applications. For instance the URL below serves the report in HTML 4.0
http://localhost/ReportServer?%2fNorthwind+Customers&rs%3aCommand=Render&rs%3aFormat=HTML4.0&rc%3aReplacementRoot=http%3a%2f%2flocalhost%2fReports%2fPages%2fReport.aspx%3fServerUrl%3d&rc%3aToolbar=True&rc%3aJavaScript=True&rc%3aLinkTarget=_top&rc%3aArea=Report
To get this report as a PDF or a excel file, the only thing you’d have to change is the Format to PDF and it will render the report into PDF.
http://localhost/ReportServer?%2fNorthwind+Customers&rs%3aCommand=Render&rs%3aFormat=PDF&rc%3aReplacementRoot=http%3a%2f%2flocalhost%2fReports%2fPages%2fReport.aspx%3fServerUrl%3d&rc%3aToolbar=True&rc%3aJavaScript=True&rc%3aLinkTarget=_top&rc%3aArea=Report
The PDF rendered report can be seen in the save as dialog box in the figure below.
Conclusion
Business Intelligence helps building systems which enterprises use to evaluate and predict future with. Reporting systems are basic part of any corporate environment and with SQL server reporting services; this development can be done in a robust, scalable and cost effective way. Reporting Services integrated with Microsoft analysis services can provide excellent support for decision support systems and personals in fields of finance, medical research, market analysis, customer analysis, product management, customer profiling, product profitability, and inventory movement to name a few. I hope this article was helpful in understanding reporting services. Links and resources section will be able to further help you in finding your way forward.
Happy Reporting!
References and Further Readings
Resources
- Microsoft SQL Server: SQL Server Reporting Services Home
- InfoWorld: Microsoft launches SQL Server Reporting Services
- SQL Server Reporting Services experiences
- Reporting Services Datasheet
- Reporting Services Features Comparison
- Reporting Services Demo: How to Author, Manage, and Deliver Reports
- Reporting Services case Studies
- Latest News on SQL Server Reporting Services
- Report Definition Language Specification
- SQL Servertm 2000 Reporting Services Deployment Guide
- Reporting Server Sample Walkthroughs
Web casts, Audio Shows & Presentations
- Reporting Services Demo: Features and Benefits
- .NET Rocks! – First Live Show!
- SQL Server Chat: Reporting Services
Newsgroups
- NNTP: Microsoft SQL Server 2000 Reporting Services newsgroup
- via Google: Microsoft SQL Server 2000 Reporting Services newsgroup
Weblogs & Articles
- Microsoft Reporting Services in Action: Extending Microsoft SQL Server 2000 Reporting Services with Custom Code [MSDN]
- Develop Reports with SQL Server 2000 Reporting Services
by Tom Rizzo (thimriz@microsoft.com)
ASP.NET Pro Magazine, March 2004 - Trying Things Out In Reporting Services
- FogCreek Software, MS Reporting Services
- .Net Reporting
- Printing Reports Programmatically Using C# and SQL Server 2000 Reporting Services
- Documenting a SOAP API: The Reporting Services Web service
Downloads
- SQL Server Reporting Services Enterprise/Standard/Developers Edition Using MSDN Universal Subscription
- Microsoft SQL Server Reporting Services SP1
- How to Obtain SQL Server Reporting Services
About The Author
Other than a being a full time Computer Science (and other derived/real science for this matter) enthusiast, Adnan Masood
works as a Software Engineer for Next Estate Communications, Monrovia, California. He’s a published author of various
online articles and credited in in both print and online publications. He holds Msc. in Software Engineering from UNW,
London, England and BS in Computer Science. He holds various technical certifications including
(MCP, MCAD.NET, SCJP-II and soon to be MCSD.NET). Adnan holds a broad hybrid development development
experience on multiple software development technologies, and his main expertise are in building class
libraries, business objects, XML web services messaging, ASP.NET and server-side programming in general
on Microsoft.NET Platform. You can reach him via email at
amasood@bcs.org.uk
