I recently worked on a project that used web services to feed information to the SSRS reports that were created for the various business segments at this particular organization. Stemming from that project, this blog post will present how to create a simple web service that returns a serialized dataset and a simple SSRS report that uses (consumes) this web service to feed data to the report.
A web service provides data as a service over the Internet or within an internal network. Standard Web Services use the SOAP protocol which defines the communication and structure of messages, and XML is the default data format. Web services are designed to allow applications built using different technologies to communicate with each other without issues. Examples of web services include Weather.com and the way the site provides weather information that other sites can use, or UPS.com providing a method to request shipping quotes or tracking of packages.
Building a Simple Web Service
In the following example, I am going to detail how to build a simple web service which will return a single data set (or list) of information about a fictitious apartment building. I will also show how to create a simple SSRS report which consumes this dataset and produces a table listing of this data. For this example, I am using Visual Studio 2013 with the BIDS add-on for 2014. I have written the web service example in C# and I am using .NET Framework 3.0 Web Service technology for the web service itself.
The Web Service
1. Open Visual Studio and create a simple web service project as seen below. In this example, I have given the web service a name of ‘AptWebSVC’.
Below is what the code in the AptWebSVC.Asmx.cs file will look like when it is first created:
2. Rename the Class ‘Service1’ to ‘AptWebSVC’.
public class AptWebSVC : System.Web.Services.WebService
3. Rename the file called Service1.asmx to AptWebSVC.asmx in the Solution Explorer window.
4. Right click the ‘AptWebSVC.asmx’ file in the Solution Explorer window and select ‘View Markup’ in order to edit the Class designation on the page so that it looks like the following:
5. Modify the code for the web service to provide the dataset that you wish to return. For the purposes of this demonstration, I have simply provided one method that returns a list of hard-coded values representing a fictitious apartment building and some basic information about each apartment. Below is the sample code that is in this solution. Note that at the top I have defined a class which will represent the data that is going to be returned by the web service method called ‘GetAllUnits()’.
6. Build and publish this web service to IIS on the web server of your choosing. Not that for this blog, I assumed that the reader knows how to publish and install a web service under Microsoft’s IIS server.
7. Test the web service and make sure that it returns a dataset that looks like the following output:
Notice that the data returned is surrounded by XML tags and has been converted to ASCII. This is important to understand when manipulating or using the data in the SSRS report designer language (RDL). For instance, if you are passing a field of type ‘Decimal’ or ‘Double’ from the web service to the SSRS report, you will need to convert this ASCII field from the web service to a double or decimal field inside the report when using it for calculation purposes.
Designing the SSRS Report
1. Create a new Visual Studio project for Reporting Services like the following:
– In this example, I have named the report ‘ApartmentReport’.
2. Right click on ‘Reports’ in the Solution Explorer and add a new report:
– I have named this report ‘ApartmentReport.rdl.
3. On the left of the screen, right click on ‘DataSources’ and select ‘Add Data Source’:
4. In the dialog box that is displayed, give the data source a name (I named mine ApartmentDataSource). Select the ‘Embedded Connection’ radio button, Select ‘XML’ as the Type and enter the HTTP address of the web service that was deployed to IIS in the earlier step. Mine happens to be http://localhost:3010/AptWebSvc.asmx.
5. On the ‘Credentials’ tab of this dialog, choose the credential method that makes sense for your installation.
6. Right click on the ‘Datasets’ field on the Report Data view and choose ‘Add Dataset’:
7. In the dialog box for the new dataset, give the dataset a name (I named mine ‘ApartmentDataSet’. Select the ‘Use a dataset embedded in my report’ radio button. Select the ‘ApartmentDataSource’ from the DataSource drop down and then fill in the Query text with what is shown below.
– Note that the Method name (GetAllUnits()) in the ‘Query’ text is the same name as we coded in our web service.
8. Click OK.
9. Add a table to the report design surface and fill in the header columns like below:
10. Right click the top left corner of the table and select ‘Tablix Properties’:
11. Under the ‘General’ section of this dialog, give the table a name and then select the ‘ApartmentDataSet’ from the drop down box:
12. In each of the columns for the table on the report, right click on the column under the corresponding header and select ‘Expression’:
13. Choose the correct dataset field to be placed into the text box for each column:
14. Preview the report and the output should look like the following:
As you can see, adding a web service to a report which has been designed using SSRS is simple and extremely flexible. Since the web service handles all of the formatting and gathering of the data for each web method which returns data, complex logic can be implemented in the web service to perform tasks that might otherwise be difficult, if not impossible to do using SQL at run time. In addition, you are not limited to just one data method in the web service. Each dataset required for a report can call a different method if needed to format the data in the manner that is desired for the report. While not covered in this tutorial, it is also easy to pass parameters into the web service from the report to allow for data filtering using the criteria passed in to the web service method.
As you can see, following these few steps, it’s relatively easy to create a simple web service that returns a serialized dataset and a simple SSRS report that uses web service to feed data to the report.
