Josef Petrák Reporting

Any report implemented in JasperReport / designed in iReport editor may contain only one source query to get data — defined in <queryString> element. In some case you need to obtain data from two or more queries. In such case is having one report not sufficient — to insert other reports into main template there are subreports available. Simple connect other JRXML file, share connection, parameters, … When deploying on JasperServer, the usage of subreports is slightly more complicated because of setting path to other template.

I have introduced how to work with JasperServer in a previous tutorial. We will need to report files to illustrate subreport usage: the main file MainReport.jrxml which generates tabular data and other report file, SubReport.jrxml, which shows one line with a value from database. Each of reports has its own parameters, settings and it is possible to run it independendly in editor. We will deploy them on server from iReport using JS plugin — select a folder to deploy the file, right-click on it and deploy the main report using Add > Report Unit, respectively subreport using Add > JRXML Document. We will not run the second file, but include in main report.

When having both templates deployed, open the main JRXML file file. In order to insert the second template use the Subreport button from toolbar and somewhere in report, e.g. in <title> section, create a space to render it. A wizard will open. Select Just create the subreport element option and finish the wizard; we will set its attributes in properties: in the Subreport tab we have to setup database connection. In the Connection/Data Source Expression select menu we will choose Use connection expression and as a expression we will set $P{REPORT_CONNECTION}, which will share the connection from main report to the subreport. Another important tab is the Subreport (Other) where you can set the path to the subreport template. Set java.lang.String as a Subreport Expression Class and put absolute path to the template into Subreport Expression. The pseudo–protocol "repo" is important as well! If you have the subreport template deployed on JasperServer into "(Root) / Subreports" folder, the path should be (include quotes as it is a Java String expression):

"repo:/Subreports/SubReport.jrxml"

Now you should see the subreport content directly in main report.

Sooner or later, while developing a JasperServer–hosted reports, you will require parameteres and input controls which allow you to select more then one value. Usually there is a need for an input control, which allows either single value, single value with an option "ALL", or multiple values. Of course, the input controls are populated from database as I described in previous write-up.

Today I will describe how to create an input control which allows to select multiple values. First of all you need to define a parameter. In iReport's editor panel Document structure right–click on Parameters and select Add… > Parameter. Datatype java.util.Collection is most important setting.

In JasperServer we will define appropriate input control which allows us to select multiple values. Having a source Query in JasperServer defined, we will select Add > Input Control in iReport's JS–Plugin 3.0 panel, select Multi–Select Query type and fill in other values as for Single–Select Query. We will link the input control to the Report Unit.

Last and most important thing is to use the parameter in report. Because there might be various count of parameter values, we will use WHERE table.attr IN(…) clause to restrict the query. Ordinary single–value parameters are encoded using syntax $P{ParameterName}. For multi-value parameters there is a special syntax – $X{IN, table.attr, ParameterName} which generates the query condition. You can use NOTIN insted of IN in case you would like to generate the NOT IN(…) clause.

The multi-select input control will show as a selectbox with multiple lines displayed and you can select multiple values as well.

Following tutorial should give you a short overview how to create reports using JasperReports. Will discuss infrastructure, editor and guide you through basic steps to create, deploy and run reports.

Discover the tools

While creating a report you need several tools. First of all, you must have a SQL query created. Using DbVisualizer or SQL*Plus console for Oracle you may develop a query which will extract data necessary for the report. It is absolutely right to tune the query outside the report engine. If you are ready to create a report, you have to use a iReport editor - download iReport Classic 3.0.0 (not any other version) from the project page. Using this WYSIWYG report editor you can create and deploy a report. To deploy a report, you need to know where to find or install on your own an instance of JasperServer and have administrator access into it.

Authoring the report in iReport

In order to create a report, open the iReport and select File > New Document. Here you can give your report a name and select page format. It is better to use landscape page format in case you will have a lot of columns and want to have all in one row. Let's start... We have created a new document. We will use a dummy SQL query to get some report data:

SELECT 1 AS DATA_ID, 'A name' AS DATA_NAME 
FROM dual

Using Data > Report Query menu you can add the query into report. The report requires to specify a field for any row retrieved from the query. You can add fields using View > View Field dialog, or in the Document Structure explorer where right-clicking on Fields and selecting Add > Field you can start to specify one new field. The Field Name must conform to column name in the query, e.g. DATA_ID and DATA_NAME in our example. Consider the best Field Class Type for the field value. Of course, a Description is optional.

When you setup the source query and define field which map to columns in the result dataset, you can put the fields into the report. Drug'n'drop them from the Document Structure explorer into the detail part in the report. To define some column headers, use F icon for Static Text, draw a text field, fill in the text and you may format the text as well.

Don't forget to save your report as a temporal file before we deploy it, e.g. to /tmp or so.

Deploying and running a report

JasperServer is an environment implemented on top of Apache Tomcat container which hosts, manages and runs reports. You can manage the server using JS plugin 3.0 inside the iReport editor. You just need have administrator account. After you setup JasperServer connection and log in, you may deploy your newly created report. We will work in the /Tutorial folder of the internal JS structure.

Right-click on the Tutorial folder and select Add > Report Unit, a dialogue will be shown. Fill in a Name and a Label, and move to next step. Here you define the JRXML template file. You can select anyone deployed in the JasperServer but we will select the file that we have created previously and which we have opened in the iReport. We will select Locally Defined and using Get source from current opened report button we fill the path of the opened report. Last step requires to select a Data Source. We suppose to have one defined in the JasperServer. Thus we select From the repository and find the appropriate one in the select. For this dummy report it does not matter which one in case it works. Finish the dialogue and you have the report deployed.

Now we are going to run the report and get result. You need to login into the JasperServer using your preferred browser. Now navigate using the Folders tree explorer into the folder /Tutorial. Here you can find your Dummy Report. Clicking on report name you run the generating and the result is shown on the screen. You are able to download the report in various formats such as PDF, XLS or RTF.

How to edit a deployed report

If you have the report opened in the iReport, you just make the changes and save. Otherwise you need to double-click to the report in JS plugin which will result into expanding the structure, right click on Main.jrxml and select Edit JRXML. The template file will download and open in iReport. After you have changes done and save, right click on report in JS plugin, select Properties, Main Report and Data Sources, in Locally Defined fill the text field using Current Report button and Save Report Unit. After that you can see in JasperServer that the report changed.

If you have solved an issue of reporting, you have probably found and used or at least investigated the JasperReports. This framework offers flexible template tool based on defined XML format, which you design using WYSIWYG graphical editor iReport from the same vendor. Various output formats are available – PDF and XLS are the most interesting ones.

Last week I was working on some reports with data in Oracle database. It was very helpful that we have a JasperServer installed - it servers for hosting, management, scheduling of the reports, and managing the generated reports. The iReport editor contains a pre-installed plugin for server management. It requires only location and credentials to logon, will load content into the editor widget and you can manipulate anything from the server content, or edit the templates directly in the editor. My issue was a bit complicated because there were input parameters required, e.g. select an invoice state or restrict the time interval. I was searching some solution for a day or longer, so I am going to describe, how to create a resusable input control components with value options loaded from database at JasperServer.

You must have input parameters defined in the report template. The input control name must be equal to the name of the input parameter! If you have completed the report template, you can define input controls. To fill the component with data, you need a Query. After right click at a folder in JS-plugin select Add > Query. General tab requires to fill Name (the component ID) and Label (component label name). At the Query tab you define source to obtain data. You have to select the query language (e.q. SQL, EJBQL, HQL or XPath), select one of defined data connections (e.q. to database) and define the query.

Having the Query created, you can create an input control. Use Add > Input Control dialog; one of the attributes worth mentioning is Type, which you should set to Single Select Query, then find the query at the JasperServeru. Value Column defines value column name. Visible Query Columns defines columns, which will appear visible in the select input control.

After you have all necessary components created, open the Report Unit structure, and link each of them via right-click and Link an Existing Input Control. Now you have complete report, select input controls with right value options and after selecting them a report is generated.

In Czech / česky