Click here to Skip to main content
15,867,453 members
Articles / Web Development / IIS
Article

The new reporting horizons with Microsoft Reporting Services 2005

Rate me:
Please Sign up or sign in to vote.
4.65/5 (51 votes)
18 May 20065 min read 175.4K   921   109   34
How to use a WebService as a data source to build a report with Microsoft Reporting Services 2005.

Image 1

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:

C#
[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

Image 2

Fig. 2 The query string

Image 3

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

Image 4

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

Image 5

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

Image 6

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

Image 7

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

Image 8

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:

C#
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


Written By
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

 
GeneralSSRS - Chart Control Pin
T. Ravindran12-Aug-08 5:14
T. Ravindran12-Aug-08 5:14 
Questionrdlc report [modified] Pin
rajkumarg_7713-May-08 4:01
professionalrajkumarg_7713-May-08 4:01 
QuestionHow to download the source code? Pin
hanqin.lee8-May-08 3:37
hanqin.lee8-May-08 3:37 
QuestionReporting Services Pin
Pragaas10-Sep-07 19:28
Pragaas10-Sep-07 19:28 
Questionhai Pin
veeru.k11-Jun-07 1:11
veeru.k11-Jun-07 1:11 
AnswerRe: hai Pin
Lev Vayner.11-Jun-07 7:50
professionalLev Vayner.11-Jun-07 7:50 
GeneralOpen .rdlc designer at runtime Pin
Mukund Pujari13-Mar-07 22:47
Mukund Pujari13-Mar-07 22:47 
GeneralRe: Open .rdlc designer at runtime Pin
Dimitar Madjarov14-Mar-07 3:09
Dimitar Madjarov14-Mar-07 3:09 
GeneralRe: Open .rdlc designer at runtime Pin
Lev Vayner.11-Jun-07 7:56
professionalLev Vayner.11-Jun-07 7:56 
GeneralQuestions please Dimitar, I am studying this application Pin
kermitpiper10-Feb-07 16:17
kermitpiper10-Feb-07 16:17 
GeneralRe: Questions please Dimitar, I am studying this application Pin
Dimitar Madjarov11-Feb-07 1:29
Dimitar Madjarov11-Feb-07 1:29 
QuestionrptViewer.DataBind(); Pin
Member 35867768-Jan-07 14:23
Member 35867768-Jan-07 14:23 
Hi;
I downloaded the app, and I'm stuck on the following error

Error 1 The name 'rptViewer' does not exist in the current context

any one can help me with this error

Thanks



Sam
stharia@michiganheart.com
AnswerRe: rptViewer.DataBind(); Pin
Dimitar Madjarov8-Jan-07 22:01
Dimitar Madjarov8-Jan-07 22:01 
QuestionrptViewer.DataBind(); Pin
Member 35867768-Jan-07 14:22
Member 35867768-Jan-07 14:22 
AnswerRe: rptViewer.DataBind(); Pin
Dimitar Madjarov8-Jan-07 21:59
Dimitar Madjarov8-Jan-07 21:59 
GeneralWeb Services in Reporting Services 2005 Pin
drifter578115-Dec-06 20:31
drifter578115-Dec-06 20:31 
GeneralRe: Web Services in Reporting Services 2005 Pin
Dimitar Madjarov16-Dec-06 0:32
Dimitar Madjarov16-Dec-06 0:32 
GeneralRe: Web Services in Reporting Services 2005 Pin
drifter578123-Dec-06 13:48
drifter578123-Dec-06 13:48 
GeneralRe: Web Services in Reporting Services 2005 Pin
Dimitar Madjarov23-Dec-06 22:23
Dimitar Madjarov23-Dec-06 22:23 
GeneralRe: Web Services in Reporting Services 2005 Pin
rylyty16-Apr-08 4:24
rylyty16-Apr-08 4:24 
QuestionReport Serveices Pin
flysky61813-Dec-06 20:22
flysky61813-Dec-06 20:22 
AnswerRe: Report Serveices Pin
Dimitar Madjarov13-Dec-06 21:53
Dimitar Madjarov13-Dec-06 21:53 
QuestionXML as ref parameter Pin
Slobodan30-May-06 9:22
Slobodan30-May-06 9:22 
AnswerRe: XML as ref parameter Pin
Dimitar Madjarov31-May-06 10:24
Dimitar Madjarov31-May-06 10:24 
GeneralTell me one thing plz... Pin
BlackTigerAP24-May-06 10:41
BlackTigerAP24-May-06 10:41 

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

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