Before you start

WebSphere Commerce analytics are best presented in a visual format using a reporting tool that is standardized. Ideally, you develop these reports easily in an integrated development environment (IDE), such as WebSphere Commerce and Rational Application Developer V6. Currently, the WebSphere Commerce reporting framework is used out of the box for both business intelligence and operational reports. This reporting framework displays data in tabular, not graphical, format, which is not ideal for business users who need to visualize results they are trying to analyze.

In this tutorial, you will choose an operational report named Order Status Report and use the embedded Crystal Report tools to implement the report using the IDE. The embedded Crystal Report tools have a Rational Application Developer plug-in so that you can finish designing, developing, and testing the report in the WebSphere Commerce Toolkit.

This tutorial shows how to implement the order status report for WebSphere Commerce using Crystal Report tools, and outlines briefly the strengths of each reporting tool. It also explains how you can use these same steps to extend existing reports or create new reports.

WebSphere Commerce development environment overview

WebSphere Commerce development environment includes tools you need to create customized code and perform Web development tasks. It builds on top of Rational Application Developer, which is the core development environment from IBM. It helps you optimize and simplify Java™ 2 Platform Enterprise Edition (J2EE) and Web services development by offering best practices, templates, code generation, and the most comprehensive development environment in its class. This integrated development environment includes integrated support for Java components, enterprise beans, servlets, JSP files, HTML, XML, and Web services all in one development environment.

WebSphere Commerce report framework overview

The WebSphere Commerce reporting framework provides generic, customizable reporting functionality for a WebSphere Commerce site. The framework generates reports using data contained in the production database, and displays the reports in real time. The framework consists of a generic controller command, a data bean, and a generic view that displays the result. You can customize the framework by adding valid SQL queries, and defining JSP files used to request and display the generated reports.

Reports are accessible from the WebSphere Commerce Accelerator. Consequently, each report requires a number of associated assets. While the report itself consists of data represented in a tabular format, the underlying assets consist of the report identifier, an SQL query, access control elements, and so on. The report request launches a controller command on the server. The controller command calls tasks to set the generic view, unless the report specifies a particular view. The command also sets a number of required variables, and returns this data to populate the ReportDataBean in the target JSP file. Access control for the reports is set on the views which request (input) and display (output) the report. The results returned from the database are stored in the data bean as a vector of hashtable. Finally, the JSP file displays the report. If the report is empty, the JSP file displays a generic text string instead.

Each report has an input view and an output view. The purpose of all input JSP files is the same. They share the same look and feel; they request different input criteria from a shared input widget pool. On the other hand, output views have common structure and formats. Based on the nature of the reports, all reusable parts of the report JSP files are built as sharable components.

The report framework also supports multiple types of relational databases (RDB) products. Developers can specify the SQL that was used to generate report data with a common style, then the report framework composes the SQL according to a certain database type, and outputs the final SQL statement that is executed. Figure 1 illustrates how the report framework outputs a report.

As Figure 1 shows, the user enters query criteria into the report input view. The report control center gets the input parameter from the input view, and reads, analyzes, and formats the xml file that defined the current report content. The report control center generates the SQL statement according to the user's input and current database type, and then pushes this SQL to the database. Lastly, the report control center fills the result returned by the database into the report data bean and parses it to the output view. The output view displays the content of the report in tabular format.

Crystal report overview

Crystal Reports is a popular report tool. It is designed to work with multiple data source and helps users to analyze and interpret important information. Crystal Reports make it easy for users to create simple reports, and it also has comprehensive visible tools users need to produce complex or specialized reports.

In Rational Application Developer, you can use embedded reporting to build Crystal Reports into Java applications. The embedded reporting scenario uses the Java Reporting Component (JRC) and Crystal Reports Viewers Java SDK to enable users to view and export reports in a Web browser. They provide the functionality that creates and customizes a report viewer object, processes the report, and then renders the report in DHTML. The JRC keeps report processing completely internal to the Java application server. The JRC allows you to process Crystal Reports report (.rpt) files within the application itself, without having to rely on external report servers. It has the following advantages:

• Provides a 100% Java reporting solution.
• Dynamically pushes runtime data to a report.
• Programmatically changes the database location for a report.
• Exports Crystal Reports report (.rpt) files to RTF and PDF formats.

Back to top

Converting to Crystal Reports overview

This section discusses how to convert a WebSphere Commerce operational report into a Crystal Report. Each WebSphere Commerce report has a corresponding xml file to identify the SQL that was executed to query data for the report. To support multiple database production as well as provide flexibility, this SQL, which we call "rough SQL", cannot be executed directly because it includes some self-defined functions such as "$Date()" or "$Time()". These self-defined functions are replaced by real database functions in the report framework according to the current type of database. The rough SQL also includes some variables that are replaced by real values according to the user's input. Before fetching data from the database, the report framework replaces all variables and self-defined functions and makes it as a final SQL that is executed in the database.

To convert the WebSphere Commerce report into a Crystal Report, you need to create a rtp file for the Crystal Report using the rough SQL from the WebSphere Commerce report. This make them have the same columns and report format. Then you need to fetch the final SQL from the report framework and execute the query, and set the result set from the database as the new data source of your Crystal Report. This makes the Crystal Report have the same dynamic content with the WebSphere Commerce report.

By default, the report framework does not provide the public interface to return the final SQL, so you need to customize the report framework and add the interface to return the final SQL statement.

You also need to execute the final SQL statement from the report framework and get the report content data from the database. This is done by one of the new data beans, CrystalReportDataBean.

Finally, you need to set the result set returned from CrystalReportDataBean as the new data source for the Crystal Report. You can implement this by calling the API of JRC (Java Reporting Component). The code looks like this:

DatabaseController.setDataSource(java.sql.ResultSet data, 
java.lang. String oldTableAlias, java.lang.String newTableName)

This method updates the data source used by the Crystal Report with the specified java.sql.ResultSet data set. Figure 1 shows a dynamic list that is used to display the list of defined subsidies.

Figure 2 shows how the WebSphere Commerce report framework and WebSphere Commerce tools framework work with embedded Crystal Report tools, and how they convert WebSphere Commerce reports into Crystal Reports.

The blue arrows demonstrate the flow of how WebSphere Commerce reports were generated as described in the WebSphere Commerce report framework overview section.

The green arrows demonstrate the flow of how embedded Crystal Report tools get necessary information, such as the final SQL from the report framework, and generate the corresponding Crystal Reports. CrystalReportDataBean gets the final SQL statement from the Report Control Center, and then executes the query in the database. You get the result set in the JSP, which was used to display the Crystal Report, and you set it as a new data source for the current report. The tools framework then shows the Crystal Report in WebSphere Commerce Accelerator.

1 of 10 | Next

Comments

Back to top

Table of contents

• Before you start
• Converting an operational report to a Crystal Report
• Configuring embedded Crystal Reports in Rational Application Developer
• Creating the Crystal Report template in Rational Application Developer
• Developing a Crystal Report to display a WebSphere Commerce operational report
• Displaying the Crystal Report in the tools framework
• Customizing the report framework
• Conclusion
• Resources
• About the authors

---

1 of 10 | Next

Next steps from IBM

• Try: Rational Application Developer for WebSphere Software
• Buy: Rational Application Developer for WebSphere Software

Dig deeper into WebSphere on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.