Hi Randy, someone else might be more clever but I can't think of way to do that.
Would it instead be an option to try to make the report a bit more
flexible, so that it will display top 10 diseases no matter which level the
selected org unit is from?
The report template provides several org unit-related parameters, which
will give enough information to produce that report. See below, or try to
load the template directly into iReport (get the template from the add new
standard report screen).
For instance, you can join with the _orgunitstructure resource table and
base the join on the column name which you can get from the
"organisationunit_uid_level_column" parameter, something like this:
---
select ..., sum(dv.value) from aggregateddatavalue dv
inner join _organisationunitstructure ous using(organisationunitid)
where "$P!{organisationunit_uid_level_column}" = $P!{organisationunits} ...
---
This way, since we join on the organisationunitid of the
_organisationunitstructure but do the where clause on the column
representing the level of which the user-selected org unit is from, we
include all org units in its sub-tree in the. Hence, the need for
restricting org units goes away and you might get a more flexible tool for
analysis.
---
<parameter name="organisationunits" class="java.lang.String"/>
<parameter name="organisationunits_uid" class="java.lang.String"/>
<parameter name="organisationunit_name" class="java.lang.String"/>
<parameter name="organisationunit_level" class="java.lang.Integer"/>
<parameter name="organisationunit_level_column" class="java.lang.String"/>
<parameter name="organisationunit_uid_level_column"
class="java.lang.String"/>
---
regards,
Lars
On Fri, Nov 1, 2013 at 11:36 AM, Wilson,Randy <rwil...@msh.org> wrote:
> We have developed some rather complex SQL queries that feed into
> iReports to display the top 10 causes of OPD visits, hospitalization and
> deaths by district by quarter. I have successfully integrated the
> parameters for period and for organization_name, but the data are only
> grouped by district, so if someone selects a different level in the
> hierarchy (national, provincial, etc…) it will fail.
>
>
>
> As with periods where there is a checkbox where I can indicate “reporting
> quarter”, I’d like to be able to require that users only select at a
> specific level of the hierarchy. Has anyone figured out how to do this
> (we’re still using version 2.12).
>
>
>
> *Randy Wilson*
>
> Senior HMIS and Data Use Advisor, Ministry of Health/Rwanda
>
> Integrated Health Systems Strengthening Project
>
> Management Sciences for Health, Inc. (MSH)
>
> BP 371
> Kigali, Rwanda
> +250-788308835
>
> Skype Name (wilsonrandy_us)
>
> www.msh.org<http://www.msh.org/?utm_source=2012-10-15+MSH+internal+announce+email+signature&utm_campaign=iemailsig&utm_medium=email>
>
>
> [image: Description:
> http://ih.constantcontact.com/fs017/1102482301888/img/438.gif]<http://www.msh.org/?utm_source=2012-10-15+MSH+internal+announce+email+signature&utm_campaign=iemailsig&utm_medium=email>
>
> *Stronger health systems. Greater health impact.*
>
> [image: Description: Follow MSH on
> Facebook]<http://www.facebook.com/ManagementSciencesForHealth>[image:
> Description: Follow MSH on
> Twitter]<https://twitter.com/#%21/MSHHealthImpact>[image:
> Description: YouTube] <http://www.youtube.com/mshhealthimpact>Follow us
> on Facebook and Twitter
>
>
>
> _______________________________________________
> 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
>
>
<<image001.gif>>
<<image004.png>>
<<image002.gif>>
<<image003.png>>
_______________________________________________ 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
