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
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:
···
On Fri, Nov 1, 2013 at 11:36 AM, Wilson,Randy rwilson@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