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

• Download demo project - 165 KB

Introduction

This example uses an ASP.NET filter page to construct an XML which then is used as a parameter for a Reporting Services web service call to render the report. The XL parameter is intercepted by a custom data extension DLL which is registered under the Reporting Services web service. The custom data extension parses the XML, and 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 parses out the filters to be added to the query that executes under SQL Server and returns the result set as a dataset.

This example does not create a security extension, but it uses the built-in Windows security; therefore, the ASPX code has Windows authentication and sets 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.

Deploying the Data Extension

The data extension is deployed by adding entries to the config files. There are four config files to be modified in order to register the custom data extension. Before modifying these files, make a backup in case the modification goes wrong. Restoring the original config file will make the Reporting Service functional again.

C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer\RSReportDesigner.config:

<Data>
   <Extension Name="CUSTOM" 
      type="CustomDataExtension.DataSetConnection, 
            CustomDataExtension"/>
</Data>

C:\Program File\Microsoft SQL Server\80\Tools\Report Designer\rspreviewpolicy.config

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

C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\RSReportServer.config

<Data>
   <Extension Name="CUSTOM" 
     type="CustomDataExtension.DataSetConnection, 
           CustomDataExtension"/>
</Data>

Filter Pages

Filter pages are contracted using ASPX web pages. On page submit, the XML is constructed on the code-behind and is passed as a report parameter to the SOAP calls, which are then passed to the data extension interface. This XML is passed into the data extension as a command text. This way, the data extension can parse the XML and append the web method calls to the XML, which will then be passed to the business web service methods.

System Flow

Filter Page --(submit)--> Web Server --(XML)--> Reporting Services WS --(XML) -- > Business WS --(XML)--> Data Access Layer

The command text is the only way for the XML to be passed to the data extension. XML was chosen as the means to pass parameters because the XML can be very large and can contain a large number of elements. The Report Definition Language (RDL) file contains a section for report parameters, and the datasets section contains the command text. Manually modify the RDL file by opening it in the Visual Studio View Code option, add a report parameter named XmlCommandText, and modify the dataset section command text as follows:

Report1.rdl

<ReportParameters>
  <ReportParameter Name="XmlCommandText">
   <DataType>String</DataType>
   <Prompt>CommandText</Prompt>
  </ReportParameter>
</ReportParameters>

Note: The default XML value can be added to the above report parameters to test out the report in preview mode.

Dataset Section

<Query>
  <DataSourceName>CustomDataExtension</DataSourceName>
  <CommandText>=Parameters!XmlCommandText.Value</CommandText>
  <QueryParameters>
   <QueryParameter Name="WebMethod">
    <Value>GetCustomers</Value>
   </QueryParameter>
  </QueryParameters>
</Query>

The CommandText will receive the XML value when the data extension interface is called. The XML value is passed to the SOAP as a report parameter value.

WebForm1.aspx.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);

The XML contains only the filter parameters to be passed to the data extension. The web service method name is stored in the dataset parameter, which is discussed later.

XML example

<doc>
  <Parameters>
   <CustomerID>12345</CustomerID>
   <SalesOrderNumber>s00125</SalesOrderNumber>
  </Parameters>
</doc> 

Data Source

The data source in the report RDL is set to use the custom data extension. The 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 in the Visual Studio .NET report design view. The custom data extension is then selected from the dataset tab.

Report1.rdl

<DataSources>
  <DataSource Name="CustomDataExtension">
  <rd:DataSourceID>08b67ea6-e6c2-4b64-bcc4-650dc06c73e6</rd:DataSourceID>
  <ConnectionProperties>
   <DataProvider>CUSTOM</DataProvider>
   <ConnectString/>
  </ConnectionProperties>
  </DataSource>
</DataSources>

The Datasources section in the report RDL contains the custom data extension reference.

Data Extension

Data Extension is called for each dataset in the report RDL during report service processing. Each dataset is bound to a data source, and each dataset contains a query parameter which will be added to the data extension parameter collection implementation.

Dataset and Data Extension Working Together

The report RDL contains a dataset section that describes the data fields and the query. The dataset section may contain one or more datasets depending on the report layout. Each dataset entry has a query section with the command text entry and the 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.

Report1.rdl

<Query>
  <DataSourceName>CustomDataExtension</DataSourceName>
  <CommandText>=Parameters!XmlCommandText.Value</CommandText>
  <QueryParameters>
   <QueryParameter Name="WebMethod">
    <Value>GetCustomers</Value>
   </QueryParameter>
  </QueryParameters>
</Query>

DataSetCommand.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);
}

The Reporting Service calls the parameter collection object to add dataset query parameters during render execution. The data extension IDbCommand.ExecuteReader method loads the 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 contains the filter parameters element from the filter page. The result XML is then fed into the DataSetDataReader interface. The DataReader constructor then calls the business web service method and passes the XML to the data access layer.

I can handle the sub report XML parameter passing in the same way. Sub report XML parameters must be hard coded in the main report RDL file. For example:

Navigation Tab - Jump to report: Sales Order Detail

The RDL action section contains sub report entries with the XML parameter and the 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.

Report1.rdl

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

Data Extension

Data extension handles the XML parameter passing via command text, and also appends the dataset query parameters into the XML and the 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 passes along the XML to the web service method. The call method is then parsed from the XML parameter, and the appropriate calls is made to the access layer using the call method name.

IDataReader::DataSetDataReader

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);
  }
}

The DataSetDataReader construct calls 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 it is being passed into the business web service method. One web service call minimized changes to the data extension DLL. The XML parameter is very flexible, can contain any number of elements, and can be passed around without radical changes to the interface.

Business Web Service

At this layer, the XML can be passed to the 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 parse the XML, find out which method to call in the data access layer, and passes the XML.

BusinessData.asmx

[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;
}

The business web service method parses the XML, determines which data access method to call, and makes the call pass in the XML as a parameter. The data access method then parses the XML to retrieve the filter parameters, and executes the SQL to retrieve the result as a dataset. Here, we could choose not to parse the XML by just calling a single method in the data access layer and letting the data access layer do the parsing and execute the appropriate data retrieval method and return the dataset. The choices are up to the developer, but the key advantage in this approach is the flexibility as to where you want to process the XML and return the dataset results.

Issues

Image links must be handled 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.

To handle plain vanilla reports using this approach using SOAP calls is very trivial, but interactive reports such as the one with drill downs pose a big problem. Drill down report links are URL based which when clicked on turns the entire page into a URL style page. This is not very nice. I wish Microsoft would get their acts together and make Reporting Services more ASP.NET compatible by utilizing the post back mechanism instead of URL based for reports running under ASP.NET web pages.

One way to achieve this is by writing a rendering extension that replaces all links with a post back JavaScript function and a report viewer custom control that can handle all the post back clicks and make SOAP calls to retrieve report fragments, and at the same time, add report session cache management to the process. This would then make Reporting Services a complete ASP.NET centric product.