Render list of Data Sources and Dataset's within a Report Definition (.RDL) file, using Reporting Services Web Services

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

reporting builder 2.0

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

reporting builder 2.0

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.

reporting builder 2.0

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.

reporting builder 2.0

4) Click Add Reference button.

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

reporting builder 2.0

 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.sln.zip (430.39 kb)

Comments (8) -

Hi!

Your link to the zip file is broken. Can you update it please?

Sam

Hi Sam,

We're taking a look into it now and will get back to you shortly. Thank you for identifying the issue.

Sam, try now, it should be available.

Thanks for your patience.

design your style now 12/21/2012 6:22:06 AM

Sorry for the huge review, but I'm really loving what I learned and your visions are exceptional.

saravanakumar 12/30/2012 11:25:24 PM

the zip file is broken : the file is corrupted ....can u plz update the latest one

Hi Saravanakumar,

The file seems to be fine. We were able to download and extract on several different computers.

Other:

After extracting the .zip, you may need to convert the project to your version of Visual Studio. For example, the downloadable solution was built using VS 2008. If you're using VS 2010, you'll need to perform the conversation, so the project files open without error.

saravanakumar 12/30/2012 11:26:58 PM

rs.GetItemDataSources(reportPath);

// I am facing an system.web.services.protoclol.soap expection
path is invalid and less than 260 characters how to resolve the issue

Hi Saravanakumar,

I haven't seen this error before. I would suggest you review the following:

1) Make sure your web reference properties are configured to your environment settings - you may have our default settings for the demo. For example, the demo Web Reference URL =http://localhost/reportserver/ReportService2005.asmx?wsdl  your report server path may be different.

2) Check the web.config file and look for the <RSIntegrationDemo.Properties.Settings> element. make sure the "value" settings match your evironment.

<applicationSettings>
    <RSIntegrationDemo.Properties.Settings>
   <setting name="RSIntegrationDemo_RS2005_ReportingService2005"
    serializeAs="String">
    <value>http://localhost:80/ReportServer/ReportService2005.asmx</value>
   </setting>
  </RSIntegrationDemo.Properties.Settings>
  </applicationSettings>

3) Within the RDDataset.cs file, under Page_Load(...), make sure you use existing report paths from your environment. For example, "/Corporate Reports/Company Sales 2008" is a specific path we used for the demo, we can assume you wouldn't have the same path to folders/reports.

I hope this helps.

Pingbacks and trackbacks (1)+

Add comment