Click here to Skip to main content
Click here to Skip to main content

The new reporting horizons with Microsoft Reporting Services 2005

, 18 May 2006
Rate this:
Please Sign up or sign in to vote.
How to use a WebService as a data source to build a report with Microsoft Reporting Services 2005.

Introduction

A few months ago, I was involved in testing and estimating the new options which Microsoft Reporting Services 2005 provides to developers to make better reports. One of these options is the great ability to use one ore more WebServices as a data source. Unfortunately, the Help which comes with MSDN and the MS SQL Server 2005 books is not clear enough and the Help has a few printed mistakes. These mistakes make the developer's life hard, and the developer might end up spending a lot of hours to solve problems. Therefore, this article intends to share my knowledge about this wonderful opportunity which Microsoft Reporting Services 2005 offers us and save your time.

Creating a WebService

My first step was to create an test WebService which later will be used as data source for my test/demo report. This is a very important step because, before providing a DataSet as data source, we have to transform it to a XmlDataDocument. Without this transformation, we will not get any result on the next step when we try to use the Webservice web methods to retrieve data from the AdventureWorks sample database. This transformation is represented in C# code as below:

[WebMethod]
public XmlDataDocument GetPersonAddress(string cityNameID)
{
   //
   // Define the local variables
   //   
   StringBuilder   myQuery           = new StringBuilder();
   XmlDataDocument resultXMLDocument = new XmlDataDocument();
   SqlConnection   myConnection      = new SqlConnection();
   SqlCommand      myCommand         = new SqlCommand();
   SqlDataAdapter  myDA              = new SqlDataAdapter();
   DataSet         myDS              = new DataSet();

   
   //
   // Prepare different query depend from precondition
   //   
   if ((cityNameID != null) && (cityNameID.Trim() != ""))
   {
    myQuery.Append("Select City as City, " + 
                   "AddressLine1 as Address, " + 
                   "PostalCode From Address ");
    myQuery.Append("Where City Like '" + 
                   cityNameID.Trim().Replace("%", "") + 
                   "%' Order By City");
   }
   else
   {
    myQuery.Append("Select City as City, AddressLine1" + 
                   " as Address, PostalCode From Address" + 
                   " Order By City");
   }

   
   //
   // Get connection string and establish connection with server
   //   
   myConnection.ConnectionString = ReadSetting("ConnectionString", "");
   myCommand.Connection          = myConnection;
   myCommand.CommandText         = myQuery.ToString();
   myCommand.CommandType         = CommandType.Text;
   myDA.SelectCommand            = myCommand;

   
   //
   // Return an DataSet with data from our query
   //   
   try
   {
      myDA.Fill(myDS, "Address");
      
      //
      // Here we have to tansform our DataSet
      // into XmlDataDocument before to return it
      //
      XmlDataDocument temporaryXMLDoc = 
                new XmlDataDocument(myDS);
      resultXMLDocument = temporaryXMLDoc;
      temporaryXMLDoc = null;
    }
    catch
    {
       resultXMLDocument = null;
    }
    finally
    {
       myDS.Dispose();
       myDA.Dispose();
       myCommand.Dispose();
       myConnection.Dispose();
       myQuery = null;
    }

   return resultXMLDocument;
}

Finally, we have to build our Webservice and publish it in our IIS 5.0 server. Because we are using VS2005, and in case that your computer has VS2003 installed, please check that when you publish the Webservice, the virtual application/directory on which you publish and associate the service has its default setting as Microsoft ASP.2.0.

Creating and deploying the report which uses the WebService as a datasource

Our next step is to create a report with Microsoft Reporting Services 2005 which will use as data source the webservice which we built and published in our first step. To complete this task, you have to install the Microsoft Reporting Services from the installation CD of Microsoft SQL Server 2005. We will create a new empty Reporting Services project with the name "TestReport". After that, we will add a separate Shared Data Source. Here, the important thing is the connection string which you will write in the General tab when you choose your data source type to be XML. This connection string is shown below. Also, there you may see our query with which we take data from the Webservice. Here "http://madjarov_d_n_demo.org" is our service namespace. Also, here it is very important to name the method which you like to call in the Webservice. In our case, this is "GetPersonAddress".

Fig. 1 The connection string

Fig. 2 The query string

Finally, you may choose Next and build the report. The final report in the designer of MS SQL Server 2005 and VS2005 Studio looks as is shown below:

Fig. 3

Now, what happens with our query parameter from our Webservice method GetPersonAddress(string cityNameID)? We have to define it somewhere and use it, of course. For our convenience, Microsoft has made a perfect and developer-friendly report engine. To implement the parameters in a report from Microsoft Reporting Services 2005 is very easy. First, we have to choose Report Parameters in design mode, and add a parameter with the name "cityNameID" and data type "string". This is shown in the picture below:

Fig. 4

Now that the last step is to connect this report parameter with the DataSet. For this purpose, we have to edit the data source in the design mode of our report and create an new DataSet parameter, "cityNameID", and connect it logically with "Parameters!cityNameID.Value", which is our previously defined report parameter. You may see this connection on the picture below:

Fig. 5

Now, we have to deploy this report on the report server. Before this action, you have to be sure that your settings "TargetReport Folder" and "TargetReport Server" have the correct values. You can see these report project properties as in the picture below:

Fig. 6

Please notice that the settings which are shown on the picture above are my current settings, and on your computer, you might have different ones. Now we are ready with this test/demo report, and may deploy it on a suitable reporting server.

Create a simple web based viewer for our report

Our last task is to create a web based viewer application which will be responsible to take the results from the report and visualize them to us. Also, more important than this is to show you how to send parameters from your code to your report. For this, we have to create a new web project from VS 2005 (C#) environment with name "TestReportWebViewer". Just set the "Default.aspx" page as the default page for our new project and add from "ToolBox" a "Report Viewer" control to our page. Set this control with the appropriate size and set the Viewer settings as follows:

Fig. 7

Please notice that the settings "Reportingpath" and "ReportServerUrl" have the same values as our report which we deployed in the previous step. This is very important because otherwise our report viewer will not be able to show us our report. Below is the source code for the Init event of our report viewer component:

protected void rptViewer_Init(object sender, EventArgs e)
{ 
 //
 // Create a new report's parameter and set it with inital value "Al"
 //   
 ReportParameter cityID   = new ReportParameter();
 cityID.Name              = "cityNameID";
 cityID.Values.Add("Al"); 
 
 //
 // Setting-up the reportviewer control to use remote mode for processing
 //   
 rptViewer.ProcessingMode = ProcessingMode.Remote;
    
 //
 // Send parameter and initialize the viewer control with correct report.
 //   
 rptViewer.ServerReport.SetParameters(new ReportParameter[] { cityID });
}

As final words, I would like to thank you for your patience, and I hope that this article will be really useful for you and will give you the right direction when you try to implement Webservices as a data source in your Microsoft Reporting Services 2005 projects. Please download the sources from this article, and take a look.

N.B I would like to express my special gratitude to my best colleague Mr. Svilen Donev for his valuable support.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Dimitar Madjarov
Software Developer (Senior) http://www.uplandsoftware.com/
Canada Canada
Canada, Quebec, Laval,
Currnet position: Senior ASP.NET Developer
Company: Uplandsoftware Inc
Certificates: MCITP, MCP, MCTS

Comments and Discussions

 
Questionrdlc report [modified] Pinmemberrajkumarg_7713-May-08 4:01 
QuestionHow to download the source code? Pinmemberhanqin.lee8-May-08 3:37 
QuestionReporting Services PinmemberPragaas10-Sep-07 19:28 
Questionhai Pinmemberveeru.k11-Jun-07 1:11 
AnswerRe: hai PinmemberLev Vayner.11-Jun-07 7:50 
GeneralOpen .rdlc designer at runtime PinmemberMukund Pujari13-Mar-07 22:47 
GeneralRe: Open .rdlc designer at runtime PinmemberDimitar Madjarov14-Mar-07 3:09 
GeneralRe: Open .rdlc designer at runtime PinmemberLev Vayner.11-Jun-07 7:56 
GeneralQuestions please Dimitar, I am studying this application Pinmemberkermitpiper10-Feb-07 16:17 
GeneralRe: Questions please Dimitar, I am studying this application PinmemberDimitar Madjarov11-Feb-07 1:29 
QuestionrptViewer.DataBind(); PinmemberMember #35867768-Jan-07 14:23 
AnswerRe: rptViewer.DataBind(); PinmemberDimitar Madjarov8-Jan-07 22:01 
QuestionrptViewer.DataBind(); PinmemberMember #35867768-Jan-07 14:22 
AnswerRe: rptViewer.DataBind(); PinmemberDimitar Madjarov8-Jan-07 21:59 
GeneralWeb Services in Reporting Services 2005 Pinmemberdrifter578115-Dec-06 20:31 
GeneralRe: Web Services in Reporting Services 2005 PinmemberDimitar Madjarov16-Dec-06 0:32 
GeneralRe: Web Services in Reporting Services 2005 Pinmemberdrifter578123-Dec-06 13:48 
GeneralRe: Web Services in Reporting Services 2005 PinmemberDimitar Madjarov23-Dec-06 22:23 
GeneralRe: Web Services in Reporting Services 2005 Pinmemberrylyty16-Apr-08 4:24 
QuestionReport Serveices Pinmemberflysky61813-Dec-06 20:22 
AnswerRe: Report Serveices PinmemberDimitar Madjarov13-Dec-06 21:53 
QuestionXML as ref parameter PinmemberSlobodan30-May-06 9:22 
AnswerRe: XML as ref parameter PinmemberMadjarov.D.N31-May-06 10:24 
GeneralTell me one thing plz... PinmemberBlackTigerAP24-May-06 10:41 
GeneralRe: Tell me one thing plz... PinmemberMadjarov.D.N24-May-06 22:16 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140821.2 | Last Updated 18 May 2006
Article Copyright 2006 by Dimitar Madjarov
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid