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)
{
StringBuilder myQuery = new StringBuilder();
XmlDataDocument resultXMLDocument = new XmlDataDocument();
SqlConnection myConnection = new SqlConnection();
SqlCommand myCommand = new SqlCommand();
SqlDataAdapter myDA = new SqlDataAdapter();
DataSet myDS = new DataSet();
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");
}
myConnection.ConnectionString = ReadSetting("ConnectionString", "");
myCommand.Connection = myConnection;
myCommand.CommandText = myQuery.ToString();
myCommand.CommandType = CommandType.Text;
myDA.SelectCommand = myCommand;
try
{
myDA.Fill(myDS, "Address");
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)
{
ReportParameter cityID = new ReportParameter();
cityID.Name = "cityNameID";
cityID.Values.Add("Al");
rptViewer.ProcessingMode = ProcessingMode.Remote;
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.
