Click Here to Download The Visual Basic Sample Code for this article, and here to download the c # code.
Contents
IntroductionData Processing Extension BasicsImplementing The ExtensionDeploying The ExtensionCreating The ReportConclusionReled Books
Introduction
Reporting Services provides access to SQL Server, Oracle, ODBC, and OLE DB data sources as part of its standard features. For many reporting scenarios, connecting to a database and running a query is all that is needed to get all the information you need to report upon. But what happens if you want to use a DataSet as your data source? For example, maybe you already have a middle tier that processes your data to conform to your business logic and produces a DataSet as a result. Or maybe you don 't, but you nonetheless want to manipulate your raw data prior to reporting in ways that are a better fit for a Microsoft Visual Basic or C # implementation than a SQL implementation, and a DataSet would be the logical end result of such processing. Well, fortunately, it is possible to do this. It is even relatively easy, once you work out what parts of the data processing extension interfaces really have to be implemented to wrap up a DataSet in a way that Reporting Services can use.
In this Article, We'll Take a Look At Creating and Deploying A Simple Data Processing Extension That Can Be Used to Provide DataSet Data To a Reporting Services Report.
Data Processing Extension Basics
Reporting Services allows you to expand or customize the data sources available to you seven interfaces by means of data processing extensions. A data processing extension is an assembly containing the implementation of a set of interfaces available in the Microsoft.ReportingServices.DataProcessing namespace. There are That Must Be Implement in Any Data Processing Extension: Table 1. The Seven Interfaces in A Data Processing Extension
InterfaceDescriptionIDbConnectionRepresents a unique session with a data source. In the case of a client / server database system, the session may be equivalent to a network connection to the server.IDbTransactionRepresents a local transaction.IDbCommandRepresents a query or command that is used when connected to a data source.IDataParameterRepresents a parameter or name / value pair that is passed to a command or query.IDataParameterCollectionRepresents a collection of all parameters relevant to a command or query.IDataReaderProvides a method of reading a forward-only, read-only stream of data from Your Data Source.
There are also several optional interfaces that provide additional functionality for connections, transactions, and so on, but the enhancements they provide are not needed in a DataSet data processing extension. If you'd like to know more about these additional interfaces, take a look At The Preparing To Implement A Data Processing Extension Topic in The Reporting Services Documentation.
From looking at the interfaces listed above, you can see that building a data processing extension is rather similar to building a Microsoft .NET Framework data provider. Many of the interfaces are named the same and provide similar but often not identical functionality, so if you have worked with the System.Data interfaces before, make sure to keep the differences in mind.For the purposes of accessing a DataSet, half of the required interfaces need only the most minimal implementation, and the remaining interfaces can be implemented with fairly basic functionality . Let's take a look at what really needed to happen to get this extension working.
Implementing the extension
The sample application that this article is based on reads data from two or more XML files, then aggregates the data from these files into one Table in one DataSet. The file names are provided as the command text. The schema to verify the file structure is read from the Report Server config file using a Connection property. This file and schema information is used to create a DataSet that provides the data source for the DataReader. The Report Designer will use DataReader.Read to access the DataSet data for the report.
Paying your dues
Open a new Class Library project in Microsoft Visual Studio, and add a reference to Microsoft.ReportingServices.Interfaces.dll. It contains the Microsoft.ReportingServices.DataProcessing namespace that you'll need to reference for the data processing extension interfaces.
Our first order of business is to take care of the classes that implement the IDbTransaction, IDataParameter, and IDataParameterCollection interfaces. These classes are included only because they are required in any data processing extension. In this implementation, since we do not make a database connection, do any data modification, or use any kind of structured language to communicate with a relational database, they are not used for anything. They can all be implemented in a skeletonized form, as illustrated in the sample code accompanying this article.Note It is possible that you might want to implement these interfaces more fully, depending on your application. In cases where you want to retrieve relational data, process it, create a DataSet, and then report on that, clearly these would be of use.
The Heart of the Matter
Once The Required-Unnenecessary Classes Are Out of The Way, Let's Take a Look At The IdbConnection, IDBCommand, and iDataReader Implementations That Actually Do The Work In this Extension.
Since we are not connecting to a database, we're using the connection class for two things, really. The first is to use the SetConfiguration method to retrieve information from the config file about what schema should be used to validate the XML data. Storing the schema info makes the app a bit more flexible, as you can always just update it to conform to the kind of data you want to use. There are actually two config files used. Since a report developer may not have access to the target Report Server, the Report Designer and the Report Server need separate sources of configuration information. RSReportServer.config accesses the extension information for deployed reports, and RSReportDesigner.config accesses the extension information that is used in the designer UI. These two config files should have Identical Entries Identifying The Data Processing Extension.The Second Thing We Use The Connection Class for Is The CreateCommand Method, To Create A New Command Object Using The Connection Object overload. This Will Provide Access To The Configuration Info in The Context of The Command Object. All The Other Member The Minimum Required Implementation.
Using system;
Using system.data;
Using system.configuration;
USING SYSTEM.XML;
Using Microsoft.reportingServices.DataProcessing;
Namespace microsoft.samples.reportingservices.DataSetExtension
{
Public Class DSxConnection
: Microsoft.ReportingServices.DataProcessing.idbConnection
{
Private string _ConnString;
// idbconnection.connectionTIMEOUT DEFAULTS to 15 seconds.
Private int _Conntimeout = 15;
Private connectionState_State = connection;
Private string _locname = "Dataset Data EXTENSION";
INTERNAL STRING _XMLSCHEMA; / / DEFAULT CONSTRUCTOR.
Public dsxConnection ()
{
}
// Connection String Constructor Overload.
Public dsxConnection (String ConnString)
{
_CONNSTRING = connString;
}
Public String Connectionstring
{
get
{
Return_ConnString;
}
set
{
_CONNSTRING = Value;
}
}
Public int connectionTIMEOUT
{
get
{
Return_ConNTimeout;
}
}
Public ConnectionState State
{
get
{
Return_State;
}
}
// not used.
public
Microsoft.ReportingServices.DataProcessing.idbtransaction
Begintransaction ()
{
Return (NULL);
}
// not used.
Public void open ()
{
_State = connectionState.open;
Return;
}
// not used.
Public void close ()
{
_State = connectionState.Closed;
Return;
}
// Implement.
public
Microsoft.ReportingServices.DataProcessing.idbcommand
CreateCommand ()
{
// Create a command Object and pass in the
// Connection Object to Provide Config Info.
Return New DSxCommand (this);
}
Public String LocalizedName
{
get
{
Return_locname;
}
}
// Implement. Inherited from
// IEXtension through IDbconnection.
Public void setConfiguration (String Configuration)
{
// Get the XML Schema File
// from the config file settings.
XMLDocument Schemadoc = new xmldocument ();
Schemadoc.loadxml (Configuration);
IF (Schemadoc.documentelement.name
== "xsdconfiguration")
{
Foreach (XMLNode Schemachild in)
Schemadoc.documentelement.childNodes)
{
IF (schemachild.name == "xsdfile")
{
_Xmlschema = schemachild.innertext;
}
Else
{
Throw new Exception
"" Cannot Find Xsd Configuration Element. ");
}
}
}
Else
{
Throw new Exception
"" Error Returning Data from The Configuration File. ");
}
}
Public void dispose () {
}
}
}
Next up is the implementation of IDbCommand. As with the Connection class, there are only a handful of members in the Command class that we need to use to accomplish our goal. In this case, those are the Connection object overload of the constructor, the CommandText Property, And The Commandbehavior Overload of The ExecuteReader Method.
The overloaded constructor gives us a reference to the Connection object. All we want from that is access to the XML schema information that is set as an internal variable in DSXConnection. This variable is subsequently passed to the DataReader implementation in the call to ExecuteReader so that IT can be used in processing the xml data source.
The CommandText Property Takes The Comma Delimited String That Identifies The XML Files To Pull Data from, and is Entered in Report Designer When You Are Setting Up A New Report.
The ExecuteReader call creates the DataReader, creates a DataSet to serve as its data source, and returns the reader to the caller, which in this case is our report. Note that you must implement the CommandBehavior overload to support the SchemaOnly CommandBehavior value. Report Designer And Report Server Both Use this Overload Rather Than The Parameterless Version TO Obtain Field Information In Additional To The Data.
Using system;
Using system.data;
Using system.componentmodel;
Using Microsoft.reportingServices.DataProcessing;
Namespace microsoft.samples.reportingservices.DataSetExtension
{
Public Class DsxCommand
: Microsoft.ReportingServices.DataProcessing.idbcommand
{
Private string _cmdtext;
PRIVATE DSXCONNECTION _CONNECTION;
// idbcommand.commandtimeout Defaults to 30 seconds.
Private INT _CMDTIMEOUT = 30;
Private Microsoft.reportingServices.DataProcessing.comMAndType_cmdtype;
Private dsxparametercollection _parameters =
New dsxparametercollection ();
// Default constructor.
Public dsxcommand ()
{
}
// Command Text Constructor OverLoad.
Public dsxcommand (String cmdtext)
{
_cmdtext = cmdtext;
}
// Connection Object Constructor overload.
Public DSXCommand (DSxConnection Connection)
{
_CONNECTION = Connection;
}
Public String CommandText
{
Get {return _cmdtext;
Set {_cmdtext = value;}
}
Public int commandtimeout
{
Get {return_cmdtimeout;
Set {_cmdtimeout = value;}
}
Public Microsoft.reportingServices.DataProcessing.comMandType
CommandType
{
Get {return_cmdtype;
Set {_cmdtype = value;}
}
public
Microsoft.ReportingServices.DataProcessing.idataParameterCollection
Parameters
{
Get {return _parameters;
}
public
Microsoft.ReportingServices.DataProcessing.idbtransaction
TRANSACTION
{
Get {return (null);
Set {throw new notsupportedException ();
}
// not used.
Public void cancel ()
{
Throw new NotSupportedException ();
}
// not used.
public
Microsoft.ReportingServices.DataProcessing.idataParameter
CreateParameter ()
{
Return (NULL);
}
// Implement.
public
Microsoft.ReportingServices.DataProcessing.idaTareader
ExecuteReader
(Microsoft.ReportingServices.DataProcessing.commandbehavior Behavior)
{
Try
{
// CREATE The DataReader.
DSXDataReader TestReader =
New dsxdatarader (_cmdtext);
// Call The Custom Method That
// Populates the dataset.
TestReader.createdataDataSet (_Connection._Xmlschema);
// Return The DataReader.
Return TestReader;
}
Catch (Exception E)
{
Throw new Exception (E.MESSAGE);
}
}
Public void dispose () {
}
}
}
Finally, there is the DataReader class implementing IDataReader. This class is the most fully fleshed out, as most of its members are used by Report Designer to retrieve the data to be displayed. There are also two custom functions, CreateDataSet and ParseCmdText that provide additional functionality. ParseCmdText parses the input string of comma-delimited XML source files, and CreateDataSet merges them into a single DataSet for reporting. The resulting DataSet is then used as the source for the data that the DataReader returns.
Using system;
Using system.data;
USING SYSTEM.XML;
Using system.collections;
Using Microsoft.reportingServices.DataProcessing;
Namespace microsoft.samples.reportingservices.DataSetExtension
{
Public Class DSxDataReader
: Microsoft.ReportingServices.DataProcessing.idataReader
{
Private string _cmdtext;
Private int _currentrow = 0;
Private INT _fieldcount = 0;
Private string _fieldname;
Private Int_fieldordinal;
Private type _fieldtype;
Private Object _fieldValue;
Private dataset_ds = null;
// Default Constructionor
INTERNAL DSXDataReader ()
{
}
// Command Text Constructor OverLoad.
Internal DSXDataReader (String CmdText)
{
_cmdtext = cmdtext;
}
// Implement. Will Be Called
// by The Report Server To
// Return DataSet Data.
Public bool read ()
{
_currentrow ;
IF (_currentrow> = _ds.tables [0] .rows.count)
{
Return (False);
}
Else
{
Return (TRUE);
}
}
Public int fieldcount
{
get
{
_fieldcount = _ds.tables [0] .columns.count;
Return_fieldcount;
}
}
Public String GetName (INT i)
{
_fieldname = _ds.tables [0] .columns [i] .columnname;
Return_fieldname;
}
Public Type getFieldType (INT i)
{
_fieldtype =
_ds.tables [0] .columns [i] .datatype;
Return_fieldType;
}
Public Object GetValue (INT i)
{
_fieldvalue =
_ds.tables [0] .rows [this._currentrow] [i];
Return _fieldValue;
}
Public int getordinal (String name)
{
_fieldordinal =
_ds.tables [0] .columns [name] .ordinal
Return_fieldordinal;
}
// Input Parameter Should Be The Path
// to the .xsd file what was retrieved
// from the connection.setConfiguration Call.
Internal void createDataSet (String Schemafile)
{
// Open an XML DOC to Hold The Data.
XMLDocument Xmldoc = new xmldocument ();
// CREATE The Dataset.
DataSet DS = New Dataset ("Customers");
// Create The Schema for the Dataset.
DS.Readxmlschema (schemafile);
// Parse The Command Text String for the Files.
String [] Parameters = this.ParsecmdText ();
// Get the XML Data And
// Merge It Into The Dataset.
Try
{
For (int i = 0; i { DataSet Tempds = New Dataset (); Tempds.readxml (parameters [i]); DS.MERGE (TEMPDS); } } Catch (Exception E) { Throw new Exception (E.MESSAGE); } // set the dataset variable used in // the rest of the datareader members // to the one job product. _DS = DS; // set the current row to -1 // to prepare forread. _currentrow = -1; } Private string [] PARSECMDText () { // Check Format of Command Text. IF (_cmdtext.indexof (",")! = -1) { String [] dsparams = _cmdtext.split (new char [] {','}); // in Production Code, You'D // Want More Error Handling Here // confirming what the string value // Are Appropriate XML File Names, ETC. Return dsparams; } Else Throw new argumentexception ("The CommandText Value IS Not in The Appropriate Format."); Public void dispose () { } } } Of course, it is possible that you already have a middle-tier component that does some processing and produces a DataSet for you, and you'd like to use that rather than putting together a new DataSet in the extension code. That's easy enough- Just Add A Reference To The Assembly and Call The Method That Returns The DataSet from There, Like: Using system; Using system.data; USING SYSTEM.XML; Using system.collections; Using Microsoft.reportingServices.DataProcessing; Using thiscompany.thisassembly; Namespace thiscompany.thisnamespace.datasetextension { Public Class ExtensionDataReader: Microsoft.ReportingServices.DataProcessing.idaTareader { Private dataset_ds = null; // more variables ... INTERNAL EXTENSIONDATAREADER (THISPARAMETER) { // Get the dataset from the middle-tier assembly. THIS._DS = New thiscompany.thisassembly. Customer.IntegratedataDataSource . // set the current row. Currentrow = -1; } ... TO RECAP by Making The Workflow Explicit Here, What The Report Server Will Do When Running this Report IS: Call the DSXConnection default constructor Call DSXConnection.CreateCommand, which uses the DSXCommand Connection overload constructor to create the Command object Call DSXCommand.ExecuteReader (CommandBehavior), which in turn..: Calls the DSXDataReader command text overload constructor to create the DataReader object. Calls DSXDataReader.CreateDataSet to create the DataSet data source. This method in turn calls DSXDataReader.ParseCmdText to verify the input. Processing returns to DSXCommand.ExecuteReader (CommandBehavior), which then hands The DataReader Back to Report Server.and That's It. really. You can go ahead and buildate the solution, and then we're on to deployment. Deploying the extension First we'll need to register the extension with Reporting Services by copying the extension DLL to the Report Server and Report Designer directories and adding the appropriate entries for it to their config files. Copy RSCustomData.DLL and paste it into the Report Server bin directory (C: / Program Files / Microsoft SQL Server / MSSQL / Reporting Services / ReportServer / bin by default) and the Report Designer directory (C: / Program Files / Microsoft SQL Server / 80 / Tools / Report Designer by default) Then open the Report Server config file, RSReportServer.config, located by default in C:.. / Program Files / Microsoft SQL Server / MSSQL / Reporting Services / ReportServer You'll need to modify this file to add a new TYPE = "Microsoft.Samples.ReportingServices.DataStextension.dsxConnection, RSCustomData"> C: /customer.xsd Save and close the Report Server config file, and open the Report Designer one, RSReportDesigner.config in C:. / Program Files / Microsoft SQL Server / 80 / Tools / Report Designer Add an identical Name = "dataset" type = "Microsoft.reportDesigner.design.GenericQueryDesigner, Microsoft.ReportingServices.Designer" /> . 店................ Code Access Security The next thing we'll want to do is add entries to the Report Server and Report Designer policy files explicitly setting the code access permissions for the extension. Data processing extensions require FullTrust permissions to run properly. Open the Report Server policy file, rssrvpolicy.config, located by default in C: / Program Files / Microsoft SQL Server / MSSQL / Reporting Services / ReportServer, and create a new Version = "1" PermissionssetName = "fulltrust" Name = "DataseTextensionGroup" Description = "This Code Group GRANTS DATA EXTENSIONS FULL TRUST."> Class = "UrlmembershipCondition" Version = "1" URL = "C: / Program Files / Microsoft SQL Server / MSSQL / Reporting Services / Report Server / BIN / RSCUSTOMDATA.DLL" /> THEN Add An Identical These entries grant the DataSet data processing extension FullTrust permissions, based on URL evidence that identifies the extension assembly. For more information on managing code access security, see Code Access Security in SQL Server 2000 Reporting Services. Creating the report Now that we've created and deployed the DataSet extension, it's a snap to use it in a report. Open Visual Studio and start a new report project. Right-click on the Shared Data Sources folder and select Add New Data Source. In the TYPE DROP-DOWN, You Should See An Entry for The DataSet Data Processing Extension: Figure 1. The Shared Data Source Folder Select The Extension Entry, SELECT The No Credentials Radio Button-Since The No Credentials Connection To make, No Credentials Are Necessary. Save this data source. . Right-click on the Reports folder and select Add New Report Start to step through the Report Wizard that comes up On the Select the Data Source screen, accept the default selection on the DataSet extension data source you just created.: Figure 2. The Select The Data Source Screen On the design the query screen . Finish out the report using whatever settings you like-for the purposes of this sample I just accepted the defaults When previewed, the sample report should look similar to the following-I monkeyed with the field layout a bit for space considerations: Figure 4. Reporting Services Sample Report To deploy your report, specify your target report server (usually http: // MachineName / ReportServer) in your project properties, then select Debug, Start to deploy the solution and view your report in the browser. Conclusion So, as you can see, it is easy to access your DataSets from Reporting Services, once you work out the salient points of the APIs and the configuration requirements. Give it a whirl the next time you want to leverage some existing data processing functionality in One of your reporting apps.
