dhis2-users team mailing list archive

[Olav Poppe <olav.poppe@xxxxxx>]

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


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

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