Crystal Reports provides direct access, or native, drivers for some databases. These drivers are written specifically for a particular database and are often the best choice. However, because hundreds of types of databases exist, Crystal Decisions can't possibly write direct access drivers for all of them. So often, users turn to using standard data access layers such as ODBC or OLEDB to connect to their databases. Often, the vendor of a database provides an ODBC driver or OLEDB provider so that other applications can access the vendor's database. Sometimes though, even this is not enough. Customers have data that they would like to report off of that is not accessible by any Crystal Reports data source driver or via ODBC or OLEDB. To accomplish this, customers often turn to the COM Data Source driver or the Java Data Source driver. This section describes the COM version of the driver, but much of the theory applies to the Java Data Source driver as well.
The Component Object Model, or COM, is a Microsoft-based technology for software component development. It's the underlying technology that runs Visual Basic and Active Server Pages. A COM object is a piece of code that adheres to the COM specification and is easily used by other components, either inside a single application or between disparate applications.
Because COM is a popular technology, Crystal Decisions decided to leverage it to create an extensible data source driver mechanism. This COM Data Source driver doesn't connect to a databaserather it gets data from a COM object written by you. This means that if you are somewhat savvy in the Visual Basic world, you can write your own "mini data source driver" (called a COM Data Provider) that enables access to data that would otherwise be unavailable.
The COM and Java Data Provider scenarios are only possible with the Advanced Developer edition of Crystal Reports 10. The other versions do not include the appropriate Crystal Reports driver.
To better understand the concept on writing your own COM Data Provider, look at a few scenarios in which this can be beneficial.
Leveraging Legacy Mainframe Data
Although new technologies are surfacing at an alarming rate, many companies still have data held in legacy mainframe systems. Often, the nature of these systems doesn't allow for any kind of relational data access, and thus lowers the value of the system. However, these systems can often output text-based files called print files or spool files that contain the data held in the mainframe system. These text-based files are often more complicated than a set of simple comma separated values and thus require a "bridge" between the files and a data access and reporting tool like Crystal Reports. Writing a COM Data Provider can serve just this purpose. The Data Provider would read the text files, parse out the required data, and return it to Crystal Reports for use in numerous reports.
Handling Complex Queries
Often, companies have a database that is accessible via standard Crystal Reports data access methods. However, the process of connecting to the database and performing a query can be quite complex. Sometimes this is because the database servers are constantly changing, queries are becoming more complex, and other business processes affect the complexity of the query. By writing a COM Data Provider, a clever person can abstract the location and complexity of the database interaction away from the user designing a report. The user simply connects to the Data Provider, and the rest of the logic is done transparently in the background.
Runtime Manipulation of Data
Performing a simple query against a database that returns a set of records is often all that is needed. However, sometimes logic needs to be incorporated into the query that cannot be expressed in the database query language (using SQL). Other times, per-user manipulation of data needs to be performed, such as removing all salaries stored in a database for all other users than the currently logged in user for confidentiality purposes (often called data-level security). This runtime manipulation can be performed by a COM Data Provider.
These three scenarios outline just a few of the reasons why you might want to use the COM Data Source driver and create your own COM Data Provider. The following sections describe the technical details of doing this.
Creating a COM Data Provider
COM Data Providers can be written in any development language or platform with the capability of creating COM objects. Most commonly, they are created in either Visual Basic or Visual C++. The following example uses Visual Basic, but it can easily be translated to other development languages. To create a simple COM Provider, follow these steps:
Figure 15.1. Creating a new Active DLL project in Visual Basic.
Figure 15.2. The Visual Basic Project References dialog is shown here referencing the ADO Library.
Public Function GetRecordset() As ADODB.Recordset Dim rs As New ADODB.Recordset ' Populate the recordset Set GetRecordset = rs End Function
Returning an ADO Recordset
There are generally two ways to obtain an ADO recordset: performing a database query and constructing it yourself. The following code example illustrates how to perform a database query and obtain a recordsetin this case using a query against the Xtreme sample database.
Public Function CustomerOrders() As ADODB.Recordset Dim rs As New ADODB.Recordset Dim sql as String sql = "SELECT * FROM Customer, Orders WHERE Customer.'Customer ID'" sql = sql & " = Orders.'Customer ID'", "DSN=Xtreme Sample Database 10" rs.Open sql Set CustomerOrders = rs End Function
The question you might be asking yourself is how this query could be parameterized. The COM Data Source driver handles this nicely. It maps any arguments you have defined to your method into report parameters. The following code example illustrates a Data Provider function that has a parameter:
Public Function Customers(CountryParam As String) As ADODB.Recordset Dim rs As New ADODB.Recordset rs.Open "SELECT * FROM Customer WHERE Country = '" & CountryParam & "'", _ "DSN=Xtreme Sample Database 10" Set Customers = rs End Function
When a Data Provider with a parameterized method is used from the report designer, the user is prompted for a parameter value.
As was mentioned previously, one way to obtain a recordset is to perform a query. Listing 15.1 illustrates how to construct a recordset on the fly and read data out of a text file.
Listing 15.1. A COM Data Provider That Parses Data from a CSV File
Public Function CSVText(FileName As String) As ADODB.Recordset Dim rs As New ADODB.Recordset ' Open the text file Dim FileSystem As New IWshRuntimeLibrary.FileSystemObject Dim fileText As IWshRuntimeLibrary.TextStream Set fileText = FileSystem.OpenTextFile(FileName) ' Read the first line of text to grab the field names Dim buffer As String buffer = fileText.ReadLine() Dim fields() As String fields = Split(buffer, ",") Dim i For i = LBound(fields) To UBound(fields) ' Add a field in the recordset for each field in the csv file rs.fields.Append fields(i), adBSTR Next rs.Open ' Read the contents of the file While Not fileText.AtEndOfStream buffer = fileText.ReadLine() rs.AddNew For i = LBound(fields) To UBound(fields) ' Grab the field values fields = Split(buffer, ",") rs(i).Value = fields(i) Next rs.Update Wend Set CSVText = rs End Function
This code could be used as is or adopted to meet the needs of other kinds of files or data sources. Using the COM Data Source driver gives you complete flexibility and control over the data source.
Part I. Crystal Reports Design
Creating and Designing Basic Reports
Selecting and Grouping Data
Filtering, Sorting, and Summarizing Data
Understanding and Implementing Formulas
Implementing Parameters for Dynamic Reporting
Part II. Formatting Crystal Reports
Fundamentals of Report Formatting
Working with Report Sections
Visualizing Your Data with Charts and Maps
Custom Formatting Techniques
Part III. Advanced Crystal Reports Design
Using Cross-Tabs for Summarized Reporting
Using Record Selections and Alerts for Interactive Reporting
Using Subreports and Multi-Pass Reporting
Using Formulas and Custom Functions
Designing Effective Report Templates
Additional Data Sources for Crystal Reports
Multidimensional Reporting Against OLAP Data with Crystal Reports
Part IV. Enterprise Report Design Analytic, Web-based, and Excel Report Design
Introduction to Crystal Repository
Crystal Reports Semantic Layer Business Views
Creating Crystal Analysis Reports
Advanced Crystal Analysis Report Design
Ad-Hoc Application and Excel Plug-in for Ad-Hoc and Analytic Reporting
Part V. Web Report Distribution Using Crystal Enterprise
Introduction to Crystal Enterprise
Using Crystal Enterprise with Web Desktop
Crystal Enterprise Architecture
Planning Considerations When Deploying Crystal Enterprise
Deploying Crystal Enterprise in a Complex Network Environment
Administering and Configuring Crystal Enterprise
Part VI. Customized Report Distribution Using Crystal Reports Components
Java Reporting Components
Crystal Reports .NET Components
COM Reporting Components
Part VII. Customized Report Distribution Using Crystal Enterprise Embedded Edition
Introduction to Crystal Enterprise Embedded Edition
Crystal Enterprise Viewing Reports
Crystal Enterprise Embedded Report Modification and Creation
Part VIII. Customized Report Distribution Using Crystal Enterprise Professional
Introduction to the Crystal Enterprise Professional Object Model
Creating Enterprise Reports Applications with Crystal Enterprise Part I
Creating Enterprise Reporting Applications with Crystal Enterprise Part II
Appendix A. Using Sql Queries In Crystal Reports
Creating Enterprise Reporting Applications with Crystal Enterprise Part II