Create Report using Jaspersoft Studio

In this post I will show you how to create a report with grouping using Jaspersoft Studio. Here I am using Wamp server for development. First you can create a database using phpMyAdmin. We will create a database named ‘report’. Next we will create three tables named student,subject and mark. Fisrt table student will have two coloumns id(pk) and name.

CREATE TABLE IF NOT EXISTS `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

The second table subject also will have two coloumns id(pk) and name.

CREATE TABLE IF NOT EXISTS `subject` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

The third table mark will have four coloumns named id(pk),student_id(fk),subject_id(fk),mark.

CREATE TABLE IF NOT EXISTS `mark` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`student_id` int(11) NOT NULL,
`subject_id` int(11) NOT NULL,
`mark` decimal(10,0) NOT NULL,
PRIMARY KEY (`id`),
KEY `student_id` (`student_id`,`subject_id`),
KEY `subject_id` (`subject_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1

ALTER TABLE `mark`
ADD CONSTRAINT `mark_ibfk_2` FOREIGN KEY (`subject_id`) REFERENCES `subject` (`id`) ON UPDATE CASCADE,
ADD CONSTRAINT `mark_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`) ON UPDATE CASCADE;

Now we will insert some values into these tables. First we will insert the values into table student.

INSERT INTO `report`.`student` (`id`, `name`) VALUES (NULL, 'suresh'), (NULL, 'vineeth');
Next we will insert values into table subject.
INSERT INTO `report`.`subject` (`id`, `name`) VALUES (NULL, 'english'), (NULL, 'maths'), (NULL, 'physics'), (NULL, 'chemistry'), (NULL, 'biology');

Next we will insert values into table mark.

INSERT INTO `report`.`mark` (`id`, `student_id`, `subject_id`, `mark`) VALUES (NULL, '1', '1', '52'), (NULL, '1', '2', '60'), (NULL, '1', '3', '54'), (NULL, '1', '4', '72'), (NULL, '1', '5', '65');

INSERT INTO `report`.`mark` (`id`, `student_id`, `subject_id`, `mark`) VALUES (NULL, '2', '1', '66'), (NULL, '2', '2', '57'), (NULL, '2', '3', '72'), (NULL, '2', '4', '75'), (NULL, '2', '5', '60');

So now we have created the tables and inserted the values. Next we will write the query to get the values from the tables.

SELECT s.id as student_id,s.name as student_name,sb.id as subject_id,sb.name as subject_name,m.mark from mark m
left join student as s on m.student_id=s.id
left join subject as sb on m.subject_id=sb.id;

Now we can create the report using Jaspersoft Studio 6.1.1. Select File->New->Jasper Report. From the dialg box select Blank A4 and give it a name for example marks.Then select data adapter as One Empty Record and click finish.

Jaspersoft Studio

 

Jaspersoft Studio

 

Now we will create a Data Adapter for the report in the Jaspersoft Studio. On the left side of the report you can see the Repositor tab, in that right click on the Data Adapters and select Create Data Adapter.In the dialog box give it a name for example “Student Marks Data Adapter”. Then select the JDBC driver “com.mysql.jdbc.Driver”, JDBC Url “jdbc:mysql://localhost/report”, User Name “root” and Password leave it as blank.

Jaspersoft Studio

 

Jaspersoft Studio

Now we have to download the driver for MySQL JDBC Driver at http://dev.mysql.com/downloads/connector/j/. Run and install it, then in the Data Adapter dialog select the “Driver Classpath” tab and add the jar file you just installed. Default location will be at C:\Program Files\MySQL\MySQL Connector J\mysql-connector-java-5.1.36-bin.jar. Now click on the “Test Connection” button. If connection is successful you can close the window.

Jaspersoft Studio

 

There is a button on the top to write the query for selecting data from the Data Adapter. You can select the Data Adapter and write the SQL query to get the data from the database.Then click on read fields button and close the window.

Jaspersoft Studio

 

Jaspersoft Studio

Now we can create the group in the report. For that you can right click on the report name on the Outline tab located on the bottom left corner of the report.Then select create Group.In the dialog box that appears select “Create group from a report object” and then click on “student_id” and click next. In the next screen check the add group header and click finish.

Jaspersoft Studio

Jaspersoft Studio

Jaspersoft Studio

In the report you can find different bands named “Title”, “Page Header”, “Column Header”, “Group1 Group Header”, “Detail1”, “Column Footer”, “Page Footer”, “Summary”. You can delete the bands that are not necessary by right clicking and selecting delete.You can retain the bands Title”, “Page Header”,”Group1 Group Header” and “Detail1”.

 

From the Palette tab on the right side you can drag and drop elements into the report. First drag and drop the Static Text field into the Title and change text as “Student Marks”. You can apply styles
using the tool bar on the top. Similarly you can drag and drop the fields from the Outline tab located at the bottom left corner. Drag and drop the student_name field into the group header and drop the subject_name and mark fields into the detail1 band. You can style the report according to your needs. Then click on the preview tab on the bottom to run the report.

Jaspersoft Studio

Jaspersoft Studio

Jaspersoft Studio

In the next post

I will show you how to intergrate the report with php using Jasper Server.