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. See the Application version compatibility table for a correspondence list 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 the iReport > General tab, click on the Compatibility tab.
• Set the Jasper version compatibility.
• Click the OK button.

Create a datasource

• Click the Report datasources button.

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

• Check the Default checkbox.
• Click the Close button.

Testing SQL sample report

Caution

To test the SQL report sample, you must have OpenKM running.

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

 

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

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

Tip

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

A closer look at the JRXML file

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

Note

Although the iReport tool does all the work for you in a friendly user 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>