dhis2-users team mailing list archive
-
dhis2-users team
-
Mailing list archive
-
Message #01807
Re: Example of iReport based on custom query on DHIS-2 demo site?
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@xxxxxx]
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)
15. feb. 2013 kl. 08:40 skrev "Wilson,Randy" <rwilson@xxxxxxx<mailto:rwilson@xxxxxxx>>:
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@xxxxxxxxxxxxxxxxxxx<mailto:dhis2-users@xxxxxxxxxxxxxxxxxxx>
Unsubscribe : https://launchpad.net/~dhis2-users
More help : https://help.launchpad.net/ListHelp
<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports"; xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"; xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd"; 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>
Follow ups
References
