business intelligence consulting
 

Display TreeView list of Reports, Datasets and Report Parts using Report Server Web Service EndPoint (ReportService2010)

by Hans Esquivel 18. January 2013 09:42

Recently, I've had a few requests for how to display a list of Report Parts from SQL Server Reporting Services Report Server Web Services. This article will demonstrate how to query a list of Reports, Datasets and Report Parts using Report Server Web Service EndPoints. For this session we'll be using the ReportingServices2010 EndPoint.

What are Report Server Web Service EndPoints?

Report Server Web Services EndPoints provide methods for developers to manage a report server, execute and navigate reports through managed code.

What we'll cover in this session:

  • Create an ASP.NET Web Application project
  • Add a Web Reference to ReportingServices2010
  • Add a Web Form and include a TreeView component
  • Develop code to return list of Report Server Catalog Items (Reports, Datasets and Report Parts)
  • Test code

This article assumes you have the following:

  • Installed SQL Server 2012 or 2008 R2, with Reporting Services.
  • Installed Visual Studio 2010 or greater
  • Experience with C#/ASP.NET and Web Services developement
  • Experience with authoring reports using: SQL Server Data Tools (SSDT) or Business Intelligence Development Studio (BIDS). Note: for the session we'll be using BIDS. 

Note: A download of the demo is available at the end of this session.

Let's get started!

 

Step 1- Create ASP.NET Web Application

1)  Open Microsoft Visual Studio 2010 from Start >> All Programs >> Microsoft Visual Studio 2010 >> Microsoft Visual Studio 2010

2) From the menubar, select File >> New >> Project...

3) Expand the C# node and select the Web child node.

4) From the template pane, select ASP.NET Web Application.

5) Name the Project "RSWSDemo" (without quotes).

6) Click OK.

 

Step 2 - Add a Web Reference to ReportingServices2010

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

2) The Add Services Rerference window renders. Click Advanced... from the lower left corner.

3) The Service Reference Settings window renders. Click Add Web Reference... button from the lower left corner.

4) Enter the following value for the URL: http://localhost/ReportServer/ReportService2010.asmx?wsdl (replace localhost as necessary).

5) Name the Web Reference "RS2010" and click Add Reference button to create the web reference.

6) Click OK for the next two windows.

Note: you should see RS2010 under the Web References folder.

 

Step 3 - Add a Web Form and include a TreeView component


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.

reporting builder 2.0

8) Select Auto Format...

9) From the Select a scheme:, select "XP File Explorer" and then click OK.

10) Save your work.

 

Step 4 - Consuming the ReportingServices2010 web 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.RS2010;

3) Add the following code above Page_Load(object sender, EventArgs e)

private ReportingService2010 rs;

protected void Page_Load(object sender, EventArgs e)

{

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.

protected void Page_Load(object sender, EventArgs e)

{

rs = new ReportingService2010();

// 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:

///<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 folde 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.TypeName != "DataSource"
                            & item.Name != "Data Sources" & item.TypeName != "Model"
                            & item.Name != "Models"))
                        {
                            TreeNode folderNode = new TreeNode(item.Name, null);
                            folderNode.ImageUrl = GetNodeImage(item.TypeName);
                            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
                            if (item.TypeName == "Folder")
                                GetCatalogItems(item.Path, folderNode);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw (new Exception(ex.Message));
            }
        }

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>
privatestring GetNodeImage(string type)
{
string imagePath = string.Empty;
 switch (type) 

  {

case"Folder":
imagePath = folderImage;
break;
case"Report":
imagePath = reportImage;
break;
case"LinkedReport":
imagePath = reportImage;
break;
case"DataSet":
imagePath = datasetImage;
break;
case"Component":
imagePath = reportPartImage;
break;
}
return imagePath;

}

 }

7) Add the following Constants  

const string reportImage = "~/images/report.gif";
const string folderImage = "~/images/folder.gif";
const string datasetImage = "~/images/32dataset.gif";
const string reportPartImage = "~/images/32component.gif";

 

Step 5 - Test the Code

1) From the Menur Bar, select Debug >> Start Debugging or press F5 on your keyboard.

You should see the following after the page renders.

You have completed consuming Reporting Services Web Services to list Report Catalog Items - Reports, Datasets and Report Parts. Think about creating custom enums for the CatalogItems Types, so you don't have to pass the string values. Also, another potential idea is creating a custom add-in for Visual Studio 2010, which allows you to search for existing Report Parts, like in Report Builder 3.0 designer.

Download:  RS Web Services 2010 Demo

 

Cheers!

 

Recently, I&#39;ve had a few requests for how to display a TreeView list of Report Parts from SQL Server Reporting Services Report Server Web Services. This article will demonstrate how to query a list of Reports, Datasets and Report Parts using Report Server Web Service EndPoints. For this session we&#39;ll be using the ReportingServices2010 EndPoint.

C#/ASP.NET | SQL Server | Reporting Services

About Us

InfoToad Consulting provides Data Management and Business Intelligence Solutions and Services. If you would like to learn more about our services or solutons, please visit us at http://www.infotoad.com or call us at (877) 488-0566.

Month List

Tag cloud