Business Intelligence with Microsoft SQL Server Reporting Services - Part 3

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.

Adding web reference to the application.
Figure: Adding web reference to the application.

Reporting services web service properties window
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.

Web based console shows the report catalog entries
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,
    null, out OptionalParam, out OptionalParam, out optionalParams,
    out optionalWarnings, out StreamIdentifiers);
// Write the report to Response

Listing: Reportform.aspx.cs

Rendering a report using Reporting Service web service.
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);

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,
    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);

Listing: index.aspx.cs: Saving the report as PDF file

Reporting Service Rendering PDF on the media
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.

Reporting Services code window.
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.

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 = ";

              &amp; _PostalCode &amp; "&amp;fakeTab.x=0&amp;fakeTab.y=0"
        Case "France"
            strMapLink = "
              cname=Great+Britain&amp;pc=" &amp; _PostalCode
              &amp; "&amp;overviewmap=&amp;scale=50000&amp;lang=&amp;
              place=" &amp; _PostalCode &amp; "addr3="

        Case "Germany"
            strMapLink = "
              cname=Great+Britain&amp;pc=" &amp; _PostalCode
              &amp; "&amp;overviewmap=&amp;scale=50000&amp;lang=&amp;
              place=" &amp; _PostalCode &amp; "&amp;addr3="

        Case "UK"
            strMapLink = "
              amp;addr3=&amp;pc=" &amp; _PostalCode
        Case Else
            strMapLink = "" ‘// TODO - add more mappings
    End Select

    Return strMapLink 
End Function


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.

<Hyperlink>=Code.DisplayLink(Fields!ShipCountry.Value, Fields!ShipPostalCode.Value)</Hyperlink>

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




        <From> </From>




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.

Shared Scheduling
Figure: Shared Scheduling

Also, in the figure below, it shows how can you configure your report delivery hourly vs. daily schedule.

Hourly vs Daily Scheduling
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.

Individual Scheduling
Figure: Individual Scheduling

After setting up a subscription for list, following entry will appear in the report's subscription list.

Subscription Details
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.

Caching details for report.
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


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.


The PDF rendered report can be seen in the save as dialog box in the figure below.


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


Web casts, Audio Shows & Presentations


Weblogs & Articles


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



  • golf carts, SUVs, motorcycles, jet skis, boats, and many others.

    Posted by deborahrsv on 05/15/2013 06:51am

    Sam finds the splinter destructive and gives it for safekeeping or supplement with DVDs. The digital academies provide some forms

  • wholesale sunglasses

    Posted by zgliliImpumpvzd on 04/01/2013 12:01pm

    fake ray ban sunglasses [url=]fake ray ban sunglasses[/url] cheap oakleys fake ray ban sunglasses [url=]fake ray ban sunglasses[/url] cheap wayfarer sunglasses cheap fake oakleys [url=]cheap fake oakleys[/url] cheap oakleys fake ray ban [url=]fake ray ban[/url] wholesale designer sunglasses cheap ray ban [url=]cheap ray ban[/url] fake ray ban wayfarer designer sunglasses cheap [url=]designer sunglasses cheap[/url] fake oakleys sunglasses fake oakley sunglasses [url=]fake oakley sunglasses[/url] fake ray ban fake ray ban [url=]fake ray ban[/url] discount sunglasses

  • Lingerie Costumes

    Posted by Fishnetox1073 on 03/29/2013 10:00am - Sexy Lingerie storeHence, black suit is often seen in formal functions Ask any woman and you will perhaps get an idea how close to the heart of a woman is lingerie - Sexy Babydoll LingerieBy listing an auction for your services, you will allow others to compete for your business Apart from that, you can choose from a wide range of colors these wholesale lingerie pieces are available in including red, white, yellow, black and blue - Chinese ManufacturerPurchase items from online distributors and resell them online via your website or eBay auctions They also have stockings, sleepwear and shoes - Sexy Lingerie ShopThose with large bosoms might prefer fitting designs that can be achieved with stretching synthetic materials that can be flexible to make them appear slimmer with a tighter, smoother finishDreamgirl is actually recognised as the leading marketer and manufacturer of sexy lingerie - Chemise LingerieIf she is really athletic, a boxer set might be something she would like Third, what special perks are afforded? What kind of special time off is there for busy people to go shopping, to prepare special meals, to travel during this time? Not everyone has the luxury of a school schedule; most companies close only a day or two during holidays

  • Hot Teddy

    Posted by Fishnetfw1095 on 03/29/2013 09:00am - Lingerie TeddiesIf you're not sure what to do, consult a child It gives us a great reason us be more adventurous, more self-assured, and more free, which may very well filter down into our everyday lives - Nurse LingerieMeanwhile, yellow and blue, red and green are known as contrast color  If she is an outgoing person then you can choose lingerie that is a little more exciting and daring - Sexy BodyStockingss undergarments, sometimes, including the breasts?Lovers?Lane - white lace lingerieThin women will look good in anything, but especially teddies, corsets, and babydolls Invite your co-workers, family and friends to join you - Sexy LingerieAsk any woman and you will perhaps get an idea how close to the heart of a woman is lingerie It should be elastic around the bust and it should be able to stretch while ensuring that it remains firm

  • Sexy Lingerie sale

    Posted by Fishnetzv1006 on 03/29/2013 08:39am - Cheap Bustiers?They have created all the latest in dreamgirl style including the most comprehensive ones One of the most popular types of lingerie is the baby doll lingerie set - French Maid LingerieHe was so pleased with the system that he had it installed in all his restaurants On this day, you can be whoever you want - womens babydollThe tops are tight, with thin straps and usually a V neckline, although there are boxer sets with normal t-shirt topsFor those who prefer a bit of modesty, matching panties in a similar material are readily available, often sold as part of a set - sexy babydollsShopping and deciding appropriate bridal lingerie is a difficult task, as individual brides require different types of lingerie And it always gives off the impression that a woman wearing this color is one who is confident, headstrong and sexy - womens sexy clothesAbout the Author:Mac, is the founder of the one stop gift site - UniqueGiftLane Lingerie can now be boasted by every woman regardless of her size and figure

  • fake ray ban

    Posted by egliliImpumpcxl on 03/29/2013 12:08am

    cheap oakley [url=]cheap oakley[/url] discount sunglasses discount sunglasses [url=]discount sunglasses[/url] fake ray ban wayfarer cheap oakley [url=]cheap oakley[/url] oakley sunglasses discount cheap oakleys for sale [url=]cheap oakleys for sale[/url] cheap ray ban wayfarer cheap sun glasses [url=]cheap sun glasses[/url] discount sunglasses cheap sunglasses [url=]cheap sunglasses[/url] cheap oakley cheap oakley [url=]cheap oakley[/url] cheap ray ban sunglasses cheap sunglasses [url=]cheap sunglasses[/url] cheap oakleys

  • oakley sunglasses cheap

    Posted by ogliliImpumpswr on 03/28/2013 10:00pm - cheap fake oakley sunglasses ray ban wayfarer cheap - cheap oakley cheap ray ban,,,, - fake oakleys cheap ray ban wayfarer - ray ban sunglasses cheap fake ray ban - fake oakleys cheap ray ban,,,,

  • fvfylk

    Posted by Suttonjtt on 03/28/2013 10:49am

    ray ban wayfarer sizes,oakley sunglasses sale of course did not mean it, do not misunderstand me, oh! If Miss Sophia knew I destroyed this cooperation will certainly blame me, that time may suffer. Felicity lips say, but her beautiful face still smiling, and did not see a little bit of fear, Xiao Feng pomp seems do not care. Well, she is a robot, but also just a copy of myself., No wonder she so brazenly. Xiao Feng mind a move, quickly use the spirit of the scan scan inside and out in the hall a bit. He soon found that the hall is unusual, it seems that the other side can be really hard ah! It seems that ray ban have been ready, ah! Those facilities ...... ah, cheap ray ban sunglasses do not worry, I will not move ray ban wayfarer. 00ks.

  • wholesale snapbacks

    Posted by pgexpenueMoxjef on 03/27/2013 08:52am

    cheap snapbacks from chinaRefurbished laptops can be purchased several ways [url=]wholesale snapbacks[/url] The more oxygen you can get inside your muscle tissue just after exercise, the quicker they recoup I consider that I can be including to the reports in a number of months with my feedbackOn the same day in the blonde boundless charm, the presence of all the lady-killer is fierce swallow slobber, make Chen South still fresh in [url=]snapback hats wholesale[/url] Chen south with mortal combat mindset, completely into a frenzy in real environment, he fought in the windDeclaring bankruptcy could not just wipe away the whole lot from the slate and give a person the precise to begin once more with a clean sheetWhile value is the main consideration for most individuals whereas selecting a web hosting supplier, there are additionally a number of different factors which should be appeared into [url=]cheap sunglasses[/url] The concern here, black community practitioners from several scream, the warm light the place visited Earth totemThe great magic hair out with a roarHowever, some of his frown, so many people, but the strength was not so strong, but the third day order dungeon prisonKeep in mind that once the body creates fat cells, the cells are not destroyed [url=]cheap snapbacks free shipping[/url] You should ask how long the school has been running when you do visit it Nevertheless, many people would like to know extra about offshore banking options for a portion of their wealth as a result of they view taxes as an all too unnecessary evilPork: Relying on the reduce, the piece of pork in entrance of you may be akin to low-fats, low-calorie rooster, or as excessive in fats as a sizzling dog

  • ghd australia mlhmwa

    Posted by Mandyore on 02/07/2013 04:09pm

    1yFbb christian louboutin gKzb longchamp outlet aLba michael kors outlet 3jPty 3nWfm chi 5sAtt michael kors outlet 5tVng cheap nfl jerseys 3eBjt nike uk 9nAjq ghd 0kAte ugg 4dRhj toms outlet 6rDdz Tory Burch Reva Snake Print Ballerina Flats Cheap 0eUiq hollister 6uVot ghd 9cQnc ugg boots sale

  • Loading, Please Wait ...

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • As all sorts of data becomes available for storage, analysis and retrieval - so called 'Big Data' - there are potentially huge benefits, but equally huge challenges...
  • The agile organization needs knowledge to act on, quickly and effectively. Though many organizations are clamouring for "Big Data", not nearly as many know what to do with it...
  • Cloud-based integration solutions can be confusing. Adding to the confusion are the multiple ways IT departments can deliver such integration...

Most Popular Programming Stories

More for Developers

RSS Feeds

Thanks for your registration, follow us on our social networks to keep up-to-date