Example of iReport based on custom query on DHIS-2 demo site?

Support - Assistance technique
1

We’ve been trying to publish iReports in our DHIS-2 instance using custom queries and parameters, but continue to receive errors. I was hoping to see some examples on the demo site, but they are all based on report tables. Could someone put some examples of iReports based on custom postgres queries so that we can see how to configure connection strings.

The documentation in the user guide is not adequate.

Randy Wilson

2

Hi,
below is an example of an ireport using custom SQL, with report parameters (period and orgunit). I assume you start out with the empty report template you get in DHIS?

Olav

(not sure if attachments work on the user list, so I've put the content of the file at the end of the mail)

We’ve been trying to publish iReports in our DHIS-2 instance using custom queries and parameters, but continue to receive errors. I was hoping to see some examples on the demo site, but they are all based on report tables. Could someone put some examples of iReports based on custom postgres queries so that we can see how to configure connection strings.

The documentation in the user guide is not adequate.

Randy Wilson

_______________________________________________
Mailing list: DHIS 2 Users in Launchpad
Post to : dhis2-users@lists.launchpad.net
Unsubscribe : DHIS 2 Users in Launchpad
More help : ListHelp - Launchpad Help

<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports&quot; xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance&quot; xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd&quot; name="report1" pageWidth="595" pageHeight="842" whenNoDataType="AllSectionsNoDetail" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="df48aa2c-3795-40ea-8bb4-7e8988796c80">
  <property name="ireport.zoom" value="1.0"/>
  <property name="ireport.x" value="17"/>
  <property name="ireport.y" value="0"/>
  <parameter name="periods" class="java.lang.String">
    <defaultValueExpression><![CDATA[46424]]></defaultValueExpression>
  </parameter>
  <parameter name="period_name" class="java.lang.String"/>
  <parameter name="organisationunits" class="java.lang.String"/>
  <parameter name="organisationunit_name" class="java.lang.String">
    <defaultValueExpression><![CDATA["orgunitname"]]></defaultValueExpression>
  </parameter>
  <parameter name="organisationunit_level" class="java.lang.Integer"/>
  <parameter name="organisationunit_level_column" class="java.lang.String">
    <defaultValueExpression><![CDATA["idlevel2"]]></defaultValueExpression>
  </parameter>
  <queryString>
    <![CDATA[SELECT
  patientdatavalue.value,
  count(patientdatavalue.value)
FROM
  public.patientdatavalue,
  public.programstageinstance
WHERE
  programstageinstance.programstageinstanceid = patientdatavalue.programstageinstanceid AND
  patientdatavalue.dataelementid = 46570 AND
  programstageinstance.programinstanceid = 46222 AND
  programstageinstance.executiondate >= (select startdate from period where periodid in (select cast(($P{periods}) as int))) and
  programstageinstance.executiondate <= (select enddate from period where periodid in (select cast(($P{periods}) as int))) and
  programstageinstance.completed = true and
  programstageinstance.organisationunitid in (select organisationunitid from _orgunitstructure
        where
      idlevel5 is not NULL and
      ($P!{organisationunit_level_column}) = (select organisationunitid from organisationunit where name like $P{organisationunit_name}))
group by
  patientdatavalue.value
order by
  count(patientdatavalue.value) desc
limit 50;]]>
  </queryString>
  <field name="value" class="java.lang.String"/>
  <field name="count" class="java.lang.Long"/>
  <background>
    <band splitType="Stretch"/>
  </background>
  <title>
    <band height="84" splitType="Stretch">
      <staticText>
        <reportElement uuid="9a842c2e-cf40-4e12-8582-dde35583b4e9" x="11" y="0" width="532" height="41" forecolor="#184F73"/>
        <textElement textAlignment="Center">
          <font size="24"/>
        </textElement>
        <text><![CDATA[Top 50 Inpatient Morbidity]]></text>
      </staticText>
      <staticText>
        <reportElement uuid="a29131ef-514a-408d-8d71-c600deb16c3f" x="261" y="52" width="24" height="20"/>
        <textElement textAlignment="Center">
          <font size="14"/>
        </textElement>
        <text><![CDATA[-]]></text>
      </staticText>
      <textField>
        <reportElement uuid="be4d738d-f34a-448b-9365-fe5b1ca2b735" x="96" y="52" width="165" height="20"/>
        <textElement textAlignment="Right">
          <font size="14"/>
        </textElement>
        <textFieldExpression><![CDATA[$P{period_name}]]></textFieldExpression>
      </textField>
      <textField>
        <reportElement uuid="e2b2eaab-321d-417d-97e3-7f1e73352a35" x="285" y="52" width="232" height="20"/>
        <textElement>
          <font size="14"/>
        </textElement>
        <textFieldExpression><![CDATA[$P{organisationunit_name}]]></textFieldExpression>
      </textField>
    </band>
  </title>
  <pageHeader>
    <band height="20" splitType="Stretch"/>
  </pageHeader>
  <columnHeader>
    <band height="37" splitType="Stretch">
      <staticText>
        <reportElement uuid="b7338901-0ed1-4753-8a13-749e264b5c3d" x="79" y="16" width="110" height="20"/>
        <textElement verticalAlignment="Middle">
          <font size="10" isBold="true"/>
        </textElement>
        <text><![CDATA[Primary Diagnosis]]></text>
      </staticText>
      <line>
        <reportElement uuid="baf312d4-7855-4df0-8819-c3c074552eef" x="79" y="35" width="428" height="1"/>
      </line>
    </band>
  </columnHeader>
  <detail>
    <band height="20" splitType="Stretch">
      <textField>
        <reportElement uuid="7ad6eadb-abc8-4fba-a779-245c4b125d61" x="79" y="0" width="375" height="20"/>
        <textElement verticalAlignment="Middle"/>
        <textFieldExpression><![CDATA[$F{value}]]></textFieldExpression>
      </textField>
      <textField pattern="###0">
        <reportElement uuid="efa7fc82-f0be-48b6-b8bc-0f98ba8028cf" x="454" y="0" width="53" height="20"/>
        <textElement verticalAlignment="Middle"/>
        <textFieldExpression><![CDATA[$F{count}]]></textFieldExpression>
      </textField>
    </band>
  </detail>
  <columnFooter>
    <band height="16" splitType="Stretch"/>
  </columnFooter>
  <pageFooter>
    <band height="32" splitType="Stretch"/>
  </pageFooter>
  <summary>
    <band splitType="Stretch"/>
  </summary>
</jasperReport>

···

15. feb. 2013 kl. 08:40 skrev "Wilson,Randy" <rwilson@msh.org>:

3

Hi Olav,

Thanks for sending this, I’ve replaced the query with one that is applicable for one of my custom iReports, but still can’t seem to get the report to work. Do you think you could make a generic ireport jrxml file – perhaps listing all orgunits at level 3 sorted by level 1 and level 2.

Then I could run it with fewer modifications in my local instance.

Our issue may be partly due to the fact that we’re using SSL and we need a special connection string;

jdbc:postgresql://lion:5432/healthfinance?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory

Thanks,

Randy

···

From: Olav Poppe [mailto:olav.poppe@me.com]

Sent: Friday, February 15, 2013 9:49 AM

To: Wilson,Randy

Cc: DHIS Users

Subject: Re: [Dhis2-users] Example of iReport based on custom query on DHIS-2 demo site?

Hi,

below is an example of an ireport using custom SQL, with report parameters (period and orgunit). I assume you start out with the empty report template you get in DHIS?

Olav

(not sure if attachments work on the user list, so I’ve put the content of the file at the end of the mail)

  1. feb. 2013 kl. 08:40 skrev “Wilson,Randy” rwilson@msh.org:

We’ve been trying to publish iReports in our DHIS-2 instance using custom queries and parameters, but continue to receive errors. I was hoping to see some examples on the demo site, but they are all based on report tables. Could someone put some examples of iReports based on custom postgres queries so that we can see how to configure connection strings.

The documentation in the user guide is not adequate.

Randy Wilson

Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

<?xml version="1.0" encoding="UTF-8"?> 

        <property name="ireport.zoom" value="1.0"/>

        <property name="ireport.x" value="17"/>

        <property name="ireport.y" value="0"/>

        <parameter name="periods" class="java.lang.String">


        </parameter>

        <parameter name="period_name" class="java.lang.String"/>

        <parameter name="organisationunits" class="java.lang.String"/>

        <parameter name="organisationunit_name" class="java.lang.String">


        </parameter>

        <parameter name="organisationunit_level" class="java.lang.Integer"/>

        <parameter name="organisationunit_level_column" class="java.lang.String">


        </parameter>

        <queryString>
= (select startdate from period where periodid in (select cast(($P{periods}) as int))) and programstageinstance.executiondate <= (select enddate from period where periodid in (select cast(($P{periods}) as int))) and programstageinstance.completed = true and programstageinstance.organisationunitid in (select organisationunitid from _orgunitstructure where idlevel5 is not NULL and ($P!{organisationunit_level_column}) = (select organisationunitid from organisationunit where name like $P{organisationunit_name})) group by patientdatavalue.value order by count(patientdatavalue.value) desc limit 50;]]> 
        </queryString>

        <field name="value" class="java.lang.String"/>

        <field name="count" class="java.lang.Long"/>

        <background>

        </background>

        <title>

                                <staticText>

                                            <reportElement uuid="9a842c2e-cf40-4e12-8582-dde35583b4e9" x="11" y="0" width="532" height="41" forecolor="#184F73"/>

                                            <textElement textAlignment="Center">

                                                        <font size="24"/>

                                            </textElement>

                                            <text><![CDATA[Top 50 Inpatient Morbidity]]></text>

                                </staticText>

                                <staticText>

                                            <reportElement uuid="a29131ef-514a-408d-8d71-c600deb16c3f" x="261" y="52" width="24" height="20"/>

                                            <textElement textAlignment="Center">

                                                        <font size="14"/>

                                            </textElement>

                                            <text><![CDATA[-]]></text>

                                </staticText>

                                <textField>

                                            <reportElement uuid="be4d738d-f34a-448b-9365-fe5b1ca2b735" x="96" y="52" width="165" height="20"/>

                                            <textElement textAlignment="Right">

                                                        <font size="14"/>

                                            </textElement>

                                            <textFieldExpression><![CDATA[$P{period_name}]]></textFieldExpression>

                                </textField>

                                <textField>

                                            <reportElement uuid="e2b2eaab-321d-417d-97e3-7f1e73352a35" x="285" y="52" width="232" height="20"/>

                                            <textElement>

                                                        <font size="14"/>

                                            </textElement>

                                            <textFieldExpression><![CDATA[$P{organisationunit_name}]]></textFieldExpression>

                                </textField>

        </title>

        <pageHeader>

        </pageHeader>

        <columnHeader>

                                <staticText>

                                            <reportElement uuid="b7338901-0ed1-4753-8a13-749e264b5c3d" x="79" y="16" width="110" height="20"/>

                                            <textElement verticalAlignment="Middle">

                                                        <font size="10" isBold="true"/>

                                            </textElement>

                                            <text><![CDATA[Primary Diagnosis]]></text>

                                </staticText>

                                <line>

                                            <reportElement uuid="baf312d4-7855-4df0-8819-c3c074552eef" x="79" y="35" width="428" height="1"/>

                                </line>

        </columnHeader>

        <detail>

                                <textField>

                                            <reportElement uuid="7ad6eadb-abc8-4fba-a779-245c4b125d61" x="79" y="0" width="375" height="20"/>

                                            <textElement verticalAlignment="Middle"/>

                                            <textFieldExpression><![CDATA[$F{value}]]></textFieldExpression>

                                </textField>

                                <textField pattern="###0">

                                            <reportElement uuid="efa7fc82-f0be-48b6-b8bc-0f98ba8028cf" x="454" y="0" width="53" height="20"/>

                                            <textElement verticalAlignment="Middle"/>

                                            <textFieldExpression><![CDATA[$F{count}]]></textFieldExpression>

                                </textField>

        </detail>

        <columnFooter>

        </columnFooter>

        <pageFooter>

        </pageFooter>

        <summary>

        </summary>
4

Hi,
sorry for the delay. Below is a query that will take the org unit parameter and list all the children of that orgunit.

Olav

Hi Olav,

Thanks for sending this, I’ve replaced the query with one that is applicable for one of my custom iReports, but still can’t seem to get the report to work. Do you think you could make a generic ireport jrxml file – perhaps listing all orgunits at level 3 sorted by level 1 and level 2.

Then I could run it with fewer modifications in my local instance.

Our issue may be partly due to the fact that we’re using SSL and we need a special connection string;
jdbc:postgresql://lion:5432/healthfinance?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory

Thanks,

Randy

From: Olav Poppe [mailto:olav.poppe@me.com]
Sent: Friday, February 15, 2013 9:49 AM
To: Wilson,Randy
Cc: DHIS Users
Subject: Re: [Dhis2-users] Example of iReport based on custom query on DHIS-2 demo site?

Hi,
below is an example of an ireport using custom SQL, with report parameters (period and orgunit). I assume you start out with the empty report template you get in DHIS?

Olav

(not sure if attachments work on the user list, so I've put the content of the file at the end of the mail)

We’ve been trying to publish iReports in our DHIS-2 instance using custom queries and parameters, but continue to receive errors. I was hoping to see some examples on the demo site, but they are all based on report tables. Could someone put some examples of iReports based on custom postgres queries so that we can see how to configure connection strings.

The documentation in the user guide is not adequate.

Randy Wilson

_______________________________________________
Mailing list: DHIS 2 Users in Launchpad
Post to : dhis2-users@lists.launchpad.net
Unsubscribe : DHIS 2 Users in Launchpad
More help : ListHelp - Launchpad Help

<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports&quot; xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance&quot; xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd&quot; name="report1" pageWidth="612" pageHeight="792" whenNoDataType="AllSectionsNoDetail" columnWidth="572" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="df48aa2c-3795-40ea-8bb4-7e8988796c80">
  <property name="ireport.zoom" value="1.3310000000000004"/>
  <property name="ireport.x" value="0"/>
  <property name="ireport.y" value="0"/>
  <parameter name="periods" class="java.lang.String"/>
  <parameter name="period_name" class="java.lang.String"/>
  <parameter name="organisationunits" class="java.lang.String"/>
  <parameter name="organisationunit_name" class="java.lang.String">
    <defaultValueExpression><![CDATA["Sierra Leone"]]></defaultValueExpression>
  </parameter>
  <parameter name="organisationunit_level" class="java.lang.Integer"/>
  <parameter name="organisationunit_level_column" class="java.lang.String"/>
  <queryString>
    <![CDATA[select name from organisationunit where parentid in (select organisationunitid from organisationunit where name like $P{organisationunit_name}) order by name asc;]]>
  </queryString>
  <field name="name" class="java.lang.String"/>
  <background>
    <band splitType="Stretch"/>
  </background>
  <title>
    <band height="248" splitType="Stretch">
      <staticText>
        <reportElement uuid="4746abd3-c65d-4a1a-be4d-0be0885a7c59" x="219" y="112" width="122" height="20"/>
        <textElement/>
        <text><![CDATA[List of children orgunits]]></text>
      </staticText>
    </band>
  </title>
  <pageHeader>
    <band height="13" splitType="Stretch"/>
  </pageHeader>
  <columnHeader>
    <band height="22" splitType="Stretch">
      <staticText>
        <reportElement uuid="517ac85f-8df4-4474-9654-bc5323fa9448" x="219" y="0" width="149" height="20"/>
        <textElement verticalAlignment="Middle">
          <font isBold="true"/>
        </textElement>
        <text><![CDATA[name]]></text>
      </staticText>
    </band>
  </columnHeader>
  <detail>
    <band height="21" splitType="Stretch">
      <textField>
        <reportElement uuid="7c6e200f-f2ac-47e6-bac1-19bddded4115" x="219" y="0" width="149" height="20"/>
        <textElement verticalAlignment="Middle"/>
        <textFieldExpression><![CDATA[$F{name}]]></textFieldExpression>
      </textField>
    </band>
  </detail>
  <columnFooter>
    <band height="45" splitType="Stretch"/>
  </columnFooter>
  <pageFooter>
    <band height="42" splitType="Stretch"/>
  </pageFooter>
  <summary>
    <band height="38" splitType="Stretch"/>
  </summary>
</jasperReport>

···

18. feb. 2013 kl. 14:14 skrev "Wilson,Randy" <rwilson@msh.org>:

15. feb. 2013 kl. 08:40 skrev "Wilson,Randy" <rwilson@msh.org>:

5

Hi Randy,

what I typically do when creating iReports using custom queries is to set up a JDBC connection to the DHIS database straight from iReport. You can then use the preview function and preview your reports directly from ireport which simplifies debugging. One can either temporarily open the postgres database for remote connections (put a strong password first!) or install a copy of the database in a local postgres installation on your machine.

regards,

Lars

···

On Fri, Feb 15, 2013 at 8:40 AM, Wilson,Randy rwilson@msh.org wrote:

We’ve been trying to publish iReports in our DHIS-2 instance using custom queries and parameters, but continue to receive errors. I was hoping to see some examples on the demo site, but they are all based on report tables. Could someone put some examples of iReports based on custom postgres queries so that we can see how to configure connection strings.

The documentation in the user guide is not adequate.

Randy Wilson

Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

6

Hi Lars, Randy

I think this is what Randy has done. Remote direct connection to the
database is possible and uses ssl, hence the connection string Randy
has posted above.

But I think the point is as Lars says, this is for development and
debugging. Or if you need/want to share the iReports outside the
context of DHIS2. Once the report is imported into DHIS2 you no
longer need the connection string. That is why you don't see it with
Olav's reports. Just the queries. The connection is made using the
existing connection which DHIS2 will have open to the database.

Bob

···

On 21 February 2013 07:40, Lars Helge Øverland <larshelge@gmail.com> wrote:

Hi Randy,

what I typically do when creating iReports using custom queries is to set up
a JDBC connection to the DHIS database straight from iReport. You can then
use the preview function and preview your reports directly from ireport
which simplifies debugging. One can either temporarily open the postgres
database for remote connections (put a strong password first!) or install a
copy of the database in a local postgres installation on your machine.

regards,

Lars

On Fri, Feb 15, 2013 at 8:40 AM, Wilson,Randy <rwilson@msh.org> wrote:

We’ve been trying to publish iReports in our DHIS-2 instance using custom
queries and parameters, but continue to receive errors. I was hoping to see
some examples on the demo site, but they are all based on report tables.
Could someone put some examples of iReports based on custom postgres queries
so that we can see how to configure connection strings.

The documentation in the user guide is not adequate.

Randy Wilson

_______________________________________________
Mailing list: DHIS 2 Users in Launchpad
Post to : dhis2-users@lists.launchpad.net
Unsubscribe : DHIS 2 Users in Launchpad
More help : ListHelp - Launchpad Help

_______________________________________________
Mailing list: DHIS 2 Users in Launchpad
Post to : dhis2-users@lists.launchpad.net
Unsubscribe : DHIS 2 Users in Launchpad
More help : ListHelp - Launchpad Help