dhis2-users team mailing list archive

[Olav Poppe <olav.poppe@xxxxxx>]

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>:

> 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
> 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>