Tutorial on running Crystal Reports with BigSQL in Eclipse

1 Photo E1380908457353

1. Pre Requsites to run this tutorial:

a) BigSQL Tutorial

The BigSQL tutorial is required to be followed till the first three steps from the following link http://www.bigsql.org/se/tutorial.jsp
Step 1 : Creating example.customer_history in HIVE and loading values.
Step 2 : Creating example.customer_history as foreign table in Postgres
Step 3 : Verifying example.customer_history

b) Eclipse Standard version http://www.eclipse.org/downloads/ 

2. Things required to be downloaded

a) Download Crystal Reports Manual Installation from the URL :
http://downloads.businessobjects.com/akdlm/crystalreportsforeclipse/2_0/cr4e_2.0.15.zip

b) Goto the following link to download Postgres JDBC Driver
http://jdbc.postgresql.org/download/
Get the latest version of the driver file .

3. Creating a view on top of example.customer_history :

a) Verify if BIGSQL is running

$ ./bigsql status
#################################################
# BIGSQL:- your BIGSQL version
# TIME: current time
# HOST: your hostname
# JAVA: path to your JAVA_HOME
# OS: your OS
#################################################
## Checking critical ports ################
Postgres port 5432 is busy.
ZooKeeper port 2181 is busy.
HDFS port 50070 is busy.
HBase port 60010 is busy.
Hive port 9083 is busy.
Tomcat port 8080 is busy.

b) Run the psql instance from bigsql package

$ psql

postgres=# Create view example.view_customer_history
AS
SELECT  *
FROM example.customer_history;

CREATE VIEW

4. Setting up Eclipse Plugin for Crystal Reports

a) Browse to the path where you downloaded the Crystal Report package

$ unzip cr4e_2.0.15.zip -d crystalreports
$ cp -R crystalreports/plugins/ <your-eclipse-installation-directory>/plugins/

b) Run an instance of Eclipse .

5. Creating a Report through Eclipse

a) Creating a new project with report

  • File → New → Project → Crystal Report Web Project
    • Enter Name : BigSQL and press NEXT
    • Make sure you have checked “Include a Report” while creating the project.
  • Change the perspective to SAP Crystal Reports

b) Create a new Database Connection

  • Under Data Source Explorer , right click on NEW
  • Enter name : BigSQL , Select PostgresSQL and press next
  • Select / Add JDBC postgres driver
  • Fill the details as below

Database : postgres
Url : jdbc:postgresql://localhost:5432/postgres
Username : postgres
Password : password

  • Select Next and press Finish to see the created Data Source .

c) Adding custom SQL to report

  • Create a SQL file

File → New → Other → SQL Development → SQL File → Next

    • Enter File name : BigSQL
    • Select parent folder as “BigSQL”
    • Database server type : postgres_8.x
    • Connection Profile Name : BigSQL
    • Database Name : postgres
  • Open the created SQL file and enter the query as below.

SELECT hist_id,h_date,h_amount
 FROM view_customer_history
Order by 1 Limit 10;

  • Select type,name and database and make sure the status is Connected

  • Right click on the query window and select Crystal Reports → Add to existing reports → CrystalReport1.rpt[BigSQL/WebContent/CrystalReport1.rpt] → OK
  • Drag and drop the elements of data query (hist_id , h_date , h_amount ) from field explorer to the report Layout tab under report Body section.

  • Build and Save the report

6. Publishing the report

  • Export the created report to WAR form

File → Export → Web/WAR file

  • Move the exported WAR file to the following path /bigsql-9.3rc1-42/tomcat/webapps/

( make sure Tomcat is running in the BigSQL package )

  • The WAR file will explode to create a folder , e.g BigSQL , that will contain a *.jsp report file . Access the jsp file in the browser .eg  http://localhost:8080/BigSQL/CrystalReport1-viewer.jsp

  1. Denis LussierDenis Lussier10-04-2013

    Great job Umair. I haven’t used Crystal Reports in years. I didn’t realize they ship now as an Eclipse plug-in.

Leave a Reply

Please type the characters of this captcha image in the input box

Please write the answer to the math question of this captcha image in the input box

/* ]]> */