Introducing to reports based on SQL

• Install the iReport tool.
• Execute the iReport tool.

Caution

OpenKM comes with a specific JasperReports Library engine that supports older versions but not newer ones. The Application version compatibility table shows the correspondence between OpenKM and JasperReports Library engine versions.

You can set a specific output version in the iReport tool:

• Click on the Tools menu > Options.
• From iReport > General tab, click on the Compatibility tab.
• Set the Jasper version compatibility.
• Click on the OK button.

Create data source

• Click on the Report data sources button.

• Click on the New button.
• Choose the “Database JDBC connection” option.
• Click on the Next button.
• Set the name.
• Choose the JDBC driver.
• Click on the Text button to check the connection parameters.
• Click on the Save button.

• Check the As default checkbox.
• Click on the Close button.

Testing SQL sample report

Caution

For testing the SQL report sample, you must have OpenKM running.

• Download the SQLReportPattern.jrxml file.
• Open the file.

 

• Click on the Preview tab.
• Set the host value (for example http://localhost:8080/OpenKM).

• Click on the Designer tab.
• Click on  the report query button.

Tip

From this point you can add your jrxml file and register it in your OpenKM under Administration > Reports.

A closer look at the JRXML file

The iReport tool creates the Jasper report file in XML format.

Note

Although the iReport tool does all the work for you in a user-friendly interface, it is a good idea to take a look—at least out of curiosity—at what kind of file is being created.

Query section

<queryString>
  <![CDATA[select * from OKM_USER;]]>
</queryString>

Fields section

<field name="USR_ID" class="java.lang.String"/>
<field name="USR_NAME" class="java.lang.String"/>
<field name="USR_PASSWORD" class="java.lang.String"/>
<field name="USR_EMAIL" class="java.lang.String"/>
<field name="USR_ACTIVE" class="java.lang.String"/>

Remote image section

<imageExpression><![CDATA[$P{host}+"/logo/report"]]></imageExpression>

Using fields in report

<textField isBlankWhenNull="false">
  <reportElement key="textField" x="329" y="0" width="206" height="18" uuid="5e3e2df8-a99b-4ce2-aab9-9adb97bc4dc9"/>
    <textElement>
      <font fontName="Arial"/>
    </textElement>
  <textFieldExpression><![CDATA[$F{USR_EMAIL}]]></textFieldExpression>
</textField>