|
|
<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>
<Data>
<Extension Name="CUSTOM" type="CustomDataExtension.DataSetConnection, CustomDataExtension"/>
</Data>
</PRE>
<h4>C:\Program File\Microsoft SQL Server\80\Tools\Report Designer\rspreviewpolicy.config</h4>
<PRE lang=xml>
<CodeGroup class="UnionCodeGroup" version="1" PermissionSetName="FullTrust" Name="DataExtensionSample"Description="Code group for sample data processing extension">
<IMembershipCondition class="UrlMembershipCondition" version="1"Url="C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer\CustomDataExtension.dll"/>
</CodeGroup>
</PRE>
<h4>C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\RSReportServer.config</h4>
<PRE lang=xml>
<Data>
<Extension Name="CUSTOM" type="CustomDataExtension.DataSetConnection, CustomDataExtension"/>
</Data>
</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)--> <strong>Web Server</strong> --(xml)-->
<strong>Reporting Service WS </strong> --(xml) -- > <strong>Business WS</strong>
--(xml)--> <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>
<ReportParameters>
<ReportParameter Name="XmlCommandText">
<DataType>String</DataType>
<Prompt>CommandText</Prompt>
</ReportParameter>
</ReportParameters>
</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>
<Query>
<DataSourceName>CustomDataExtension</DataSourceName>
<CommandText>=Parameters!XmlCommandText.Value</CommandText>
<QueryParameters>
<QueryParameter Name="WebMethod">
<Value>GetCustomers</Value>
</QueryParameter>
</QueryParameters>
</Query>
</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 = "XmlCommandText";
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>
<doc>
<Parameters>
<CustomerID>12345</CustomerID>
<SalesOrderNumber>s00125</SalesOrderNumber>
</Parameters>
</doc>
</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>
<DataSources>
<DataSource Name="CustomDataExtension">
<rd:DataSourceID>08b67ea6-e6c2-4b64-bcc4-650dc06c73e6</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>CUSTOM</DataProvider>
<ConnectString/>
</ConnectionProperties>
</DataSource>
</DataSources>
</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 "WebMethod" 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>
<Query>
<DataSourceName>CustomDataExtension</DataSourceName>
<CommandText>=Parameters!XmlCommandText.Value</CommandText>
<QueryParameters>
<QueryParameter Name="WebMethod">
<Value>GetCustomers</Value>
</QueryParameter>
</QueryParameters>
</Query>
</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("Invalid Xml command Text: " + 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>"<doc><Parameters><SalesOrderNumber>" &
Fields!SalesOrderNumber.Value & "</SalesOrderNumber></Parameters></doc>"</td>
</tr>
</table>
<p>The rdl action section contains sub report entries with the xml parameter and
value. Again here the parameter name is "XmlCommandText", 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>
<Action>
<Drillthrough>
<Parameters>
<Parameter Name="XmlCommandText">
<Value>="&lt;doc&gt;&lt;Parameters&gt;&lt;SalesOrderNumber&gt;" &amp; Fields!SalesOrderNumber.Value &amp; "&lt;/SalesOrderNumber&gt;&lt;/Parameters&gt;&lt;/doc&gt;"</Value>
</Parameter>
</Parameters>
<ReportName>Sales Order Detail</ReportName>
</Drillthrough>
</Action>
</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("Error calling web service XML: " + 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.
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.
Submit an article or tip