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:
- Create ASP.NET Web Application
- Reference Reporting Services Web Services SOAP API
- Add Web Form
- Reference required namespaces
- Create code for both ListReportDataSource and ListReportDataset
- 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)