WebService as DataSource in SSRS

In this article, I will show you how to use Web Service as Data Source in SSRS.

1. First of all create one web service in visual studio 2010. In that web service create one Web Method which returns list of strings.

?

1 2 3 4 5 6 7 8 9 10 11 12 13

[WebMethod] public List<string> getListPlayers() {     List<string> list = new List<string>();     list.Add("ABC");     list.Add("MNO");     list.Add("PQR");     list.Add("XYZ");     return list; }

2. Now publish web service on your IIS. To check whether web service was published successfully or not, paste following URL on your Internet Explorer.

 http://localhost/Service1.asmx

you will see results something like below:

3. Now open Visual Studio 2010 and open your existing ssrs project or create new project. In that project crate new report named asDemoWebServiceDataSource. Then create a new Data Source. To create a new data source, right click on Data Source in Report Data window and selectAdd Data Source.

A data source properties window opens. In that select Embedded connection and select XML from list of data source.In connection string text box enter following URL :

http://localhost/Service1.asmx?wsdl

Then click on OK button.

4. Then right click on Datasets in Report Data window and select Add Dataset.

A Dataset Properties window opens. In that select DataSource and enter following xml query into text box of query.  Then click on OK button.

?

1 2 3 4

<Query> <Method Namespace="http://tempuri.org/" Name="getListPlayers"></Method> <SoapAction>http://tempuri.org/getListPlayers</SoapAction> </Query>

5. Now your report design looks like below :

6. Now right click on Report Area and go to Insert–>Table. Now select string as Data field in 1st Column and delete rest 2 columns.

so your report design looks like below :

7. To see the results click on preview tab. Your report should look like below screenshot :

Congratulations! We successfully completed use of WebService as DataSource in SSRS.

Call SSRS Report With Prameters from ASP.NET Web Application

1. First of all open Visual Studio 2008 then go to New >> Website

2. Then Browse the directory where you want to save New Web Site.

3. Now in your web page add following controls:

• One ScriptManager
• One Button
• One MicrosoftReportViewer

So your web page design will look like following:

4. Now add following code on button’s click event in your .cs file :

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

using Microsoft.Reporting.WebForms; try {    MyReportViewer.ProcessingMode = ProcessingMode.Remote;    MyReportViewer.ServerReport.ReportServerUrl = new Uri("http://bhushan-pc:8080/ReportServer");    MyReportViewer.ServerReport.ReportPath = "/StartSSRS/PersonAddressDetails";    MyReportViewer.ServerReport.Refresh();    ReportParameter[] reportParameterCollection = new ReportParameter[1];       //Array size describes the number of paramaters.    reportParameterCollection[0] = new ReportParameter();    reportParameterCollection[0].Name = "City";                                 //Give Your Parameter Name    reportParameterCollection[0].Values.Add("Seattle");                         //Pass Parametrs's value here.    MyReportViewer.ServerReport.SetParameters(reportParameterCollection);    MyReportViewer.ServerReport.Refresh(); } catch (Exception ex) {    Response.Write(ex.ToString()); }

5. Now Run your website and click on Get Report Button.
It will show following results in your web page :

Congratulations! We successfully called our Parameterized SSRS report from ASP.NET web application.