Wednesday, 24 April 2013

Deploying a Report to JasperReports Server



Now we have developed the report in iReport where we can run the report and, now we are going to deploy the report in JasperReport Server.
To deploy the report in JasperReport Server, start the browser and type
                                    http://localhost:8080/jasperserver/login.html
Default port for tomcat is 8080, so please confirm you are also using the same port.
Now enter User ID and Password (default User ID and Password is jasperadmin/jasperadmin)
Once you logged in, create a folder called “Charts” (just for tutorial, you can give any name) and right click on the Chart folder and select “Add Resources”  à“JasperReport”




Click on “Data Source” tab and select the data source from repository         (/datasources/Sample_Database__HSQLDB_test_)

And submit the report. Now you should be able to run the report.
 

How to develop a bar chart in iReport ?


JasperReports supports a large number of built-in charts created using the popular open source library JFreeChart. Chart types include 2D & 3D Pie, 2D & 3D Bar, 2D & 3D Stacked Bar, Line, Area, Bubble, Gantt, Thermometer and Meter.
In this post, we will learn how to create a chart in iReport and will use “Sample Database (HSQLDB test)”.
We start creating a blank report based on the following SQL.
SELECT COUNT(*) orders,
             YEAR(ORDERDATE) year,
            MONTHNAME(ORDERDATE) month,
            CONCAT(MONTHNAME(ORDERDATE) ,
            RIGHT(year(ORDERDATE),2))monthyear
FROM orders
GROUP BY YEAR(ORDERDATE), MONTH(ORDERDATE)
ORDER BY YEAR(ORDERDATE), MONTH(ORDERDATE)
The query returns the amount of orders placed in each month in the several years. Open the query dialog, paste the query (be sure the JasperReports Sample db is the active connection) and read the fields from the query.
From the palette drag the chart element inside the title band and select the Bar 3D chart.
Step 1:- Select “Main report dataset” and press “Finish”.

Step 2:- Right click the chart and select Chart data. This will pop up the Chart data dialog.


Select the “Details” tab and add a new category.
Set as Series expression $F{YEAR}), as category expression the $F{MONTHYEAR} and as value expression the number of orders $F{ORDERS} as shown in the following figure.

                      





Click “Preview” tab.
Just to change the property of graph because you may not able to see the month name in x-axis, to change the “Label Rotation” select the graph and go to property tab and change the “Label Rotation” to -50.

­­

­­­­­

Creating a new Report in iReport


As we have already setup the database (MySQL) connection in iReport, now I am going to create my first report in iReport.
To create a new report –
11)     Go to “File” then Select “New…” from the menu.
You should get a dialog box like this



22)     Select the report template, which you want to use for the report, here we are going to choose “Coffee” template. 
33)     Click on “Launch Report Wizard” .
44)     Name the Report Name and location where you want to save.

55)     Select the Connection / Data Sources from drop down list, I am using the Sample Database for the report.
    For the query either you can use “Desin query” button to design the query or you can write the query.

SELECT
     ORDERS."ORDERID" AS ORDERS_ORDERID,
     ORDERS."CUSTOMERID" AS ORDERS_CUSTOMERID,
     ORDERS."SHIPCITY" AS ORDERS_SHIPCITY,
     ORDERS."SHIPCOUNTRY" AS ORDERS_SHIPCOUNTRY
FROM
     ORDERS

66)     Click “Next” button and here you can select the filed, which you want to use in report.


77)     Click “Next” button and select Group1 if you want to group the data.


88)     Click “Next” and “Finish”.
99)     You can see the “Preview” the report, you can also change the title of the report from “Designer” tab.




Connecting iReport to MS SQL Server


iReport is a report designer for JasperReport server where you can develop the reports , to develop the reports you need to fetch the data from different  data sources like MySQL, Microsoft SQL Server, Oracle etc.
                     iReport supports many different Data source types to provide data for its reports.  One of these Data sources is "Database JDBC connection" which is used to write reports against MySQL.
To add a Data Source click here
                        
      

This will display a list of Datasources . Now to create a datasource click on New button. Select “Database JDBC connection” then press Next button.
                                                                      
Now fill this dailog box :-
Name:-  MySQL daatabase connection (You can give any name)
JDBC Driver:- Select data dabse driver from drop down list.
Server Address:- localhost:3306 (localhost is the host name and  3306 is default MySQL port)
Database:- Database name which you want to connect.
Click on wizard button
Username and Password, Test the datasource and Save.
                                                                  
Now the datasource is created and you can used in iReport for creating reports. To fetch the data from datasource you have to write SQL queries. You can also use Query Designer to write the queries.

Connecting to SQL Server:- Follow the same steps but you may need to download JDBC driver for SQL Server and then add in iReport.

Steps to set the Classpath for iReport:
1) Go to "Tools / Options" menu and select iReport
2) Verify that database JDBC driver is present:
         - Choose "Classpath" tab
         - Click "Add JAR" and then locate the SQL Server driver from drop down list
         - Click "OK"

3) Click "Connections / Datasources" icon in toolbar
5) Click "New" and set up a "Database JDBC Connection" using driver from step 2
6) Click "Test" to verify the data source works correctly
7) Click "Save" to save the data source


In the same way you can connect to any datasource like ORACLE.