Click here to Skip to main content
Click here to Skip to main content
Add your own
alternative version

A reporting service using SOAP calls passing XML to a Data Extension

, 19 Oct 2005 CPOL
Demostrates how to render a report by passing XML to a data extension via SOAP calls.
RSDataExtensionXml_demo.zip
RSTestApp
CustomDataExtension
CustomDataExtension.csproj.user
Web References
BusinessService
BusinessData.disco
BusinessData.wsdl
Reference.map
Global.asax
RsReportViewer
AwReportViewer.suo
Design
vssver.scc
RsReportViewer.csproj.user
Web References
RS
Reference.map
ReportService.wsdl
RSTestApp.csproj.webinfo
RSTestReports
Northwind.rds
Northwind.udl
Report1.rdl
Report1.rdl.data
RSTest.rptproj.user
RSTestReports.rptproj
RSTestReports.rptproj.user
Sales Order Detail.rdl
Sales Order Detail.rdl.data
Territory Sales Drilldown.rdl
Territory Sales Drilldown.rdl.data
RSTestWS
Global.asax
RSTestWS.csproj.webinfo
temp
Web References
ReportServer
Reference.map
ReportService.wsdl
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link rel="stylesheet" href="../global.css" type="text/css">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<h1><font color="#0000FF">Microsoft Reporting Services using web service calls 
  <br>
  and passing xml parameter to Custom Data Extension</font></h1>
<h3>By Ken C. Len</h3>
<h2>Introduction</h2>
<p> This example uses an ASP.Net filter page to construct an xml which then is 
  used as a parameter passed into reporting service web service call to render 
  the report. The xml parameter is intercepted by a custom data extension DLL 
  which is registered under reporting web service. The custom data extension parses 
  the xml decides which business component web service method to call that will 
  get the dataset to be returned for the report processing. The business component 
  web service acts as the data access layer (DAL) which also accepts the xml as 
  the parameter and parsed out the filters to be added to the query that executes 
  under SQL Server that returns a result set as dataset.</p>
<p>This example does not create a security extension but it uses the built in 
  Windows security therefore the aspx code has Windows authentication and set 
  the thread identity to impersonate. The custom data extension will be used as 
  the data source for the report definition language both in design time and run 
  time.</p>
<h2>Deploying Data Extension</h2>
<p> Data extension is deployed by adding entries to the config files. There are 
  4 config files to modified in order to register the custom data extension. Before 
  modifying these files, make a backup in case the modification went wrong. Restoring 
  original config files will make Reporting Service functional again.</p>
<h4>C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer\RSReportDesigner.config</h4>
<PRE lang=xml>
	&lt;Data&gt;
		&nbsp;&lt;Extension Name=&quot;CUSTOM&quot; type=&quot;CustomDataExtension.DataSetConnection, CustomDataExtension&quot;/&gt;
	&lt;/Data&gt;
</PRE>
<h4>C:\Program File\Microsoft SQL Server\80\Tools\Report Designer\rspreviewpolicy.config</h4>
<PRE lang=xml>
	&lt;CodeGroup class=&quot;UnionCodeGroup&quot; version=&quot;1&quot; PermissionSetName=&quot;FullTrust&quot; Name=&quot;DataExtensionSample&quot;Description=&quot;Code group for sample data processing extension&quot;&gt;
		&lt;IMembershipCondition class=&quot;UrlMembershipCondition&quot; version=&quot;1&quot;Url=&quot;C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer\CustomDataExtension.dll&quot;/&gt;
	&lt;/CodeGroup&gt;
</PRE>
<h4>C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\RSReportServer.config</h4>
<PRE lang=xml>
	&lt;Data&gt;
		&nbsp;&lt;Extension Name=&quot;CUSTOM&quot; type=&quot;CustomDataExtension.DataSetConnection, CustomDataExtension&quot;/&gt;
	&lt;/Data&gt;
</PRE>
<h2>Filter Pages</h2>
<p>Filter pages are contructed using aspx web pages. On page submit, xml constructed 
  on the code behind and is passed as report parameter to the soap calls which 
  then passed to the data extension interface. This Xml is passed into the data 
  extension as command text. This way the data extension can parse the xml and 
  appends the web method calls to the xml which will then passed to the business 
  web service methods.</p>
<h2>System Flow</h2>
<p><strong>Filter Page</strong> --(submit)--&gt; <strong>Web Server</strong> --(xml)--&gt; 
  <strong>Reporting Service WS </strong> --(xml) -- &gt; <strong>Business WS</strong> 
  --(xml)--&gt; <strong>Data Access layer</strong><br>
  <br>
  Command text is the only way for the xml to be passed to the data extension. 
  Xml was chosen as the mean to pass parameter is because xml can be very large 
  and contains large number of elements. Report Definition Language (RDL) contains 
  setion for report parameter and datasets section contain command text.<br>
  Manually modify the RDL file by opening it in Visual Studio view code option, 
  adding a report parameter named XmlCommandText and modify dataset section command 
  text as follows.</p>
<h4>Report1.rdl</h4>
<PRE lang=xml>
	&lt;ReportParameters&gt;
		&lt;ReportParameter Name=&quot;XmlCommandText&quot;&gt;
			&lt;DataType&gt;String&lt;/DataType&gt;
			&lt;Prompt&gt;CommandText&lt;/Prompt&gt;
		&lt;/ReportParameter&gt;
	&lt;/ReportParameters&gt;
</PRE>
<p><b>Note:</b> Default xml value can be added to the above report parameters 
  to test out the report in preview mode.</p>
<h2>Dataset Section</h2>
<PRE lang=xml>
	&lt;Query&gt;
		&lt;DataSourceName&gt;CustomDataExtension&lt;/DataSourceName&gt;
		&lt;CommandText&gt;=Parameters!XmlCommandText.Value&lt;/CommandText&gt;
		&lt;QueryParameters&gt;
			&lt;QueryParameter Name=&quot;WebMethod&quot;&gt;
				&lt;Value&gt;GetCustomers&lt;/Value&gt;
			&lt;/QueryParameter&gt;
		&lt;/QueryParameters&gt;
	&lt;/Query&gt;
</PRE>
<p> The CommandText will receives the xml value when the data extension interface 
  is called. The xml value is passed to the soap as report parameter value.</p>
<h4>WebForm1.aspx.cs</h4>
<PRE lang=cs>
	ReportServer.ReportingService rs = new ReportServer.ReportingService();
	rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
	... 
	ParameterValue[] parameters = new ParameterValue[1];
	parameters[0] = new ParameterValue();
	parameters[0].Name = &quot;XmlCommandText&quot;;
	parameters[0].Value = doc.InnerXml;
	... 
	result = rs.Render("/RSTest/Report1", "HTML4.0", historyID, devInfo, parameters, credentials,
			 showHideToggle, out encoding, out mimeType, out reportHistoryParameters, out warnings, out streamIDs);
	Response.BinaryWrite(result);
</PRE>
<p>The xml contains only the filter parameters to be passed to the data extension. 
  Web service method name is stored in the dataset parameter which is dicussed 
  later. 
<p> 
<h4>xml example:</h4>
<PRE lang=xml>
	&lt;doc&gt;
		&lt;Parameters&gt;
			&lt;CustomerID&gt;12345&lt;/CustomerID&gt;
			&lt;SalesOrderNumber&gt;s00125&lt;/SalesOrderNumber&gt;
		&lt;/Parameters&gt;
	&lt;/doc&gt;
</PRE>
<h2>Data Source</h2>
<p>Data source in report rdl is set to use the custom data extension. Custom data 
  extension is registered both in the report designer config file and the report 
  server config file. Once the custom data extension is registered in the designer 
  it will show up the Visual Studio .Net report design view. The custom data extension 
  is then selected from the dataset tab.</p>
<h4>Report1.rdl</h4>
<PRE lang=xml>
	&lt;DataSources&gt;
		&lt;DataSource Name=&quot;CustomDataExtension&quot;&gt;
		&lt;rd:DataSourceID&gt;08b67ea6-e6c2-4b64-bcc4-650dc06c73e6&lt;/rd:DataSourceID&gt;
		&lt;ConnectionProperties&gt;
			&lt;DataProvider&gt;CUSTOM&lt;/DataProvider&gt;
			&lt;ConnectString/&gt;
		&lt;/ConnectionProperties&gt;
		&lt;/DataSource&gt;
	&lt;/DataSources&gt;
</PRE>
<p>Datasources section in the report rdl contains the custom data extension reference.</p>
<h2>Data Extension</h2>
<p>Data Extension is called for each dataset in the report rdl during report service 
  processing. Each dataset is bind to a data source and each dataset contains 
  query parameter which will be added to the data extension parameter collection 
  imeplementation.</p>
<h2>Dataset and Data Extension working together</h2>
<p>Report rdl contains dataset section that describes data fields and query. Dataset 
  section may contain one or more datasets depending on the report layout. Each 
  dataset entry has query section with command text entry and query parameters. 
  In the example, I mapped each dataset to a web service method call which during 
  runtime would execute a business data access layer to retrieve the dataset. 
  I added a query parameter named &quot;WebMethod&quot; with the method name as 
  the parameter value. This parameter is appended to the command text xml in the 
  data extension. </p>
<h4>Report1.rdl</h4>
<PRE lang=xml>
	&lt;Query&gt;
		&lt;DataSourceName&gt;CustomDataExtension&lt;/DataSourceName&gt;
		&lt;CommandText&gt;=Parameters!XmlCommandText.Value&lt;/CommandText&gt;
		&lt;QueryParameters&gt;
			&lt;QueryParameter Name=&quot;WebMethod&quot;&gt;
				&lt;Value&gt;GetCustomers&lt;/Value&gt;
			&lt;/QueryParameter&gt;
		&lt;/QueryParameters&gt;
	&lt;/Query&gt;
</PRE>
<h4>DataSetCommand.cs</h4>
<PRE lang=cs>
	public IDataReader ExecuteReader(CommandBehavior behavior)
	{
		XmlDocument doc = new XmlDocument();
		try
		{
			// load the CommandText XML and append dataset parameters
			doc.LoadXml(CommandText);
			// Append all the dataset parameter
			// example: WebMethod contains which web service method to execute.
			DataSetParameter parameter = null;
			IEnumerator enumerator = _parameters.GetEnumerator();
			while (enumerator.MoveNext())
			{
				parameter = (DataSetParameter) enumerator.Current;
				XmlElement elem = doc.CreateElement(parameter.ParameterName);
				elem.AppendChild(doc.CreateTextNode(parameter.Value.ToString()));
				doc.DocumentElement.AppendChild(elem);
			}
		}
		catch (Exception ex)
		{
			throw new Exception(&quot;Invalid Xml command Text: &quot; + CommandText, ex);
		}
		return new DataSetDataReader(doc.InnerXml);
	}
</PRE>
<p>Reporting Service calls parameter collection object to add dataset query parameters 
  during render execution. Data extension IDbCommand.ExecuteReader method loads 
  CommandText value as xml, iterates through all the query parameters and appends 
  the name and value pair as xml elements into the main xml. The main xml contain 
  the filter parameters element from the filter page. The result xml is then fed 
  into the DataSetDataReader interface. The DataReader constructor then call the 
  business web service method and pass alone the xml to the data access layer.</p>
<p>I can handle sub report xml parameter passing the same way. Sub report xml 
  parameter must be hard coded in the main report rdl file. For example:</p>
<h4> Navigation Tab - Jump to report: <strong>Sales Order Detail</strong> </h4>
<table border="1">
  <tr bgcolor="#c0c0c0"> 
    <td>Parameter Name</td>
    <td>Parameter Value</td>
  </tr>
  <tr> 
    <td>XmlCommandText</td>
    <td>&quot;&lt;doc&gt;&lt;Parameters&gt;&lt;SalesOrderNumber&gt;&quot; &amp; 
      Fields!SalesOrderNumber.Value &amp; &quot;&lt;/SalesOrderNumber&gt;&lt;/Parameters&gt;&lt;/doc&gt;&quot;</td>
  </tr>
</table>
<p>The rdl action section contains sub report entries with the xml parameter and 
  value. Again here the parameter name is &quot;XmlCommandText&quot;, this report 
  parameter is also created in the sub report rdl. Throughout this whole process 
  xml is the metadata that is passed all around.</p>
<h4> Report1.rdl</h4>
<PRE lang=xml>
	&lt;Action&gt;
		&lt;Drillthrough&gt;
			&lt;Parameters&gt;
				&lt;Parameter Name=&quot;XmlCommandText&quot;&gt;
				&lt;Value&gt;=&quot;&amp;lt;doc&amp;gt;&amp;lt;Parameters&amp;gt;&amp;lt;SalesOrderNumber&amp;gt;&quot; &amp;amp; Fields!SalesOrderNumber.Value &amp;amp; &quot;&amp;lt;/SalesOrderNumber&amp;gt;&amp;lt;/Parameters&amp;gt;&amp;lt;/doc&amp;gt;&quot;&lt;/Value&gt;
				&lt;/Parameter&gt;
			&lt;/Parameters&gt;
			&lt;ReportName&gt;Sales Order Detail&lt;/ReportName&gt;
		&lt;/Drillthrough&gt;
	&lt;/Action&gt;
</PRE>
<h2>Data Extension</h2>
<p>Data extension handles the xml parameter passing via command text, and also 
  appends dataset query parameters into the xml and calling business web service 
  methods. The Xml is passed to the business web service call. This data extension 
  prototype calls only a single web service method and pass along the xml to the 
  web service method. The call method is then parsed from xml parameter and the 
  appropriate calls is made to the access layer using the call method name.</p>
<h4>IDataReader::DataSetDataReader</h4>
<PRE lang=cs>
	public DataSetDataReader(string sXml)
	{
		try
		{
			BusinessService.BusinessData BusWS = new BusinessService.BusinessData();
			this._dataSet = BusWS.GetReportDataSet(sXml);
			//set the current row to one before the first
			_currentRow = -1;
		}
		catch(Exception ex)
		{
			throw new Exception(&quot;Error calling web service XML: &quot; + sXml, ex);
		}
	}
	</PRE>
<p>DataSetDataReader construct call the business web service method to get the 
  dataset. The dataset is generated by the access layer in the business web service 
  method. The xml is the same xml passed from the filter page to the reporting 
  service into the data extension interface and now its being passed into the 
  business web service method. One web service call minimized changes to the data 
  extension dll. Xml parameter is very flexible, can contain any number of elements, 
  can be passed around without radical changes to the interface.</p>
<h2>Business Web Service</h2>
<p>At this layer the xml can be passed to data access layer to be processed and 
  the the data access layer can pass the xml directly to the stored procedures. 
  My implementation here is simple, I parsed the xml, find out which method to 
  call in the data access layer and pass the xml.</p>
<h4>BusinessData.asmx</h4>
<PRE lang=cs>
	[WebMethod]
	public DataSet GetReportDataSet(string sXml)
	{
		// parse xml
		// call business component based on method defined in xml
		DataSet ds = null;
		try
		{
			string sWebMethod = string.Empty;
			XmlDocument doc = new XmlDocument();
			doc.LoadXml(sXml);
			sWebMethod = doc.SelectSingleNode("//WebMethod").InnerText;
			BusinessDataAccess DataAccess = new BusinessDataAccess();
			switch (sWebMethod)
			{
				case "GetCustomers":
					ds = DataAccess.GetCustomers(sXml);
					break;
				case "GetTerritorySales":
					ds = DataAccess.GetTerritorySales(sXml);
					break;
				case "GetSalesOrder":
					ds = DataAccess.GetSalesOrder(sXml);
					break;
				case "GetSalesOrderDetail":
					ds = DataAccess.GetSalesOrderDetail(sXml);
					break;
			}
		}
		catch
		{
		}
		return ds;
	}
</PRE>
<p> Business web service method is parsing the xml, determine which data access 
  method to call and make the call pass in the xml as parameter. Data access method 
  then parse the xml to retrieve filter parameters and execute the SQL to retrieve 
  the result as dataset. Here we could choose not to parse the xml by just calling 
  one single method in the data access layer and let the data access layer do 
  the parsing and executes the appropriate data retrival method and return the 
  dataset. The choices are up to the developer, but key advantage in this approach 
  is the flexibilty as to where you want to process the xml and return the dataset 
  results.</p>
<h2>Issues</h2>
<p> Images links must be handle separately for soap calls. Images links are broken 
  when using soap api calls. The custom report viewer handles images by downloading 
  the streams to local folders. </p>
<p>To handle pain vanilla reports using this approach using soap calls is very 
  trivial but interactive reports such as the one with drill down poses a big 
  problem. Drill down reports links are URL base when clicked on it turns the 
  entire page into URL style page. This is not very nice. I wish Microsoft would 
  have get their acts together and make reporting service more ASP.Net compatible 
  by utilizing post back mechanism instead of URL based for reports running under 
  ASP.Net web pages.</p>
<p>One way to achieve this is by writing a rendering extension that replaces all 
  links with post back javascript function and a report viewer custom control 
  that can handle all post back clicks and making soap calls to retrieve report 
  fragments and at the same time add report session cache management to the process. 
  This would then make Reporting Service a complete ASP.Net centric product.</p>
</body>
</html>

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Ken C. Len
Software Developer (Senior) LEN Associates Inc.
United States United States
Years of software consulting and software development using Microsoft development products such as Microsoft Content Management System, SQL Server Reporting Service, ASP.Net C# VB.Net, HTML and javascript web development, Visual Studio add-on development, C++ MFC/ATL and COM+ development, and ActiveX components.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.1411019.1 | Last Updated 19 Oct 2005
Article Copyright 2005 by Ken C. Len
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid