Introducing to reports based on SQL

• Install iReport tool.
• Execute iReport tool.

Caution

OpenKM comes with an specific JasperReports Library engine what supports older version but now newer. At Application version compatibility table you have correspondence list between OpenKM and JasperReports Library engine versions.

You can set specific output version from iReport tool:

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

Create datasource

• Click on Report datasources button.

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

• Check as default checkbox.
• Click on Close button.

Testing SQL sample report

Caution

For testing the SQL Report sample you must have an OpenKM running.

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

 

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

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

Tip

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

Closer look into JRXML file

The iReport tool is creating for you the jasper report file in XML format.

Note

Although the iReport tool is doing all work for you in friendly user interface, is a good idea for you to take a look - at least for curiousity - about 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>