ASP.NET - How to consume created Report service from sql server in asp.net page

Asked By aman on 10-Jan-12 06:22 AM
Hello everyone
i am creating a report service into sql server business intel... so how can i consume this created Report service from sql server in my asp.net page?
i am also deploy this report.
please help me
thanks 

D Company replied to aman on 10-Jan-12 06:36 AM
Hello Aman,

Here is the step by step process to populate SSRS report in web application.


Reference reporting service WSDL

  • 1)  From within your Solution Explorer pane, right click on the project name and select Add Web Reference... to create a new web services reference.

    http://blog.infotoad.com/pics/reporting-services/webServiceReference.jpg

    2) For URL: enter "http://localhost/reportserver/ReportService2005.asmx?wsdl" (replace localhost  with your server name, if different).

    3) For Web Reference Name: enter RS2005.

    http://blog.infotoad.com/pics/reporting-services/addWebReferenceDialog.jpg

    4) Click Add Reference button.

    You will notice a new folder named Web References with RS2005 reference located within the folder.

    http://blog.infotoad.com/pics/reporting-services/rs2005Reference.jpg

     You have completed referencing the Reporting Services WSDL
  • Add a control

  • 1)  When you create your project, the ASP.NET Web Application template should have included a default.aspx page, along with a default.aspx.cs file. If you do not have this file, please add a new web form to your project and name it "default.aspx".

    2) Open the default.aspx web form in design mode.

    3) Point to the Toolbox pane (if not visible) and expand the Navigation tab.

    4) Drag on drop the TreeView object to the default.aspx web form.

    5) From within the design pane, right click the TreeView item you just added and select properties.

    6) From the properties pane on the right, name the TreeView "tvCatalog".

    Steps 7-9 are optional.

    7) Right click on the TreeView object and click the right arrow to expand the TreeView Tasks.

  • 8) Select Auto Format...
  • 9) From the Select a scheme:, select "XP File Explorer" and then click OK.
  • 10) Save your work.

Consume reporting service

 

1) Open the default.aspx.cs file.
2) In order to communicate with our web service we need to add the following reference to our file.
using RSIntegrationDemo.RS2005;

3) Add the following code above
4) We need to create and instance of ReportingService2005 and pass credentials in order to authenticate to the web service. Enter the following code within the Page_Load event.

//Sample code
Page_Load(object sender, EventArgs e)

 private ReportingService2005 rs;

      protected void Page_Load(object sender, EventArgs e)

      {

  protected void Page_Load(object sender, EventArgs e)

      {

        rs = new ReportingService2005();

        // Create an instance of the Web service proxy and set the credentials

        rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

 

        if(!IsPostBack)

          BuildTreeView();

      }

Note: The DefaultCredentials property applies only to NTLM, negotiate, and Kerberos-based authentication. For ASP.NET applications, the default credentials are the user credentials of the logged-in user, or the user being impersonated. If you have problems authenticating, make sure the application pool account used has permissions to SSRS.  

5) Next, we will add our private methods, which will populate the treeview control with report items from the catalog. The first method, BuildTreeView() creates the Parent Node for our treeview control. The Method then calls the GetCatalogItems() method, which populates the treeview control with Report Items. 

Objects and Methods used:

CatalogItem - This object is used to return a report item from the report server database. We will pass an array of CatalogItem objects.

ListChildren - This will return a list of children withing a specified folder. For example, if we wanted all folder, reports, datasources, etc. returned, we would simply pass the following: 


//Populate report

 /// <summary>

      /// Build Treeview

      /// </summary>

      private void BuildTreeView()

      {

        TreeNode RootNode = new TreeNode();

        // give root node a name

        RootNode.Text = "Report Server";

        RootNode.Expanded = true;

        // add node to treeview object

        tvCatalog.Nodes.AddAt(0, RootNode);

        // populate treeview with catalog items

        GetCatalogItems(string.Empty, RootNode);       

      }

 

      /// <summary>

      /// recursivly get folder items from report

      /// server catalog; render to treeview control

      /// </summary>

      /// <param name="catalogPath"></param>

      /// <param name="parentNode"></param>

      private void GetCatalogItems(string catalogPath, TreeNode parentNode)

      {

        CatalogItem[] items;

 

        try

        {

          // if catalog path is empty, use root, if not pass the folder path

          if (catalogPath.Length == 0)

          {

            items = rs.ListChildren("/", false); // no recursion (false)

          }

          else

          {

            items = rs.ListChildren(catalogPath, false); // no recursion (false)

          }

 

          // iterate through catalog items and populate treeview control

          foreach (CatalogItem item in items)

          {

            // if folder is hidden, skip it

            if (item.Hidden != true)

            {

              // ensure only folders are rendered

              if (item.Type.Equals(ItemTypeEnum.Folder) &

                (item.Type != ItemTypeEnum.DataSource

                & item.Name != "Data Sources"))

              {

                TreeNode folderNode = new TreeNode(item.Name, null);

                folderNode.ImageUrl = GetNodeImage(item.Type);

                folderNode.Text = item.Name;

                folderNode.Value = item.Path;

                folderNode.ToolTip = item.Name;

                //Add the node to the parent node collection               

                parentNode.ChildNodes.Add(folderNode);

                // recurse            

                GetCatalogItems(item.Path, folderNode);

              }

            }

          }

        }

        catch (Exception ex)

        {

          throw (new Exception(ex.Message));

        }

        finally

        {

          rs.Dispose();

        }

      } 

6) Add the following method to handle the node image

 /// <summary>

      /// Gets corresponding node image

      /// (e.g., folder, reports,..)

      /// </summary>

      /// <param name="type"></param>

      /// <returns></returns>

      private string GetNodeImage(ItemTypeEnum type)

      {

        string imagePath = string.Empty;

 

        switch (type)

        {

          case ItemTypeEnum.Folder:

            imagePath = folderImage;

            break;

          case ItemTypeEnum.Report:

            imagePath = reportImage;

            break;

          case ItemTypeEnum.LinkedReport:

            imagePath = reportImage;

            break;

        }

 

        return imagePath;

      }
7) Add the following Constants 

const string reportImage = "~/images/report.gif";

const string folderImage = "~/images/folder.gif";

You can get the images from the download below.Optionally, you may consider using a custom user control to handle this functionality

Hope this will help you

Regards
D

Jitendra Faye replied to aman on 10-Jan-12 07:16 AM

In Visual studio 2005,there is a report veiwer contrlo to view your RDL reports.


You need to link report viewer control to corresponding report in Reporting service project.This u can do

by setting Propertis of report viewer.

You can also pass parameters form ASP page to rdl file.

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class Default2 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
this.ReportViewer2.Visible = false;
}
protected void Button1_Click(object sender, EventArgs e)
{


this.ReportViewer2.Visible = true;
this.ReportViewer2.ServerReport.ReportServerUrl = new System.Uri("http://www.sqldev.org/sql-server-reporting-services/images/emoticons/smile_regular.gif");

string strReport = "/ReportProject14/Report1";

this.ReportViewer2.ServerReport.ReportPath = strReport;

Microsoft.Reporting.WebForms.ReportParameter[] RptParameters =
new Microsoft.Reporting.WebForms.ReportParameter[3];

string strComp = this.DropDownList1.SelectedItem.Value;
RptParameters[0] =
new Microsoft.Reporting.WebForms.ReportParameter("INTCOMPANYKEY", strComp);

string strFacility = this.DropDownList3.SelectedItem.Value;
RptParameters[1] =
new Microsoft.Reporting.WebForms.ReportParameter("INTFACILITYKEY", strFacility);

string strActivity = this.DropDownList2.SelectedItem.Value;
RptParameters[2] =
new Microsoft.Reporting.WebForms.ReportParameter("Activity", strActivity);

this.ReportViewer2.ServerReport.SetParameters(RptParameters);
this.ReportViewer2.ServerReport.Refresh();
}
}

Hope this will help you.