Definitive guide for creating reports. Creation of sub reports. Calling of stored procedure. Step of parameters. (Guía definitiva para la creación de reportes. Creación de Sub Reportes. Call Stored Procedure. Paso de los parámetros)

JasperReport version 6.3.1

Java 1.8

intellij Idea 14

Maven 3.2.3

Ireport 5.6

I have been creating a report that met certain aspects in the execution. This report requires calling segments of PL codes looking for insurance quote information. For this work it was necessary to make calls to different stored procedures that return the data. I searched for many examples, but none provided effective or complete information for what I was looking for, so I started to create my own code. It was not easy but after 4 days of tests and frustrations here they are the results.

• First of all, start by opening Ireport. I used the latest version available 5.6. Open a new report. An important point when creating the reports is consider that iReport uses the "Groovy" language by default. Edit the xml code of the report and remove the "Language = Groovy" attribute that the report has setted. This will give you problems later. Among the properties of the iReport there is the possibility of setting the java language by default. But it is important to check on each occasion to create a new report.
• Set the "classpath" tab in the oracle driver to use. I set ojdbc6.jar (It's undifferent the version). 

• Then, create a database JDBC conecction. This case it's a oracle data base.

• Once the connection is established and tested. Go to your database administrator and proceed to create the Stored Procedure.

• I created a Stored Procedure that receives two parameters, a first parameter called codcli (the example was designed in Spanish) that I will use later to pass a value. The other parameter is of type sys_refcursor, which uses the report to access the created stored procedure. Go back to Ireport. Given that you already created your Stored Procedure, you know well the fields that your code returns. Create as many fields in iReport as you return the select statement. I have three fields, NOMBRE, CODIGO, EMAIL. If you wish, you can put them in the created report.

• A very important point. Create a parameter called ORACLE_REF_CURSOR (or you prefer to call it). This parameter must be of type java.sql.ResultSet. You can set it in the "Parameter Class" attribute in the creation of the attribute. Also create the other parameter (if your code requires it) to be used later from the class to pass a value that will use the stored procedure to search the data.

• Next step, create set call to stored procedure. Find the option to create "reports query". There, you will write the call to the stored procedure, setting the required fields as parameter (the ORACLE_REF_CURSOR parameter is mandatory). Set "query lenguage" as "p/sql. If you are going to use the other paramentro, it is important to mention that you should put a "!" So that there are no problems in the invocation from the class. Ex: $ P! {Codec}. You'd could see an error "ERROR: SQL Problems: Invalid column type: 2000", this is almost normal in new versions of iReport. Click the "read fields" button. You should be able to see the fields that are returned in the Select statement of the stored procedure. The "Refresh Preview Data" button should display the results.
• Save and compile your report. Maybe I could read some warning (which should not) but it may go unnoticed. In the "Preview" button of iReport, the report can be generated. If you have problems, if you do not see the data. Try regenerating the fields. Delete the fields that I created previously and write them again (when you set the call in "report query" the "Read Fields" button creates them again). But up to this point there should be no problems.

• In the end your first report should look very similar to this. This will be the report that will be used as the sub report of a principal.

Very well, if you only need to run the report in an external and individual way, you can get here, but if you are developing a program or a web application as in my case, keep moving forward. Here's how to make the report run from a java program for a web application:

• You must create a new report, which one will be used as the main report from where the sub reports necessary will be created to invoke the other reports you have created already. Create a sub report and put it in the "detail" band of the report. If you later need to use several sub reports, you must create group bands for this job.