SSRS can be used to develop Reports consuming the Web Services. In this tutorial we are going to create a sample Web Service which returns the Dataset of Customers table of Northwind Database. In Next Chapter-11 we will use the same web services for creating a SSRS Report. The steps are as follows –
Step-1: Create a WebService using visual studio 2010 as shown as click on file>New>Web site
On clicking, the new screen will appear(as shown below). Please choose .Net Framework 3.5 as shown in the box and provide the name of the website as an example here it is giving “MyWebService” in the location d:\wwwroot. You can choose the path as per your convenience.
A default project with WebMethod “HelloWorld()” is created with default file “Service.asmx” and “Service.vb” as shown in the image.
Please delete the Public Function HelloWorld() as we are going create a GetCustomer() WebMethod which will return a Dataset.
Step2: Replace the default generated WebMethod code of “HelloWorld()” Of “Service.vb” file with the following code
Public Function GetCustomer() As DataSet
Dim sqlText As String = “select CustomerID,CompanyName,ContactName,City,Country from customers”
Dim sqlConn As String = “packet size=4096;user id=sa; password=abc@123; data source=127.0.0.1;
initial catalog=Northwind;Max Pool Size=100”
Dim ds As New DataSet()
Dim daCustomer As New SqlDataAdapter(sqlText, sqlConn)
sqlText in the above code represent the SQL Script to return the data from customers table and sqlConn represent the connection string to SQL Server’s “Northwind” Database using the user id “sa” and Password “abc@123”.Please import “Imports System.Data” and “Imports System.Data.SqlClient” as we are using the dataset class and SQLDataAdapter for database connection as shown in the image.
Run the WebService you will be able to see the following screen with our WebMethod “GetCustomer” appears on screen
Click on the link “GetCustomer” and it will return following screen.
Now click on Invoke Button to Invoke the WebMethod under the WebService. It will return the dataset value connected with database “Northwind” as shown below. This completes the Process of Creating WebServices.
Step-3: Deploying the WebServices to Internet Information Services (IIS). In Windows 7/8 machine Open IIS from control panel >Administrative Tools>Right Click on IIS and Choose Run as administrator as shown in image.
Right click on “Default Web Site” and click on Add Application as shown in the image given below.
Provide the Alias as “MyWebService” and locate the directory of the folder from physical path. In our example it is under “D:\WWWROOT\MyWebService” and click on OK.
Project will be published on localhost as shown in the image below
Click on “Default Document” and remove all pages and add “Service.asmx to it. Now right click on “MyWebService” in IIS and select Manage Application>Browse, if screen opens as screen shown below indicates “MyWebService” is deployed to IIS Successfully.
This has completed the “Chapter-10: Creating and Deploying WebServices for SSRS”.
Chapter-1: Creating a basic Tabular SSRS Report using Report Wizard
Chapter-2 : Report Deployment on SQL Server 2012 Reporting Server
Chapter-3:Opening/Calling the deployed SQL Server Reporting Server report from Asp.net
Chapter-4 Creating SSRS Matrix Report / Cross Tab Report
Chapter-5 Creating a Sub Report in SSRS
Chapter-6: Creating a Drill Down Report in SSRS
Chapter-7: Working with expressions and custom code in SSRS
Chapter-8-SSRS (Reporting Services) Working with Calculated fields
Chapter-9-Sorting of Column and Custom Paging in SSRS
Chapter-10 : Creating and Deploying Web Services for SSRS