Chapter 15 Additional Data Sources for Crystal Reports

In this chapter

Understanding the Additional Crystal Reports Data Sources 338

Connecting to COM or .NET Data Sources 338

Connecting to an ADO.NET XML Recordset 344

Connecting to Java-Based Data Sources 345

Connecting to XML Data Sources 346

Introduction to the Integration Kits 348

SAP Integration Kit 349

PeopleSoft Integration Kit 353

Siebel Integration Kit 354

Baan Integration Kit 355

Troubleshooting 355

Crystal Reports in the Real World—Leveraging XML as a Data Source 356

Understanding the Additional Crystal Reports Data Sources

When thinking about data sources for Crystal Reports, most people tend to think about popular databases such as Microsoft SQL Server, Microsoft Access, Oracle, IBM DB2, and so on. However, the extent of Crystal Reports reaches far beyond these traditional relational databases. You’ve already learned about how Crystal Reports can use OLAP-based data sources in Chapter 16, “Formatting Multidimensional Reporting Against OLAP Data with Crystal Reports”; this chapter describes a few more advanced data sources that give you even more flexibility than you already have. The data sources discussed in this chapter are as follows:

• .NET- and COM-based data sources
• Java-based data sources
• XML as a data source
• Solution kits

If you would like to review how Crystal Reports can use OLAP-based data sources, see “OLAP Concepts and OLAP Reporting,” p. 360

Connecting to COM or .NET Data Sources

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, Business Objects 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, the ADO.NET 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 ADO.NET and Java Data Source drivers as well.

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 database—rather 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.

To better understand the concept of 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 other than the currently logged-in user for confidentiality purposes (often called data-level security). This runtime manipulation can be performed by either a COM Data Provider or a .NET Dataset Provider.

These three scenarios outline just a few of the reasons why you might want to use the COM Data Source driver or .NET Dataset Provider and create your own Data Provider. The following sections describe the technical details of doing this. The example demonstrates creating a COM provider, but the same functionality can be provided from .NET. For the report author, both methods are equal and transparent.

Creating a COM or .NET 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:

1. Open Visual Basic and create a new project. Instead of choosing the standard project type of Standard EXE, choose ActiveX DLL (see Figure 15.1). ActiveX is another name for COM technology. Choosing this creates a project that contains a COM object (by default called Class1).

   Figure 15.1. Creating a new ActiveX DLL project in Visual Basic.

   

2. The interface between the COM Data Provider that you create and the Crystal Reports COM Data Source driver is based on ActiveX Data Objects, or ADO. To use ADO in your project, you must first create a reference to it. From the Project menu inside Visual Basic, select References. From the list on the ensuing dialog, look for Microsoft ActiveX Data Objects. You might have just a single version of this on your machine, or you might have several. It’s usually easiest to just select the latest version. Figure 15.2 illustrates this.
   

   Figure 15.2. The Visual Basic Project References dialog is shown here referencing the ADO Library.

   

3. After that is done, the only thing left to do is create a function inside your class that returns an ADO recordset. The basic outline for this function is shown here. See the next section for more information on returning an ADO recordset.
   

   

4. By default, the class is named Class1. It’s best to give this a more meaningful name, such as DataProvider. Do this by selecting the Class1.cls file in the Project Explorer and changing the (Name) property from the Property Browser.
5. Also, the project name is Project1 by default. It’s best to give this a more meaningful name such as the company name or type of data name: for example, Xtreme or Sales. Do this by selecting Project1 Properties from the Project menu and changing the Project Name setting.
6. Build the DLL by selecting Make from the File menu; the name is not important.
7. Open the Crystal Reports Designer and create a new report. From the Data Explorer, choose Create New Connection and then expand the More Data Sources item; then choose COM Connectivity. This presents a dialog asking for you to enter the Program ID. To identify your COM Data Provider, enter the ProjectName.ClassName; for example, Xtreme.DataProvider.
8. You’ll receive a table list just like from a traditional database, but the table list is actually a list of methods on your COM object that return ADO recordsets.

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 recordset—in this case using a query against the Xtreme sample database.

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:

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

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.

Connecting to an ADO.NET XML Recordset

Although COM remains an exceedingly popular model for programming, it is being surpassed in new-development popularity by .NET. As Business Objects continues to remain current on Microsoft technologies, there is a means for reporting off ADO.NET data sources. The following steps describe connecting to ADO.NET data:

1. From the File menu, select New, Blank Report.
2. With the Database Expert open, expand Create New Connection and double-click ADO.NET (XML).
3. When presented with the ADO.NET window, first select the location of the source file. Either manually enter the path to the file or use the Browse button to find the file. When using the Browse button, be sure to select the correct filter (XML File, XML Schema, or .NET Dataset Provider).
4. If a .NET Dataset Provider is selected, the .NET dataset might expose multiple classes. To identify a specific class from the Provider, check the box beside Use Classes from Project, and select the class from the list of available classes.
5. Multiple datasets might be exposed from a given class. In order to select a dataset from a class, check the box beside Use Dataset from Class and select the dataset from the list of available datasets.

Connecting to Java-Based Data Sources

The COM Data Source driver is targeted at Visual Basic and Visual C++, and ADO.NET is targeted at .NET developers. Because Crystal Reports XI has a full Java SDK, an equivalent Java Data Source driver provides equivalent functionality of the COM driver for developers using the Java platform.

The process of creating a Java Data Source driver is conceptually similar to that of creating a COM Data Source driver. A Java class needs to be created that has a public function with a return type of ResultSet or CachedRowSet. A ResultSet is the standard object returned from a JDBC-based query, whereas the CachedRowSet is a disconnected recordset useful for parsing out things like XML. Listing 15.2 shows a simple Java Data Provider that returns a ResultSet.

Listing 15.2. A Java Data Provider That Returns Data from the Sample Database

To identify a Java class, simply compile the code into the .class file and place that compiled .class file into the JavaBeans classpath. To define the classpath, edit the following properties in the CRconfig.xml file in the default folder: C:Program FilesCommon FilesBusiness Objects3.0java.

JavaDir

This property must refer to a valid Java Runtime Environment (JRE) or J2SE Development Kit (JDK). If a JRE or JDK was detected during install, this property will already be set. If not, install JDK 1.4 and set the property manually. A valid setting is the complete path to the JDK:

<JavaDir>c:Program FilesJavaJREin</JavaDir>

JavaBeansClassPath

If the JavaBeans are unjarred, simply refer to the .class file’s path:

and if the JavaBean class files are jarred, refer to the same location with the .jar extension:

During the process of creating a report, Crystal Reports searches through all classes contained in the classpath. It then provides a list of methods with return types of java.sql.ResultSet. The same rules about function arguments apply. Any arguments to the Java method are mapped to report parameter fields. Using Java code, you can control exactly what data comes back.

Connecting to XML Data Sources

Crystal Reports can connect to XML data in three ways. XML can be presented as source data as

• Local data source—This is a physical XML file that resides locally or on the network and is identified by passing in the fully qualified path and filename. If a schema (.xsd) file exists, it can also be specified.
• HTTP(S) data source—An HTTP(S) data source is XML-formatted data accessed from a servlet, an ASP page, or a JSP page. The URL to the source may be HTTP or HTTPS. When using HTTPS, the ID and password information passed into Crystal Reports is used for authentication.
• Web Service data source—A web service data source can be a service on a local machine or network drive that is referenced either via a path and filename or via a servlet/dynamic web page (ASP/JSP). No schema is specified as it is derived through the web services framework. Web Service data sources are accessed using Web Services Description Language (WSDL). After the WSDL is specified, the driver prompts for service, port, and method.

Regardless of the data source, from the File menu choose New, Blank Report. From the Data window, expand Create New Data Connection and double-click XML. From that point, follow one of the following paths.

Connect to a Local XML Data Source

1. Specify the fully qualified path and filename to the .xml file. If the .xml file does not have embedded schema information, a schema (.xsd) file must be identified. To identify the schema file, ensure that there is a check mark beside Specify Schema File.
2. Click Next.
3. If the schema does not need to be specified, click Finish; otherwise, you will be prompted for the location of the schema. The schema may be referenced using a fully qualified path or URL.
4. Click Next and then Finish.

Connect to an HTTP(S) Data Source

1. Enter the URL to the .xml file. If the .xml file does not have embedded schema information, a schema (.xsd) file must be identified. To identify the schema file, ensure that there is a check mark beside Specify Schema File.
2. Click Next.
3. If authentication information is required, enter it here. Click Next.
4. If the schema does not need to be specified, click Next; otherwise, you will be prompted for the location of the schema. The schema may be referenced using a fully qualified path or URL. Click Next.
5. As a final step, HTTP parameters may be added/modified/removed in order to modify the set of data returned. Then click Finish.

Connect to a Web Service Data Source

1. Select the Use Web Service Data Source radio button. No schema file is needed, so the option will be dimmed immediately on a selection of this data source type. Click Next.
2. Specify the location of the web service. Either a local file or HTTP(S) location may be specified. Click Next.
3. If prompted for it, enter authentication information and click Next.
4. Select the service, port, and method from the lists. Click Finish.

After the connection to the data source is complete, the report may be designed as any other. The XML data is presented as a table just like any other Crystal Reports data source.

Introduction to the Integration Kits

The integration kits provide native access and integration to commonly used Enterprise Resource Planning (ERP) applications. They achieve this by providing unique access to the data via the ERP’s application layer, via the application’s published programming interfaces, or API. A complete list of supported ERP applications is defined in Table 15.1.

Table 15.1. Features by Integration Kit

This unique approach to data access allows the integration kit to honor the security, access all the data, make sample reports, leverage the metadata of the ERP application, and provide real-time access to the data.

Honor the Security

Organizations spend a lot of time and effort setting up security in their ERP applications. The integration kits are made so that users do not have to recreate this security. This results in organizations having to change security only once in their ERP application and this change is then reflected in the reports.

Access to All the Data

Many ERP applications, such as SAP, do not store all their data in the underlying database. Some of the data used in SAP can be found only in programmatic sources, such as cluster tables and other objects unique to SAP. Therefore, if your reporting tool could connect to only the underlying database, you would likely miss some critical pieces of data that might dramatically affect your business decisions. With the BusinessObjects integration kits, you do not have to worry about this problem because the kit connects to the application layer of SAP and can thus leverage SAP functionality and gain access to all forms of data storage that SAP provides.

Sample Reports

Sample reports are provided for all the integration kits. These reports not only provide a basis on which to start; but more importantly provide examples on how to report off hierarchies and use variables, to name just a few.

Leverage the Metadata of the ERP Application

Changes made to the application might not necessarily be reflected in the database. The native drivers connect to the application’s metadata and should see any of the changes. For example, SAP R3 has cluster tables that you will not find in the underlying database, but they are visible in the SAP R3 data dictionary.

Provide Real-Time Access to Data

With this approach, data is not extracted from the ERP application in order to report against it. In some cases it might make sense to extract the data to a data mart or data warehouse, and the ERP provider or Business Objects may provide solutions for that. A lot of production reporting requires real-time data, however, and the ERP’s own tools might prove inflexible and difficult to use.

SAP Integration Kit

The SAP integration kit includes drivers to allow connectivity to both R3 and Business Warehouse (BW). In addition, it includes a BW toolbar for Crystal Reports, a specific version of InfoView with Role-based links, sample iViews for Enterprise Portal, and an SDK in Java, COM, or .NET.

Reporting off R3 Data

The kit includes two drivers to allow Crystal Reports to connect to the ABAP data dictionary via a remote function call or RFC.

The Open SQL driver allows the user to connect to SAP R3, displaying the field and the short text description of the tables and fields. The developer can then join the tables accordingly, using the database expert, and then select the relevant fields from which to report. This driver displays not only transparent tables, but includes views, pool tables, cluster tables, ABAP data clusters, and ABAP functions. This driver is geared toward a technical person with functional expertise in joining the tables and then creating the report.

The InfoSet driver allows the user to connect to an existing InfoSet, as defined by SAP transaction SQ02. Alternatively, you can use an existing ABAP query, as defined by SAP transaction SQ01. The benefit of this driver is that the user does not need to know where the data is stored or how to join the tables. Another advantage is that any prompts defined in the InfoSet become parameters in Crystal Reports.

Reporting off BW Data

When reporting off of BW data, the Business Objects’ Integration Kit consists of three drivers that are used to pull data.

The BW Query driver enables the user to connect to a BW query. To enable selection of a query with this driver, Crystal Reports has its own toolbar, as shown in Figure 15.3, and the user needs to select the New Report icon from this toolbar. The user then selects the required BW query, and thus displays a listing of fields available in the query. The user can then create the report using any of those fields. Variables defined in the query automatically become Crystal Reports parameters. After the user has finished building the report, the user saves the report using the BW toolbar. This saves the report to BW, and if the user selects the option to do so, automatically publishes the report to Enterprise.

Figure 15.3. Crystal Reports after the SAP integration kit is loaded. Note the SAP menu and BW toolbar.

Two new drivers have been added to the XI R2 version of the SAP Integration Kit.

The MDX query driver supports using Crystal Reports to report off either a SAP BW query or a BW Cube.

Furthermore, it adds additional functionality by providing support for

• A structure in both the row and column of the BW query
• Hierarchy node and hierarchy node variables
• Free characteristics in a BW query

The Operational Data Store or ODS driver exposes the ODS tables, so that they look much like the tables of any relational database. However, because of the structure of the objects, it considers them to be like a database stored procedure.

Connecting to the ODS objects is not performed with the SAP toolbar described here, but rather by selecting the driver from the Data Explorer as shown in Chapter 1, “Creating and Designing Basic Reports.” After the user has finished building the report, the report can be saved back to BW and published to Business Objects Enterprise by selecting the Automatically Publish to Enterprise check box, as shown in Figure 15.4.

Figure 15.4. The additional options after saving a Crystal Report to SAP BW.

Using OLAP or Web Intelligence to Report off BW Data

Included in the SAP Integration Kit are specific drivers to allow OLAP Intelligence and Web Intelligence to report off of SAP BW.

To connect to a BW cube or query using OLAP Intelligence, open the connection browser and select Add. Select SAP Business Information Warehouse and then proceed to fill in the relevant data, as shown in Figure 15.5.

Figure 15.5. The information required to connect to a BW cube or query from OLAP Intelligence.

After you are connected, select the query or cube and process to build the report, as described in Chapter 19, “Creating OLAP Intelligence Reports.”

Web Intelligence is new to XI R2 and requires that a universe be built on a BW Cube or Query. However, to aid in this process, Designer builds the universe for you. To do this, select SAP from the list of connects and fill out the required information, as displayed in Figure 15.6.

Figure 15.6. The information required to connect to a BW cube or query from Web Intelligence.

Finish the remainder of the wizard and when you click OK, Designer generates the Universe for you. It may be necessary to move some of the objects around to suit your needs, but when you are finished, export to Business Objects Enterprise.

Viewing the Reports

Your SAP reports can be viewed either via the SAP integration kit’s InfoView or via SAP Enterprise Portal 6. The integration kit provides a number of sample iViews that can be imported by a portal administrator. These include

• Folder iView—The folders a user has access to are displayed, and from here a user can run her reports.
• Alert iView—Shows all the alerts that have been triggered by Crystal Reports. The user can then click on the report to view the report. The creation of alerts is defined in Chapter 11, “Using Record Selections and Alerts for Interactive Reporting.”
• Thumbnail iView—This iView displays the Crystal Report as a thumbnail of the report. The user can then click on the thumbnail to run the report.

BusinessObjects InfoView for SAP is a unique version of InfoView designed for integration with SAP BW systems. This integration includes features such as the ability to log on to InfoView using your SAP username and password. Related to authentication is the ability to access your roles and the Crystal Reports saved to these roles. The roles are defined in Enterprise as folders. The user can also schedule reports in the different languages loaded into SAP BW. This creates an instance for each language.

Peoplesoft Integration Kit

Crystal Reports has been embedded in PeopleSoft for many versions. The BusinessObjects integration kit for PeopleSoft extends the usefulness of the embedded reporting functionality by providing all the features of Business Objects XI to the report consumer.

Reporting Off Peoplesoft Data

The native PeopleSoft driver connects to the PeopleSoft query as defined within the PeopleSoft system. Furthermore, because you are required to pass a username to the PeopleSoft system, the driver will honor the parameters and security defined by the PeopleSoft query. To access the PeopleSoft query, select the PeopleSoft driver from the list of drivers available to Crystal Reports. After authenticating, a list of accessible queries becomes available. Select a query, and Crystal Reports treats the query’s resulting recordset the same way it treats a table. The developer is presented with a listing of fields that make up the query. These fields can then be dragged onto the report and the report can be developed as described in the first half of this book.

Viewing the Reports

After the report is complete, the user can publish the report and this report can then be viewed via InfoView. The integration kit allows for PeopleSoft authentication so PeopleSoft users can log on to InfoView using their PeopleSoft credentials. The security integration creates a group in Business Objects XI for each PeopleSoft role that is mapped. A single install of Business Objects XI can be mapped to multiple PeopleSoft domains.

Siebel Integration Kit

The Siebel integration kit provides access to Siebel CRM to allow report developers to create reports off Siebel business objects and have report consumers view this content from within Siebel via the Enterprise web desktop.

Reporting off Siebel Data

The Siebel integration kit provides access to Siebel’s data via the Siebel business objects. After a successful logon, Crystal Reports displays a listing of business objects and their respective business components. A user can then select single or multiple business components and then join them the same way tables are joined. In this way, multiple parent-child relationships between business components can be established under a single business object. After this joining is complete, the report designer is left with the multiple fields from the various business components. After the report is complete, it can be published to the respective folder in BusinessObjects Enterprise.

Viewing the Reports

Reports can be viewed from InfoView, which is described in further detail in Chapter 24. The integration kit also provides an integrated web desktop that users can launch from the Siebel client. This desktop is aware of the current view and can show the user only reports that are linked to that view.

Baan Integration Kit

The Baan integration kit allows developers to connect to and report against the Baan ERP and SSA ERP versions.

Reporting off Baan Data

Developers can use the native Baan driver to connect to all fields and tables via the application layer. The user can then join up the relevant tables and fields, like they would any other relational database. This driver supports the security defined for company, session, table, and field data.

The Baan integration kit also provides a component called Integrated Baan Reporting (IBR), which allows a user to run an existing Baan report and have the output of this report formatted as a Crystal Report. The user would run the report as he usually would; however, when selecting the Print option, the user should select the Business Objects print device. Business Objects captures the output of the report, formats it according to a Crystal Reports template, and then creates a report instance that includes both template and data. This method of reporting is very useful when the print function is one of the steps needed to complete an order.

Viewing the Reports

The reports are viewed using InfoView. Reports created via the IBR are also located on InfoView. Report instances are found in the Combined Reports folder and then in their respective Baan package and module subfolders. Report layout information can be found in the Baan Report Layout folder and templates are found in the Standard Templates folder.

Troubleshooting

Publishing with a COM or Java Provider

I’ve published a report using a COM (or Java) data provider to BusinessObjects Enterprise, but I am getting errors when the report is run.

Whenever one of these types of reports is published to BusinessObjects Enterprise, you need to make sure that you copy the COM or Java component to the machines running the Page and Job servers. This component needs to be installed and registered properly before the report can invoke it.

I am receiving errors when I try to connect to the Java data provider.

Make sure that you have installed a Java Virtual Machine (JVM, also called the Java Runtime Environment). The JVM is required to connect to Java data providers.

SAP Menu and Toolbar Do Not Appear

After installing the SAP integration kit for BW, I was expecting the SAP menu and toolbar to appear in Crystal Reports, but they are not there.

Before installing the integration kit, ensure that you have the SAP GUI installed, including the BW add-ons, and that the GUI is patched to the correct level.

No Tables Appear When I Select the SAP Table, Cluster, or Function Driver

When searching for a table using the Open SQL driver, the expected table names do not appear in the list of tables.

Possible causes could be that you do not have SAP access in your profile either to the table or to the required Business Objects transports. Also, you could be filtering the tables using the Data Explorer option in Crystal Reports.

Crystal Reports in the Real World—Leveraging XML as a Data Source

With the emergence of XML as a data interchange format, many customers wanted to create reports on XML documents. So in Crystal Reports 8.5, a new driver was released that allowed just this scenario. This ODBC driver reads certain types of XML documents. Version XI of Crystal Reports provides the capability to read multiple XML files, most commonly a folder of XML files that have the same schema. When using this driver, you specify either a folder name or a file path to an XML file as described in detail earlier in this chapter. Once connected, XML elements at the first level are represented as fields that you can place on a report.

If you require more flexibility around reading XML files, a good approach to take is to write a COM or Java Data Provider to read the XML. This Data Provider can use one of the many readily available XML parsers to read in the XML and choose exactly what fields to return to Crystal Reports. Listing 15.3 is a sample Visual Basic COM Data Provider that reads in a simple XML file. This method is still used by developers who want to exert very strict control over the XML data that is provided to the report.

Listing 15.3. A COM Data Provider That Reads XML Data