RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Create Reports from Any Data Source Using SQL Server Reporting Services Custom Data Extensions : Page 2

SQL Server Reporting Services makes it easy to build reports from database data, but what if the data isn't in a database? This article shows you how to take advantage of Reporting Services' unique extensibility model to build reports from ADO.NET datasets.

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.
Class Implements Purpose Used In Sample?
DsConnectionWrapper IDbConnection, IDbConnectionExtension, IExtension Responsible for establishing a database connection No
DsTransaction IDbTransaction Enlists the database commands in a the data source transaction No
DsCommand IDbCommand, IDbCommandAnalysis Responsible for handling the report query string Yes
DsDataParameter IDataParameter Represents a query parameter Yes
DsDataParameterCollection ArrayList, IDataParameterCollection A collection of the query parameters Yes
DsDataReader IDataReader Handles the access to the dataset data Yes

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.

Tip: You can help the Report Designer discover the query parameters during design time so it can prompt the user for their values. To support this feature, implement the IDbCommandAnalysis interface in the Command object. This interface exposes a single method called GetParameters. When you click on the "Exclamation point" button to run the report query, the Report Designer probes the extension to find out if it implements this interface, and if so, calls the GetParameters method. This method is responsible for parsing the query string set at design time and returning a collection with the parameter placeholders.

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

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date