Working with Folders and Properties programmatically in Reporting Services

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)

Pingbacks and trackbacks (1)+

Comments are closed