ince its first release, Microsoft SQL Server Reporting Services (SSRS) has supported a plethora of data sources. Out of the box, its data modules, called data extensions, allow you to build reports from SQL Server and Oracle database, as well as any other database that comes with an OLE DB or ODBC provider. But what if you want to build a report from a data source that isn’t a database or doesn’t have a provider? For example, what if you want the report source to be data from an ADO.NET dataset? To meet such requirements, you can extend SSRS by writing (or acquiring) a Custom Data Extension (CDE).

Choosing Data Integration Approach
Before deciding to write a custom data extension, it’s worth exploring all your data integration options with non-standard data sources. Table 1 enumerates some of them.

Table 1. Data Integration Options: The table lists alternate data integration approaches, their tool requirements, the effort required for implementation, and shows when you might consider using each.

ReportViewer Controls
Going through the various options shown in Table 1, you can first consider the SSRS 2005 ReportViewer controls that ship with Visual Studio.NET 2005. You can configure these in a local report-processing mode where the application is responsible for supplying the report data. In local-processing mode, the application can bind a local report to various collection-based objects, including ADO.NET regular or typed datasets. If you don’t need to deploy your report to the report catalog and you target SSRS 2005, the report viewer controls may be the way to go.

XML Data Extension
A second option is when the source is an ADO.NET dataset (or XML document) streamed from a URL-addressable resource, such as an ASP.NET page or a Web service, consider the SSRS 2005 XML data extension. This blog post explains how you can build a report that uses the XML data extension to invoke a Web service and use the returned ADO.NET dataset as a data source.

CLR Stored Procedures
A third possibility is to build the dataset at the data source. For example, if your application targets SQL Server 2005, you could implement a CLR stored procedure to build the dataset. Consider this option when you need to extract data from many table objects but want to avoid multiple trips from the application layer to the database. The article “Implementing Smart Reports with the Microsoft Business Intelligence Platform” demonstrates how this could be done.

Finally, as shown in the last row of Table 1, a CDE (the subject of this article) gives you the most flexibility. As with the other SSRS extensibility features, custom data extensions are supported with SSRS Standard or Enterprise Edition only.

Introducing Custom Data Extensions
A custom data extension is a .NET module that you can use to extend the SSRS data access capabilities if needed. For example, as shipped, SSRS doesn’t support binding a server report to an ADO.NET dataset. Yet, many integration requirements call for the application to pre-process the data before handing it off to SSRS for display in a report. For example, a distributed application may need to retrieve a dataset from the middle tier, update its data based on some business rules, and bind the dataset to a deployed report. This is exactly the scenario the CDE you’ll create in the rest of this article is designed to handle.

Sample CDE Design Goals
The sample CDE meets the following high-level design requirements:

• It supports both serialized and file-based datasets. In the first case, the application serializes the dataset to XML and passes the serialized payload as a report parameter. In the latter case, the application passes a report parameter that specifies the full path to a file containing the serialized dataset.
• It allows the report designer to select an arbitrary table in a multi-table dataset at design time. Having multiple tables could be useful if the report requires more than one report dataset.
• It exposes the dataset XML schema in the Report Designer. This allows the report author to use the familiar drag-and-drop technique to lay out the report.

The CDE implementation is also subject to the following performance tradeoffs and restrictions:

• Uploading large datasets from the application to the Report Server may impact the application performance.
• ADO.NET data relations and joining dataset tables are not supported; however you can (and I encourage you do so)?enhance the extension if this is a requirement.
• Due to the query parameter size limitation, the URL addressability option for requesting dataset-bound reports is impractical. Instead, the application would call down to the RS Web service to pass the dataset as a parameter. That said, URL addressability is certainly possible with file-based datasets where the application needs to pass the file path only.

Integration View
Figure 1 shows a typical integration scenario that leverages the custom data extension. The client application (either Windows Forms or browser-based) prepares an ADO.NET dataset, serializes it to XML and passes it as a parameter to a report that uses the CDE.

	?
Figure 1. Custom Data Extension Scenario: The figure shows the various steps involved in using a custom data extension to bind an ADO.NET dataset to a report.

While processing the report, the Report Server discovers that a CDE is associated with the report and sends the serialized dataset to the CDE. The CDE de-serializes the dataset and exposes it as an SSRS-compatible dataset. Finally, the Report Server extracts the dataset in a forward-only fashion and populates the report with data.

Implementing a Custom Data Extension
Microsoft has provided a sample CDE called FsiDataExtension with the SQL Server samples. After familiarizing myself with this sample, I found implementing this sample CDE straightforward. In a nutshell, the process involves coding several classes that implement standard interfaces provided by SSRS. Table 2 lists these classes and interfaces.

Table 2. Standard CDE Classes and Interfaces: A custom dataset extension has several classes that implement standard interfaces.

	?
Figure 2. CDE Interfaces: At runtime, SSRS invokes the CDE interfaces to configure the extension and retrieve data.

Based on your CDE requirements, you may find that most of these classes require either minimal or no code. For example, because the sample CDE doesn’t establish a database connection, it doesn’t use the DSConnectionWrapper class. To implement a CDE, you need to understand how SSRS interacts with the CDE (see Figure 2).

Sequence Diagram
As Figure 2 shows, SSRS first asks the CDE to open a connection to the data source, passing the connection string. Next, SSRS calls IDbConnection.CreateCommand() to obtain a reference to a command object that it uses to send the query. SSRS then calls IDbCommand.CreateParameter method as many times as needed to create all the required query parameters. The CDE class that represents a query parameter must implement the IDataParameter interface. This allows SSRS to configure the parameter and add it to an object that implements IDataParameterCollection.

After populating the parameter collection, SSRS calls the IDataReader.ExecuteReader method of the Command object, which is responsible for retrieving data from the data source and exposing the data as an object that implements IDataReader interface. For each field in the report dataset, the Report Server calls the IDataReader interface’s GetOrdinal method to get the positional index of that field in the reader field collection. Later, SSRS will ask for the value of the field by its positional index. After matching the fields of the report dataset with the fields of the CDE DataReader, SSRS begins retrieving data in a forward-only fashion by calling IDataReader.Read until it reaches the end of the rowset. The reader field values are retrieved via calls to the IDataReader.GetValue method.

Now that you know about the CDE sequence of events, let’s drill down into its implementation details.

Handling Connections
As noted, this sample CDE doesn’t connect to a data source because it gets everything it needs from the query parameters (a serialized dataset or a path to file). But if you build a CDE that needs to connect to data source, you need to flesh out the IDbConnection interface. The most important method of this interface is IDbConnection.Open(). Before calling this method, SSRS will pass the data source properties set at design time to the IDbConnection properties including the connection string (ConnectionString property).

Implementing Command
If you have done any ADO.NET programming, you know that you need to create a command object to use a DataReader. Similarly, the DsCommand class represents the CDE command object. It fulfils two main tasks. First, the IDataParameterCollection.GetParameters() method returns the query parameters defined for the query. If the parameters are defined as placeholders inside the query text, GetParameters would parse the query text. In our case, the query supports only one parameter (DataSource). Therefore, you can just add this parameter to the parameter collection and return the collection to SSRS.

   public IDataParameterCollection GetParameters ()   {      DsDataParameterCollection parameters =          new DsDataParameterCollection();      // inform the designer that @DataSource parameter       // is needed      parameters.Add(new DsDataParameter(Util.DATA_SOURCE,          m_connection.ConnectionString));      return parameters;   }

The second major role of the CDE command object is to prepare and return a DataReader object that implements the IDataReader interface. The ExecuteReader method handles these tasks.

   public IDataReader ExecuteReader()  {      if (m_connection == null || m_connection.State !=          System.Data.ConnectionState.Open)         throw new InvalidOperationException(         "Connection must be valid and open.");         DsDataReader reader = new DsDataReader(m_connection,          m_cmdText, m_parameters);      reader.LoadDataset();      return reader;   }

When SSRS needs the data, it calls the ExecuteReader method, which instantiates a new DsDataReader object and passes the CDE context consisting of the connection string, command text, and parameters. Next, ExecuteReader calls the LoadDataset helper method to load the dataset and returns the reader object to SSRS.

Implementing DataReader
The DataReader object (see the DsDataReader class) is the workhorse of the CDE. It is responsible for loading the dataset and providing forward record navigation. The most interesting method is LoadDataset.

   internal void LoadDataset()   {      string dataSource = null;      DsDataParameter parameter =          m_parameters.GetByName(Util.DATA_SOURCE)          as DsDataParameter;                    dataSource = parameter.Value.ToString();         m_dataset = GetDataSet(dataSource);                    if (m_cmdText.Trim().ToLower()=="nothing")             m_datatable = m_dataset.Tables[0];      else             m_datatable = m_dataset.Tables[m_cmdText];           m_ie = m_datatable.Rows.GetEnumerator();        }      private DataSet GetDataSet(string dataSource)   {      DataSet dataset = new DataSet();         if (dataSource.IndexOf("<") >=0 ) {                 StringReader reader = new             StringReader(dataSource);           dataset.ReadXml(reader);      }      else {         FileIOPermission permission = new           FileIOPermission(FileIOPermissionAccess.Read,             dataSource);         permission.Assert();         dataset.ReadXml(dataSource);      }        return dataset;   }   

First, LoadDataset gets the value of the DataSource parameter and passes it to the GetDataSet helper function. GetDataSet uses a simple algorithm to check the dataset format. If the value of the DataSource parameter starts with “<“, GetDataSet assumes that DataSource contains a serialized ADO.NET dataset, in which case it deserializes the parameter value back to an ADO.NET dataset. Otherwise, GetDataSet assumes that the parameter value specifies the full path to the dataset file.

Note that I am specifically demanding read CAS (Code Access Security) permission to the physical file. Regardless of the fact that you will configure the CDE assembly for full access, CAS is layered on top of the OS security. For this reason, if you decide to use a persisted dataset, grant the ASP.NET worker process at least read permission to that file.

The rest of the DsDataReader code implements the IDataReader standard property and methods, such as providing a forward-only enumerator (Read method), returning the field count (FieldCount property), getting a field value (GetValue method), etc.

Debugging Custom Data Extension
Follow these steps to step through the CDE code.

	?
Figure 3. Debugging: Use the “Start external program” debug option to step through your CDE project from the report that uses the CDE.

1. Add both the CDE and SSRS projects to the same VS.NET solution.
2. Open the CDE project properties and set up the Debug tab, as shown in Figure 3.
3. Back to the Solution Explorer, set your CDE project as a startup project.
4. Place breakpoints in the CDE code. Hit F5 to start debugging.
5. Once the SSRS project is launched in a separate instance of VS.NET, open the test report, switch to the Data tab and run the query.

At this point, execution should hit your breakpoint and you should be able to step through the CDE code.

Deploying a Custom Data Extension
You must deploy and register the CDE properly with the Report Designer and Report Server before you can use it on the report. Configuring the CDE involves modifying several configuration files. For your convenience, I included my version of the affected configuration files, but please do not overwrite your configuration files with mine. Instead, use them for reference only.

Registering the CDE with the Report Designer
At design time, the CDE is used by the Report Designer. Follow these steps to configure the CDE.

1. Copy the CDE assembly (Prologika.CustomDataExtension.dll) to the Report Designer folder (C:Program FilesMicrosoft Visual Studio 8Common7IDEPrivateAssemblies).
2. In the same folder, open the RSReportDesigner.config file and locate the element.
3. Add the following line after the last element in the section.

1. If VS.NET is open, close it and open it again to restart the VS.NET IDE and reflect the configuration changes.

At this point, the Report Designer configuration is complete, and you should be able to create a data source using the CDE.

Registering the CDE with the Report Designer
At runtime, the Report Server interacts with the CDE. Here is how to configure the CDE with the Report Server.

1. Deploy the CDE assembly (Prologika.CustomDataExtension.dll) to the Report Server binary folder (C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesReportServerin).
2. Open the rsreportserver.config file from the C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesReportServer folder.
3. Locate the element and register the CDE extension just like you did with the Report Designer configuration file.
4. To grant the CDE code the necessary security permissions, open the rssrvpolicy.config and add the following code group:

You don’t need to restart the Report Server after making this change (this will happen automatically). After these steps, you should be able to successfully author a report that uses the CDE.

Report Authoring
After installing and registering the CDE, authoring a report that uses it as a data source is easy. The Reports project includes a sample TestDS report which you can use to test the CDE. For better design-time experience, I recommend you use serialize an ADO.NET dataset object to a file using the WriteXml method. This allows you to set the report parameter to the file path instead of the entire dataset XML payload.

To lay out the report, you need the dataset schema only. However, if you want to preview the report with data, make sure that the serialized dataset also contains sample data. The DatasetSalesOrder.xml sample file (included in the downloadable code) contains both the dataset schema and sample data.

	?
Figure 4. Selecting a Data Source: Select the Dataset Extension as a data source type to use the dataset CDE.

Follow these steps to author a report that uses the CDE.

Create a Report Dataset
Start authoring the report by setting up the report dataset.

1. Create a blank report and open it in the Report Designer. Switch to the Data tab.
2. Create a new report dataset. Set its data source to use the Dataset Extension type, as shown in Figure 4.

1. Leave the rest of the Data Source settings to their default values. The CDE doesn’t use them. Click OK to return to the Dataset dialog.
2. Enter SalesOrderHeader as a query string. This is the ADO.NET table name (the only table in the sample dataset) which will be used as a data source. Alternatively, enter Nothing to default to the first table in the dataset. Click OK to close the Dataset dialog.

Set Up Report Parameters

	?
Figure 5. Creating Parameters: The client passes the application dataset to the hidden DataSource parameter.

As noted, the application will pass the ADO.NET dataset as a report parameter. Follow these steps to create a DataSource parameter.

Select the Report menu and then Report Parameters.

Create a DataSource parameter, as shown in Figure 5.

Check the “Hidden” checkbox to hide the parameter from the end user.

(Optional) To speed up report testing, enter the full path to the dataset file as a default parameter value. This allows you to switch to the Preview tab without having to set the DataSource parameter value each time. Click OK to return back to the Data tab.

The next step is very important. To pass the dataset to the CDE successfully, you need to link the report-level DataSource parameter to a query parameter. That’s because only query parameters can be passed to the CDE.

Go to the report dataset properties (click the Ellipsis (?) button next to the Dataset dropdown) and flip to the Parameters tab.

Set up a new query-level parameter, as shown in Figure 6.

	?
Figure 6. Linking Parameters: Link the report parameter to a query parameter to pass its value to the CDE.

When the report is processed, SSRS passes the value of the DataSource report-level parameter to the DataSource query-level parameter, which is passed subsequently to the CDE.

Retrieving the Dataset Schema
At this point, you have everything in place to test the CDE. Let’s take the CDE for a ride.

Because the query syntax is proprietary, make sure to select the Generic Query Designer toolbar button.

Click the exclamation (!) toolbar button to execute the query.

In the Define Query Parameters dialog that follows enter the full path to the dataset file (should be the same as the default value of the @DataSource report-level parameter) and click OK.

	?
Figure 7. Testing the CDE: The CDE query results are exposed as a regular report dataset.

If all is well, the Report Designer will pass the file path to the CDE, which loads the dataset and returns it to the Report Designer to display it in the grid.

Click the Refresh button to update the report dataset definition. Expand the DataSet1 node in the Datasets toolbox to see the dataset fields.

From here, switch to the Layout tab and create the report layout by dragging and dropping dataset fields as usual. Once you are satisfied with the results, deploy the report to the report catalog.

Report Delivery
The article code includes VS.NET 2005 Windows Forms and ASP.NET test projects that demonstrate how custom applications can use the CDE. The test projects leverage the Windows Forms ReportViewer and ASP.NET ReportViewer control respectively. If you cannot use the ReportViewer controls in your applications, you can call the Render method of the SSRS ReportExecution Web service, which is what the controls do behind the scenes.

Thanks to the fact that the report viewers abstract most of the SSRS Web service technicalities, generating the TestDS report is remarkably simple, as shown below.

   private void RunRemote() {      reportViewer.ProcessingMode =          Microsoft.Reporting.WinForms.         ProcessingMode.Remote;      // Get the Report Server endpoint from       // the config file      reportViewer.ServerReport.ReportServerUrl =          new Uri(txtReportServer.Text);      reportViewer.ServerReport.ReportPath =          "/Prologika/TestDS";      // Bind the dataset      SetParameters();      reportViewer.RefreshReport();   }      private void SetParameters() {      ReportParameter[] parameters = new          ReportParameter[1];      EntitySalesOrder entitySalesOrder = new          EntitySalesOrder();      sqlDataAdapter.Fill(entitySalesOrder);      parameters[0] = new ReportParameter("DataSource",          entitySalesOrder.GetXml());      reportViewer.ServerReport.SetParameters(parameters);   }

First, the code configures the ReportViewer for remote mode since we will be requesting a deployed (managed) report. Next, the code calls SetParameters to pass the dataset as a report parameter. For testing purposes, the code loads the ADO.NET dataset from a file. In real life, the dataset can come from anywhere (e.g. from a business logic layer) as long as it conforms to the dataset schema used by the report. Next, the code passes the XML representation of the dataset as a ReportViewer parameter. Finally, the code calls the RefreshReport method of ReportViewer to submit the report request and render the report.

I’m a firm believer that no matter how feature-complete and mature a given tool is, if it’s not extensible or if it’s built on proprietary technologies it will probably fail to meet demanding requirements. If you were to ask me what SSRS’s most important feature is, I’d answer, “open and extensible architecture.”

In this article, you’ve seen how to extend the default SSRS data architecture by writing a custom data extension. You can use the sample CDE which accompanies the article code to build reports that use data from ADO.NET datasets. Consider this extension when your application needs to bind an ADO.NET to a server report?a scenario which is not currently supported by versions 2000 and 2005 of Reporting Services.