Category: Jasper Report

In this tutorial, I am going to talk about how to create a Jasper Report with My SQL Database and jasper studio.

To complete this tutorial you need to have basic SQL knowledge and the knowledge of how to download and install the software.

first, download and install the below software.

1. download and install Mysql workbench and Mysql database
2. download and install Jasper Studio

Creating the data base

Make sure your Mysql Database is up and running.

connect to the Mysql database with My SQL Work Bench and create a simple database and a sample Employee table. you can use the below scripts for that. or if you already have a database you can use it to complete this tutorial.

create database testJasper;
use testJasper;
CREATE TABLE Employee (
id int NOT NULL,
Name varchar(150) DEFAULT NULL,
Address varchar(30) DEFAULT NULL,
Department varchar(100) DEFAULT NULL,
Salary varchar(100) DEFAULT NULL,
dob date DEFAULT NULL,
PRIMARY KEY (id)
);
INSERT INTO testjasper.employee
(id,
Name,
Address,
Department,
Salary,
dob)
VALUES
(2,
‘Test Employee 2’,
‘No 101,1st Lane,Jasper Street’,
‘Software Development’,
‘6000’,
‘1999-08-25 00:00:00.000000’);

INSERT INTO testjasper.employee
(id,
Name,
Address,
Department,
Salary,
dob)
VALUES
(1,
‘Test Employee 1’,
‘No 99,1st Lane,Jasper Street’,
‘Software Development’,
‘5000’,
‘2020-08-25 00:00:00.000000’);

now with these two records in the Database we will create a simple report with Jasper Studio.

Design the report

1. open Jasper Studio and start a new report. file -> new -> jasper report
2. new report creation Wizard will start and you can select a template your desire, but for this tutorial, we will go with a blank report
3. Provide a name and click next. then Database connection Wizard appears. this is the main important place where you will connect a report with the Database, you can even change parameters and fields according to design any time.
4. here select your ODBC data source previously created to your database. to create an ODBC data for the MySql database please refer to my previous article on how to create an ODBC data source with the Mysql database.
5. you can select multiple tables or use a SQL query according to your choice. here I have used all columns from the employee table and click next.
6. The query executes and a list of return columns will be displayed there you have to select what are the columns required to add in to the report.
7. drag and drop the fields from the left side panel to the details section of the report. there are multiple sections in a report each has specific purpose and way of working we will use them in our next tutorials, you can find out models about them from jasper report user guide.
8. once fields are arranged click on the preview section in the design view to compile and run the report. real-time data from the DB will be displayed in the report.

in my next tutorials, I will explain how to add summary fields and format different data types and how we can create Jasper reports from Java applications.