Josef Petrák Reporting

Today I have just one top for all of you who are searching how to work with views in Oracle documentation for as long time as I do. If you have a view over tables in database and you want to know what SQL query is executed you can obtain the query using following query:

SELECT text
FROM all_views
WHERE view_name = '%VIEW_NAME%';

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

Recently I have found an information about the OpenVocab project and I was interested in its introduction of current and planned features. What's it about? The Semantic Web is mainly based on machine-readable data and data interchange. Data must be encoded using a schema (RDFS) or bettern an ontology (OWL). There are various „standards“ like FOAF, DOAP, Dublin Core… But in some cases you require to create own concepts (classes) or attributes. In that case you can either create their description in one of RDF notations (usually RDF/XML), think about URIref patterns and publish, or you can use OpenVocab service.

In short, OpenVocab is a kind of collaborative hosting and authoring tool for creating ontology terms. You can create, share and contribute data in the open system. Data are identified using unified address pattern and concepts are published as Linked Data in various formats – RDF, XHTML, JSON, Turtle. Linked Data means that each URIref (URL address) identifying a term returns HTTP 303 See Other header and redirects to other representation of the concept according to the agent preferences (usually RDF for machines and XHTML for web browsers).

Technically data are stored in the RDF repository Talis Platform and are available via SPARQL. Source codes and project documentation are available at the Google Code. OpenVocab follows the prior vocab.org project, which offered a space and stable domain for various ontologies, but it pushes the idea further. I am very interested if the author will migrate ontologies published at vocab.org into the new system.

In Czech / česky

While working on the Knowledge Engineering Group community website, which is completely implemented on top of RDF-based repository, I faced an issue about incompatibility with SPARQL standard. We used the RDF API for PHP framework, which was so-called "standard" for PHP aplication handling any RDF. Let's describe the use case. We had a list of foaf:Group instances in the repository; some of them had one foaf:name property with value in English language, and some had two foaf:names, one in English, second in Czech langauge. We want to select both names using one query and present them in the XHTML form. The SPARQL select would be like this:

PREFIX foaf: <http://xmlns.com/foaf/0.1/>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
SELECT ?group ?nameEn ?nameCs
WHERE {
       ?group rdf:type foaf:Group .
       ?group foaf:name ?nameEn .
       OPTIONAL { 
              ?group foaf:name ?nameCs . 
              FILTER (lang(?nameCs) = "cs") 
       }
       FILTER (lang(?nameEn) = "en")
}

But, if we executed the query against the RAP database repository, we received incorrect result - it contained only resources having both names. I have been debugging the query for two hours but a friend of mine proved that the query is right. OpenLink Virtuoso service hosted on DBpedia selected the data correctly. Because the RAP project seems to be frozen, we have realized to move our code from RAP to ARC2 framework and expected to run this query and receive correct results. Unfortunately, moving the code to ARC did not solve our trouble. After re-running the script, we got different, but still incorrect results. This forced us to abandon Czech names to be implemented in our website for now and to search the source of trouble. A brief answer gave us W3C SPARQL Implementation Coverage report. RAP, nor ARC does not support OPTIONAL variables for 100%. In fact, from the most commonly used RDF frameworks only Jena and Sesame2 have complete support for SPARQL. We will use ARC in future development and hope the support for SPARQL will be completed one time...

In Czech / Česky

Hello, all! It would be unfair to not introduce myself. My name is Josef Petrak, and I have been blogging in my mother language for six years already. The website zapisky.info has already became known among the readers. In case, you want to see my biography or interest, go through the jspetrak.name homepage (yeah, this is still incomplete, but will be extended soon). I am mainly interested in the development of the Semantic Web applications – RDF, SPARQL, and all related stuff are on my focus. Since November 2008, I have been working for Sun as a globalization engineer in Prague Globalization Center. Except that, you can share my interests via Twitter community service, or browse my flickr photo gallery. I suppose to writte English–written posts here, mainly translations from my mother-language-writen website, but some of them will be unique here, like this one, of course…

Anyway, if you are Mac OS X user and would like to have a cute desktop twitter client, I have one tip for you. Filemon and Jiri Pisa has recently developed and released the Jetwit – simple application to post status messages on Twitter and Facebook, and set as a Skype status on one click. It is really worth installing!