Sample development scenario

1. Initial Setup : Setting up the CRDB_JavaServer.ini file
2. Creating a new Web project with JavaServer Pages™ (JSP™) technology
3. Embedding Crystal Reports in the JSP page and running it on the Server

The example we'll review is based on IBM® Rational® Application Developer v6.0.1.1 (henceforth, Application Developer) with the default Crystal v10 features installed. It also uses an IBM® DB2® Universal Database v8.1 that contains the SAMPLE database against which the reports will be generated. For more information on Crystal Reports-based use cases, refer to Application Developer Help Contents (Help > Help Contents > Developing Web Applications > Crystal Reports > Creating Reports). Refer to IBM trial software in the Resources section to download trial versions of these products.

Step1: Setting up the CRDB_JavaServer.ini file

To set up this file, perform the following steps.

1. In the Application Developer workspace, switch to the Data perspective (Window > Open Perspective > Data)
2. Right click in the Database Explorer view and select New Connection
3. Select Choose a Database Manager and JDBC Driver as shown in Figure1
   
   Figure 1. New Database Connection dialog box
   
   
   
4. Click Next.
5. In the New Database Connection dialog box, enter the values shown in Figure 2, then click Test Connection.
   
   Figure 2. Specify connection parameters dialog box
   
   
   
6. If the Test Connection is successful, click OK (Figure 3).
   
   Figure 3. Test Connection Successful
   
   
   If you get any JDBC connection problems, please refer to your database product’s documentation. Also, a DB2 Infocenter link has been provided in the Resources section. To check if you can connect to your database without using Application Developer's Data perspective (by running a Java program that tests JDBC connectivity from the command line), refer to JDBC Test. This JDBC Test link can help troubleshoot JDBC connectivity for most database vendors.
   
   Please also refer to Business Objects' Supported Platforms to check if your development platform (that is, database, operating system, and so on) is supported by Business Objects. The Application Developer support page link in Resources section mentions that IBM® z/OS® and iSeries™ platforms are not supported for Crystal Reports by Business Objects.
   
   
7. Keeping the New Database Connection Wizard open, now open the CRDB_JavaServer.ini file from the following path:
   C:\Program Files\Common Files\Crystal Decisions\2.5\bin\CRDB_JavaServer.ini
   
   Modify the following values in the CRDB_JavaServer.ini as shown in Figure 4:
   
   
   • PATH: Point to the directory containing java.exe (*preferrably from jdk1.4.2 installation)(such as "c:\program files\IBM\Rational\SDP\6.0\runtimes\base_v6\java\bin")
   • CLASSPATH: Copy the Class Location from the New Database Connection wizard and append it to the existing CLASSPATH. If your class location path has spaces in it (such as "Program Files"), it is recommended that you include the class location path for each .jar in quotes ("c:\program files\IBM\SQLLIB\java\db2jcc.jar").
   • IORFileLocation: Provide a qualified path for any temp folder on your local file system(* you need to have READ/WRITE permissons on it).
   • JDBCURL: Copy the Connection URL value from the "New Database Connection" wizard and replace the existing value.
   • JDBCUserName: Enter db2admin (or your database username). You can change it later in the Crystal Report jdbc(jndi) Connection Wizard (Figure 12).
   • JDBCDriverName: Copy the JDBC Driver class from the New Database Connection Wizard and replace the existing value.
   
   Figure 4. CRDBJavaServer.ini file containing JDBC connection settings
   
   
   Note: Using Notepad is preferred over any other text file editor since other editors have been known to insert some invisible formatting characters that might cause the error as shown in Figure 11 below, even if the relevant parameters contain correct values.
8. Save the CRDB_JavaSever.ini file and press Cancel on the Database Connection Wizard.

Step2: Creating a new Web project with JSP containing Crystal Report

To create a new Web project, perform the following steps.

1. In your Application Developer workspace, create a new Dynamic Web project by selecting File > New > Dynamic Web Project.
2. Select the settings shown in Figure 5.
   
   Figure 5: New Dynamic Web Project dialog box
   
   
   Note: For this example, please make sure you select 2.3 as Servlet version and WebSphere Application Server v5.1 as Target Server as shown in Figure 5.
   
   
3. Click Next, and then Select Crystal Reports features in the Features dialog box, as shown in Figure 6.
   
   Figure 6: Web Project Features dialog box
   
   
   
4. Click Finish.
5. In your Web project, right-click the WebContent Folder and select New > Crystal Report.
6. Select the settings shown in Figure 7 in the New Crystal Report Wizard.
   
   Figure 7: New Crystal Report Wizard
   
   
   
7. Click Finish.
8. In the Crystal Reports Gallery dialog box, select the Report Wizard option (Figure 8).
   
   Figure 8: Crystal Reports Gallery
   
   
   
9. Click OK.
10. In the Standard Report Creation Wizard dialog box, click Create New Connection > JDBC(JNDI) (figure 9).
    
    Figure 9: Standard Report Creation Wizard
    
    
    
11. If your settings in the CRDB_JavaServer.ini file are configured correctly, then the following JDBC(JNDI) Settings dialog box (Figure 10) will pop up. The Connection URL and Database Classname should already be filled out with the correct values from the CRDB_JavaServer.ini file.
12. Click Next.
    
    Figure 10: JDBC(JNDI) Settings dialog box
    
    
    If, instead of the JDBC(JNDI) settings box, you get the following error (Figure 11), then refer to Resolve Java Server startup failure error.
    
    Figure 11: JDBC(JNDI) Settings dialog box showing error
    
    
    For a more detailed guide to setting up your CRDB_JavaServer.ini file, refer to the CRDB_JavaServer.ini settings guide.
    
    
13. Provide your DB2 User ID and Password in the Connection information dialog box (Figure 12).
    
    Figure 12: Connection Information dialog box
    
    
    Note: This dialog box picks up the username that you provided in the CRDB_JavaServer.ini file in Figure 4. You can change it here to any other database username if you desire.
    
    
14. Click Finish.
15. In the Data dialog box shown in Figure 13, from the available tables in your schema, select the table for which you want to generate the report. For this example scenario, the Department table was selected.
    
    Figure 13: Data dialog box
    
    
    
16. Click Next.
17. In the Fields dialog box (Figure 14), select the fields from the table that you want to display in your report.
    
    Figure 14: Fields dialog box
    
    
    
18. Click Next.
19. In the Grouping dialog box (Figure15), select your grouping preference (for this example, accept the default value).
    
    Figure 15: Grouping dialog box
    
    
    
20. Click Next.
21. In the Record Selection dialog box (Figure 16), select your record selection preference (for this example, accept the default value).
    
    Figure 16: Record Selection dialog box
    
    
    
22. Click Next.
23. In the Report Style dialog box (Figure 17), select your report style preference (for this example, accept the default value).
    
    Figure 17: Report Style dialog box
    
    
    
24. Click Finish and save the changes in the report file (select File > Save).

Step3: Embedding Crystal Report in the JSP and running it on the Server

To embed the Crystal Report, perform the following steps.

1. Create a new JSP page in your web project.
2. Embed the Crystal Report that you just created into the JSP Page by dragging the Crystal Report Page Viewer component from the Palette view in Web perspective, as shown in Figure 18.
   
   Figure 18: Dragging the Report Page Viewer component into the JSP Page
   
   
   
3. This will open the New Crystal Report Page Viewer dialog box (Figure 19).
   
   Figure 19: New Crystal Report Page Viewer Dialog Box
   
   
   
4. Click Browse and select the .rpt file (the actual report file) in your Web project's WebContent folder.
5. Click Finish.
6. Save the changes in the JSP (File > Save).
   
7. To run the page on the IBM® WebSphere® Test Environment v5.1, right-click the JSP page in the Project Explorer view and select Run > Run on Server to see the JSP containing the Crystal Report.
   Figure 20 shows the report in the browser.
   
   Figure 20: View JSP containing the Crystal Report in the browser
   
   
   

Some exceptions may occur during this process at run time. The following bullets describe how to handle these exceptions.

• If you get a JDBC Driver runtime error (Figure 21) during this process, do the following.
  
  Figure 21: JDBC Driver runtime error
  
  
  Copy the DB2 Universal JDBC Driver .jar files ( db2jcc.jar, db2jcc_license_cu.jar and db2jcc_license_cisuz.jar) from the <DB2-Install-directory>\SQLLIB\java directory to your <WebSphere-Application-Serverv51-Install-directory>\lib\ext directory. (Note: If you are using WebSphere Test Environment v5.1 instead of standalone WebSphere Application Server v5.1, <WebSphere-Application-Serverv51-Install-directory> = <RAD-install-directory>\runtimes\base_v51.) After you have copied the .jar files, restart the server by right clicking the server in the Servers view and selecting Restart > Start.
• If you get the Database Logon page (Figure 22), provide your DB2 username and password and click OK
  
  Figure 22: Database Login page
  
  
  
• If you get the following error message in the Console view, then refer to How to resolve log4j errors.
  

  [2/14/06 17:38:36:435 EST] 00000031 SystemErr R log4j:ERROR setFile(null,true) call failed. [2/14/06 17:38:36:435 EST] 00000031 SystemErr R java.io.FileNotFoundException: C:\Documents and Settings\Administrator\crystal\jpe.log (The system cannot find the path specified) at java.io.FileOutputStream.openAppend(Native Method) at java.io.FileOutputStream.init(FileOutputStream.java:199) at java.io.FileOutputStream.init(FileOutputStream.java:124) at org.apache.log4j.FileAppender.setFile(FileAppender.java:272) at org.apache.log4j.RollingFileAppender.setFile(RollingFileAppender.java:156)

  
  
  

Back to top

What's next...

This concludes Part 1 of this 5-part series about embedding Crystal Reports in Web applications. In this article, you learned how to embed Crystal Reports in your Web applications. More specifically, you learned how to embed Crystal reports that use JDBC connections both at design time and run time. In the next article (Part2), you will learn how to embed Crystal Reports that use JDBC connections at design time, and automatically switch to JNDI connections at runtime. By using JNDI connections at runtime, you can obtain the benefits of connection pooling and additional application server-provided functionality.

Resources

Learn

• DB2 Infocenter : IBM DB2 UDB v8.x Infocenter.
  
  
• JDBC Test :Command line based JDBC Connectivity Test.
  
  
• Business Objects' Supported Platforms : List of Business Objects' supported platforms
  
  
• RAD support page : RAD support page that mentions the specific IBM platforms that are not supported for Crystal Reports by Business Objects.
  
  
• How to resolve log4j errors: RAD support page that provides a workaround for this issue.
  
  
• Resolve Java Server startup failure error : Business Objects support page that provides the workaround for this issue.
  
  
• CRDB_JavaServer.ini settings guide : Business Objects support page that provides the complete information about configuring JNDI and JDBC connectivity in Crystal Reports 10
  
  
• Stay current with developerWorks technical events and Webcasts.
  
  
• IBM Rational Application Developer product page: Find technical documentation, how-to articles, education, downloads, and product information about Rational Application Developer.
  
  

Get products and technologies

• Build your next development project with IBM trial software, available for download directly from developerWorks.
  
  
• Order the no-charge SEK , a two-DVD set containing the latest IBM trial software for Linux or Windows from DB2®, Lotus®, Rational®, Tivoli®, and WebSphere®.
  
  
• IBM Rational Application Developer: Download a trial version from developerWorks.
  
  

Discuss

• Participate in the discussion forum.
  
  
• Rational Software Architect, Software Modeler, Application Developer and Web Developer forum: Ask questions about Rational Application Developer.
  
  

About the author

Robin holds a Masters Degree in Computer Science from California State University, Sacramento. When he wrote this article, he worked as a Support Engineer for RAD and WSAD tools at IBM Toronto Labs. He now works as a Sr. Programmer/Analyst with a major telecommunications company (Rogers Communications Inc.) in Toronto.

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Sample development scenario
• What's next...
• Resources
• About the author
• Comments

Next steps from IBM

• Try: Rational Application Developer for WebSphere Software
• Demo: Build, test and deploy JAX-WS Web services
• Webcast: Webcast: Develop innovative applications efficiently using the right application foundation in today's economy
• Briefing: Software development conference
• Buy: Rational Application Developer for WebSphere Software

My developerWorks community

Dig deeper into Rational on developerWorks

Try Rational System Architect

See how this fully integrated collection of models and documents can help you better align your IT systems and infrastructure to simplify business processes

Special offers