SQL Server Reporting Services (SSRS) - Tips & Tricks

This article describes how to render a list of data sources and dataset's from with a Report Definition (.RDL) file, while using the SQL Server Reporting Services Web Services. You will find a dowload of the code at the end of the article.

This article assumes you have the following:

• SQL Server 2008 and Reporting Services installed
• Microsoft Visual Studio 2008 installed
• Experience with Reporting Services 2005/2008 
• Experience with ASP.NET and C#
• Experience with the Reporting Services SOAP API

Steps Taken:

1. Create ASP.NET Web Application
2. Reference Reporting Services Web Services SOAP API
3. Add Web Form
4. Reference required namespaces  
5. Create code for both ListReportDataSource and ListReportDataset
6. Run code 

Create ASP.NET Web Application

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

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 "RSIntegrationDemo".

6) Click OK.

Reference Reporting Services SOAP API

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.

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.

4) Click Add Reference button.

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

 You have completed referencing the Reporting Services WSDL

 Add Web Form

1) Create a new Web Form and name it "RSDataset.aspx".

Reference Namespaces

1) Open the RDLUpload.aspx.cs and add the following additional namespaces: 

using System.Web.Services.Protocols; // protocols used to transmit data across the wire using ASP.NET

using RSIntegrationDemo.RS2005; // your wsdl reference

//used for ListReportDataset

using System.Xml;

using System.Xml.XPath;

using System.IO;

Adding the Data Source Code

The ListReportDataSource method will return all data sources within a report, by using the GetItemDataSources method, within the Reporting Services SOAP API. The custom method ListReportDataSource takes one parameter - ReportPath (string).

  /// <summary>

        /// lists all datasources within a report definition (.rdl)

        /// </summary>

        /// <param name="reportName"></param>

        private void ListReportDataSource(string reportPath)

        {

            DataSource[] dataSources = null;

 

            try

            {

                dataSources = rs.GetItemDataSources(reportPath);

 

                if (dataSources != null)

                {

                    foreach (DataSource ds in dataSources)

                    {

                        Response.Write(ds.Name + "<br />");

                    }

                }

            }

            catch (SoapException e)

            {

                Response.Write(e.Detail.InnerXml.ToString());

            }           

        }

Adding the Dataset Source Code

The ListReportDataset method will return all dataset items within a report, by using the GetReportDefinition method, within the Reporting Services SOAP API. In addition to the GetReportDefinition, we use the following objects to help with reading the stream:

• byte[] - array of bytes 
• XmlDocument - Represents an XML document
• MemoryStream - Creates a stream whose backing store is memory. In this case we use for initializing a new non-resizable instance of the MemoryStream class based on the specified byte array.
• XmlNode - Represents a single node in the XML document. References our XmlDocument object.
• XmlNodeList - Represents an ordered collection of nodes. All the elements under our Root Node (XmlNode reference and xpath).

The custom method ListReportDataset takes one parameter - ReportPath (string).

        /// <summary>

        /// List all dataset(s) names within a report definition (.rdl) file

        /// </summary>

        /// <param name="reportPath"></param>

        private void ListReportDataset(string reportPath)

        {

            byte[] reportDefinition = null;

            XmlDocument xmldoc = null;

 

            reportDefinition = rs.GetReportDefinition(reportPath);

 

            using (MemoryStream rdlFile = new MemoryStream(reportDefinition))

            {

                xmldoc = new XmlDocument();

                xmldoc.Load(rdlFile);

                rdlFile.Close();

            }

 

            XmlNode root = xmldoc.DocumentElement;

            // Get all the elements under the root node.

            XmlNodeList nodelist = root.SelectNodes("descendant::*");

 

            foreach (XmlNode node in nodelist)

            {

                if (node.Name == "DataSets") // Only search DataSets.

                {  

                    XmlNodeList childList = node.ChildNodes;

 

                    foreach (XmlNode childnode in childList)

                    {

                        if (childnode.Name == "DataSet") // Check if DataSet element exists.

                        {

                            Response.Write(childnode.Attributes["Name"].Value + "<br />");

                        }

                    }

                }

            }

        }

Run Code

1) You'll need to call the methods in your Page_Load before running your code. Enter the following code (change the report path to desired location):

  private ReportingService2005 rs;

 

        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)

            {

                // get list of data sources...

                ListReportDataSource("/Corporate Reports/Company Sales 2008");

                // get list of dataset's...

                ListReportDataset("/Corporate Reports/Company Sales 2008");

            }

        }

That's it! Think about applying these concepts while using custom user controls for best results.

Cheers!

Download the code here: RSIntegrationDemo.zip (442.21 kb)

In this article we will examine developing a method to handle stacked values within a field, while using a table or matrix. There may be multiple ways to handle stacked values approach, but we will focus on delivering the solution using a custom assembly. Code download is available at the end of the article.

For this example, we will be using Report Desinger, Visual Studio 2008 and ASP.NET/C# for sample code.

This article assumes you have the following:

• SQL Server 2008 and Reporting Services installed
• (optional) Report Builder 2.0
• Microsoft Visual Studio 2008 installed
• Reporting Services 2008 installed and a configured Report Server
• Experience with Reporting Services 2005/2008 
• Experience with ASP.NET and C#

Steps Taken:

1. Create a Custom Assembly
2. Copy assembly to ReportServer and PrivateAssemblies folders
3. Set Security Permissions in RSPreviewPolicy.config and rssrvpolicy.config 
4. Create a Report to render Vendor data
5. Reference the custom assembly
6. Add field with expression to call the method

Creating a Custom Assembly

1) Open Visual Studio 2008 and create a new Project >> Class Library. Name the project "rsCustomAssembly".

2) Delete the Class1.cs

3) Create a new Class... name it "Vendor.cs"

4) Copy and paste the following code to the file.

#region "***** Disclaimer *****"

 

/*=============================================================================

  File: Default.cs

 

  Summary:  This sample demonstrates creating a method that returns a list of

            Vendor Products by VendorID - returns a string value.

            The value can then be used in a table/matrix field to return

            stacked values, in Reporting Services.

           

---------------------------------------------------------------------

 

 THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY

 KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE

 IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A

 PARTICULAR PURPOSE.

=============================================================================*/

 

#endregion

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

 

using System.Data.SqlClient;

using System.Security.Permissions;

 

namespace rsCustomAssembly

{  

    public class Vendor

    {

        [PermissionSetAttribute(SecurityAction.Assert, Unrestricted = true)]

        public static String GetVendorProducts(int vendorID)

        {

            StringBuilder nRetVal;

 

            SqlConnection oConn = new SqlConnection();

            oConn.ConnectionString = "Data Source=(local); " +

                "Initial Catalog=AdventureWorks;Integrated Security=True";

            oConn.Open();

            SqlCommand oCmd = new SqlCommand();

            oCmd.Connection = oConn;

            oCmd.Parameters.AddWithValue("@VendorID", vendorID);

            oCmd.CommandText = "SELECT p.Name as 'CompanyName' " +

                "FROM Production.Product p " +

                "INNER JOIN Purchasing.ProductVendor pv on pv.ProductID = p.ProductID " +

                "WHERE pv.VendorID = @VendorID";

            SqlDataReader myReader;

            myReader = oCmd.ExecuteReader();

            nRetVal = new StringBuilder();

 

            try

            {

                while (myReader.Read())

                {

                    nRetVal.Append(myReader.GetString(0) + "\r\n");

                }              

               

                return nRetVal.ToString();

            }

            catch (Exception e)

            {

                return e.Message;

            }

            finally

            {

                // close reader and connection and return value

                myReader.Close();

                oConn.Close();

            }           

        }      

    }

}

5) Save your project. Don't close your IDE.

Copy assembly to ReportServer and PrivateAssemblies folders

1) Make your assembly available to Report Designer and Report Server in Reporting Services. To do this, you must copy your .dll to the Report Designer (PrivateAssemblies) folder and to the Report Server (ReportServer) folder.

Note: depending on your installation, the paths may be different.

• For Reporting Services 2008, copy the .dll to the following folders:
  Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies
  Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\bin
• For Reporting Services 2005, copy the .dll to the following folders:
  Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies
  Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin

2) Since you may be modifying your custom assembly in the future, you may consider adding the following script to your project Build Events. Place the script in the Post-Build event command line: section. This will eliminate the need to manually copy and paste the .dll every time you need to publish a new version.

copy "$(TargetPath)" "C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\"
copy "$(TargetPath)" "C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\bin\"

3) Save your changes to the project and run a build. Your .dll should be copied over the defined paths in your Post-build event command line.

Note: Sometimes you may receive an error stating the .dll file could not be copied over to the defined path. This may be due to the fact you have a report open that references the assembly, so the active assembly in ReportServer/bin or PrivateAssemblies folder is locked. You may either close the report in the design view, or simply restart your SQL Server Report Services service from the Windows Services Management Console.

Set Security Permissions in RSPreviewPolicy.config and rssrvpolicy.config 

This custom assembly will require more permissions than the default Execution level granted in Reporting Services. We need to make some security change to code access in order for this work. A common indication that your custom assembly requires special code access permissions is the "#Error" value rendered when you run your report, instead of the expected result.

Note: before making any changes to your .config files, please make a backup of the files and save them to a new location. You may need to recover the backup files if you corrupt active .config files.

RSPreviewPolicy.config

1)  Navigate to the location of your RSPreviewPolicy.config ("Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\") and open the file.

2) Scroll down the file and locate the third to the last  </CodeGroup> and paste for following script under it:

</CodeGroup>  <--- third to last                                       

<CodeGroup

class="UnionCodeGroup"

      version="1"

      PermissionSetName="FullTrust"

      Name="MyCodeGroup"

      Description="">

      <IMembershipCondition

            class="UrlMembershipCondition"

            version="1"

            Url="C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\rsCustomAssembly.dll"

/>

</CodeGroup>

</CodeGroup>

</CodeGroup>

</PolicyLevel>

3) Save your file and close it. 

RSSrvPolicy.config

1)  Navigate to the location of your RSSrvPolicy.config ("Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\bin") and open the file.

2) Scroll down the file and locate the third to the last  </CodeGroup> and paste for following script under it:

</CodeGroup>  <--- third to last                                       

<CodeGroup

class="UnionCodeGroup"

      version="1"

      PermissionSetName="FullTrust"

      Name="MyCodeGroup"

      Description="">

      <IMembershipCondition

            class="UrlMembershipCondition"

            version="1"

            Url="C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\bin\rsCustomAssembly.dll"

/>

</CodeGroup>

</CodeGroup>

</CodeGroup>

</PolicyLevel>

3) Save your file and close it.

Create a Report to render Vendor data

1) Open Business Intelligence Development Studio (BIDS) - Start >> All Programs >> Microsoft SQL Server 2008 >> SQL Server Business Intelligence Development Studio

2) Create a new Report Server Project - File >> New >> Project >> Project Type: Business Intelligence Projects >> Templates: Report Server Projects 

3) Name the Project "DemoReportProject" and click OK.

4) From within the Report Data pane, add a new data source. Use the AdventureWorks database and name the data source "AdventureWorks". If you do not have the AdventureWorks database you can get it here.

5) From within the Report Data pane, add a new Dataset.

6) Name the Dataset "VendorDS".

7) Select the AdventureWorks database for the Data Source.

8) Select the Text option for the Query Type. 

9) Enter the follow script in the Query textbox:

SELECT        VendorID, Name, FirstName, LastName, Phone
FROM            Purchasing.vVendor

10) Click OK.

11) Add a Matrix to your design surface. 

12) Drag and drop both Name and Phone, from the VendorDS dataset, to the Rows section of the Matrix. Add a Textbox for the title of the report. Your report should similar to the following:

13) Click on the Company Name column so that the column has the focus.

14) Right click on the column and select Insert Column >> Inside Group Right

15) Name the column "Products".

16) Save your work.

Reference the custom assembly

1) Make sure you are in the Design surface of Reporting Services. From the menubar, select Report >> Report Properties. This will open the Report Properties dialog.

2) From the left pane of the Report Properties dialog, select References.

3) Click Add from under the Add or remove assemblies section.

4) Click the Browse tab and navigate to your ReportServer/bin location. Select the rsCustomAssembly.dll and click OK.

Your Report Properties dialog should look similar

5) Click OK.

Add field with expression to call the method

1) Under the Products column, right click the Textbox and select Expressions... 

2) Copy and paste the  following code in the Value editor.

=rsCustomAssembly.Vendor.GetVendorProducts(Fields!VendorID.Value)

Notice how the call is made to the method: <Namespace>.<Class>.<Method>

Note: If you want to use instance-based methods, you will have go back to the Report Properties >> References tab, and then define the Class name and Instance name in Add or remove classes section.

3) Click OK.

Your Matrix should look something similar 

4) Preview the report by selecting the Preview tab. Your report should render similar results.

5) Save your work.

You're done!

Cheers!

Download code here:

rsCustomAssembly.zip (27.68 kb)

DemoReportProject1.zip (32.67 kb)

This post will demonstrates how to upload an .RDL file to the Reporting Services database. We will begin with creating the method to upload the file and save it to a temporary folder. Next, we then consume the web service method CreateReport to save the .RDL file to the Reporting Services database.

This article assumes you have the following:

• SQL Server 2008 and Reporting Services installed
• Microsoft Visual Studio 2008 installed
• Reporting Services 2008 installed and a configured Report Server
• Experience with Reporting Services 2005/2008 
• Experience with ASP.NET and C#
• Exposure to Web Services

Add Web Form

1) Create a new Web Form and name it "RDLUpload.aspx".

2) Open the RDLUpload.aspx.cs and add the following namespaces:

using System.IO;

using RSIntegrationDemo.RS2005;

using System.Web.Services.Protocols;

 

File Upload 

This section we use the System.Web.UI.WebControls.FileUpload control to upload a file to the temp folder. Next, we pass the string path of the .RDL file saved to the UploadReport method.

            // Specify the path on the server to

            // save the uploaded file to.

            String savePath = HttpContext.Current.Server.MapPath(@"~/Temp/");

 

            // Before attempting to perform operations

            // on the file, verify that the FileUpload

            // control contains a file.

            if (FileUpload1.HasFile)

            {

                // Get the name of the file to upload.

                String fileName = FileUpload1.FileName;

 

                // Append the name of the file to upload to the path.

                savePath += fileName;

 

                // Call the SaveAs method to save the

                // uploaded file to the specified path.

                // This example does not perform all

                // the necessary error checking.              

                // If a file with the same name

                // already exists in the specified path, 

                // the uploaded file overwrites it.

                FileUpload1.SaveAs(savePath);

 

                // Notify the user of the name of the file

                // was saved under.

                string reportExt = fileName.Substring(fileName.Length - 3, 3);

                try

                {

                    // check to ensure the file is type of .rdl

                    if (reportExt == "rdl")

                    {

                        UploadReport(txtReportName.Text, savePath, txtFolderPath.Text, chkOverwrite.Checked);

 

                        lblMessage.Visible = true;

                        lblMessage.Text = "Your file has uploaded successfully.";

                    }

                    else

                    {

                        lblMessage.Text = "Only files extentions of .rdl are allowed.";

                    }

                }

                catch (Exception ex)

                {

                    lblMessage.Text = ex.Message;

                }

            }

            else

            {

                // Notify the user that a file was not uploaded.

                lblMessage.Text = "You did not specify a file to upload.";

            }

Upload Report to Reporting Services database

The method below demonstrates the use of the ReportingService2005.CreateReport for save .RDL files to the Reporting Services database. The CreateReport method requires the following Parameters:

Report
        The name of the new report.
Parent
        The full path name of the parent folder to which to add the report.
Overwrite
        A Boolean expression that indicates whether an existing report with the same name in the location specified should be overwritten.
Definition
        The report definition to publish to the report server.
Properties
         An array of Property[] objects that contains the property names and values to set for the report.

The Definition is your code in the .rdl file and it's a required type of byte[], so we need to use a FileStream object to read the .rdl file - we need to reference the System.IO namespace. After the file is read we can pass the stream to the CreateReport method and save the report to the database. The code below show's how to do this.

        public string UploadReport(string fileName, string filePath, string folderPath, bool overwrite)

        {

            rs = new ReportingService2005();

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

 

            Byte[] definition = null;

            Warning[] warnings = null;

 

            try

            {

                FileStream stream = File.OpenRead(filePath);

                definition = new Byte[stream.Length];

                stream.Read(definition, 0, (int)stream.Length);

                stream.Close();

            }

 

            catch (IOException e)

            {

                throw new Exception(e.Message);

            }

 

            try

            {

                warnings = rs.CreateReport(fileName, folderPath, overwrite, definition, null);

 

                if (warnings != null)

                {

                    foreach (Warning warning in warnings)

                    {

                        Console.WriteLine(warning.Message);

                    }

                }

            }

 

            catch (SoapException e)

            {

                return e.Detail.InnerXml.ToString();

            }

 

            return String.Format("Report: {0} created successfully with no warnings", fileName);

        }

 The code is provided as a concept to approaching Reporting Services Web Application Integration and is not recommeded for production use.

Optionally, you may consider using a custom user control to handle this functionality.

Cheers!

Download the code here: RSIntegrationDemo.zip (442.21 kb)

In this article we will describe how to programmatically add a new Folder and create Properties for the folder, using Reporting Services Web Services. For this example, we will be using the ListChildren, CreateFolder and GetProperties methods. The article also show's how to render a list of Reporting Services catalog items (e.g., reports, data sources, folders, etc) by searching the Property Name and Value. This could be helpful if you plan to group information using Reporting Services and want a way to narrow your search for catalog items.

Note:The use example for the FindItemsByPropertyValue method below may be applied to reports, or any other Reporting Services object that allows for custom properties - for this demo, we will only search against folders.

The ReportingService2005.CreateFolder method adds a new folder the Reporting Services database. Optionally, you can add an array of Property objects to the newly created folder to hold additional information. For example, you may want to create a new folder name KPI's and add a property named Department, with a value of "Sales and Marketing". The really cool thing about creating a custom property value is, it allows you to create metadata to help with categorizing your information. You can then run a search for all named properties of "Department" with a value of "Sales and Marketing".

This article assumes you have the following:

• SQL Server 2008 and Reporting Services installed
• Microsoft Visual Studio 2008 installed
• Reporting Services 2008 installed and a configured Report Server
• Experience with Reporting Services 2005/2008 
• Experience with ASP.NET and C#
• Exposure to Web Services

You may download the code below to follow along with article. 

Adding Namespaces

1) Create a new Web Form and name it "CreateFolder.aspx".

2) Open the CreateFolder.aspx.cs and add the following namespaces:

Creating a Folder and Properties

using System.Web.Services.Protocols; /* used for the SoapException */

using RSIntegrationDemo.RS2005; /* this is what I named the reference to the Reporting Services Web Services. */

using System.Data; /* used for our datatable and datarow */

using System.Drawing; /* used for Color object */

 

 1) Add the following method to your CreateFolder.aspx.cs file.

        /// <summary>

        /// Adds a new folder to the Reporting Services database

        /// </summary>

        /// <param name="parentFolder"></param>

        /// <param name="folderName"></param>

        /// <param name="props"></param>

        /// <returns></returns>

        public bool CreateNewFolder(string parentFolder, string folderName, Property[] props)

        {

            rs = new ReportingService2005();

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

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

 

            try

            {

                rs.CreateFolder(folderName, parentFolder, props);

                Label2.Text = "Success";

                Label2.ForeColor = Color.Blue;

                return true;

            }

 

            catch (SoapException e)

            {

                Label2.Text = "Failed";

                Label2.ForeColor = Color.Red;

                throw (new Exception(e.Detail.InnerXml.ToString()));               

            }

        }

 2) Next, add the following event that calls the method. Essentially, we're just creating an araay of property values and passing that to the CreateNewFolder method.

        protected void btnApply_Click(object sender, EventArgs e)

        {

            // create properties for folder

            Property[] props = new Property[2];

            Property desc = new Property();

            desc.Name = "Description";

            desc.Value = txtDescription.Text;

            props[0] = desc;

            Property department = new Property();           

            department.Name = "Department";

            department.Value = cmbDepartment.SelectedValue;

            props[1] = department;

 

            // add new folder and related properties

            // you can replace '/' with whatever root path you desire

            CreateNewFolder("/", txtFolderName.Text, props);

        }

Search Property Items

The method below traverses though catalog items, from within Reporting Services database, and render only those values that meet the Property Search criteria. We first start off by listing all children from the root (i.e., ListChildren("/", true)). Next, for each item found in the catalog, we pass the path to the item to the GetProperties method to get available properties. If the catalog item has a property, we then compare if the Keyword pass matches the propertis within the catalog item. You will notice the ItemTypeEnum is set to only search type of Folder.

        /// <summary>

        /// Search items by keyword

        /// </summary>

        /// <param name="propertyName"></param>

        /// <param name="keyword"></param>

        /// <returns></returns>

        public DataTable FindItemsByPropertyValue(string propertyName, string keyword)

        {

            rs = new ReportingService2005();

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

 

            DataTable dt = new DataTable();

            DataRow dr;

 

            // add column names for dataview

            dt.Columns.Add(new DataColumn("ReportPath", typeof(string)));

            dt.Columns.Add(new DataColumn("ReportName", typeof(string)));

            dt.Columns.Add(new DataColumn("Description", typeof(string)));

 

            // Create the property to retrieve.

            Property retrieveProp = new Property();

            retrieveProp.Name = propertyName;

            Property[] props = new Property[1];

            props[0] = retrieveProp;

 

            CatalogItem[] items;

 

            try

            {

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

                if (keyword.Length != 0)

                {

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

 

                    // iterate through catalog items and populate datatable

                    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))

                            {

                                Property[] properties = rs.GetProperties(item.Path, props);

                                // check to see if the property value matches the keyword

                                foreach (Property prop in properties)

                                {

                                    if (prop.Value == keyword)

                                    {

                                        dr = dt.NewRow();

                                        dr[0] = item.Path;

                                        dr[1] = item.Name;

                                        dr[2] = item.Description;

                                        dt.Rows.Add(dr);

                                    }

                                }

                            }

                        }

                    }

                }

            }

            catch(Exception e)

            {

                throw (new Exception(e.Message));

            }

 

            return dt;

        }

The code is provided as a concept to approaching Reporting Services Web Application Integration and is not recommeded for production use.

Optionally, you may consider using a custom user control to handle this functionality.

Cheers!

Download the code here:

RSIntegrationDemo.zip (442.21 kb)

This article will demo the use of Reporting Services SOAP API to integrate with a web application. We will begin by creating a new Web Project in VS 2008 and configuring the web.config so we can authenticate using impersonation. Next, we will reference the ReportingService2005 web services proxy object. Finally, enter some code to render a list of reports from within the Report Catalog.

For this example, we will be using Visual Studio 2008 and ASP.NET/C# for sample code.

This article assumes you have the following:

• SQL Server 2008 and Reporting Services installed
• Microsoft Visual Studio 2008 installed
• Reporting Services 2008 installed and a configured Report Server
• Experience with Reporting Services 2005/2008 
• Experience with ASP.NET and C#
• Exposure to Web Services

Steps Taken:

1. Create a ASP.NET Web Application project
2. Reference the Reporting Services WSDL
3. Add Treeview control to web form
4. Develop code to consume Reporting Services Web Service

Create new ASP.NET Web Application

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

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 "RSIntegrationDemo".

6) Click OK.

Reference Reporting Services 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.

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.

4) Click Add Reference button.

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

 You have completed referencing the Reporting Services WSDL

Adding a TreeView 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.

Consuming the Reporting Services 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.RS2005;

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

 private ReportingService2005 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 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: 

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

Download code here:

RSIntegrationDemo.zip (442.21 kb)