Chapter-10 : Creating and Deploying Web Services for SSRS

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 –

Download Full Tutorial for Chapter-10-Creating-and-Deploying-WebServices-for-SSRS in PDF format



Sample Code Creating and Deploying Web Services for SSRS

Step-1: Create a WebService using visual studio 2010 as shown as click on file>New>Web site

Visual Studio 2010 Click -on Create New Website
Visual Studio 2010 Click -on Create New Website

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.

ASP.NET Web Service
ASP.NET Web Service




A default project with WebMethod “HelloWorld()” is created with default file “Service.asmx” and “Service.vb” as shown in the image.

Code Window
Code Window

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)
daCustomer.Fill(ds, “CustomerData”)
Return ds
End Function

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.

Please import “Imports System.Data” and “Imports System.Data.SqlClient”
Please import “Imports System.Data” and “Imports System.Data.SqlClient”

Run the WebService you will be able to see the following screen with our WebMethod “GetCustomer” appears on screen

Run the WebService
Run the WebService

Click on the link “GetCustomer” and it will return following screen.

Click on the link GetCustomer
Click on the link “GetCustomer”

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.

click on Invoke Button
Click on Invoke Button

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.

Deploying the WebServices to Internet Information Services (IIS).
Deploying the WebServices to Internet Information Services (IIS).

Right click on “Default Web Site” and click on Add Application as shown in the image given below.

Right click on Default Web Site and click on Add Application
Right click on “Default Web Site” and click on Add Application

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.

Provide the Alias as MyWebService
Provide the Alias as MyWebService

Project will be published on localhost as shown in the image below

Deploy to IIS
Deploy to IIS

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.

image023

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

Leave a Reply

Your email address will not be published. Required fields are marked *

*

15 − 1 =