28.8 Designing SQL based standard reports

A standard report might be based on SQL queries. This is useful when you need to access multiple tables in the DHIS2 database and do custom selects and joins.

- This step is optional, but handy when you need to debug your reports and when you have direct access to the database you want to use. Click on the “report datasources” button, “New”, “Database JDBC connection” and click “next”. In this window you can give you connection a name and select the JDBC driver. PostgreSQL and MySQL should come included in your iReport. Then enter the JDBC connection URL, username and password. The last three refers to your database and can be retrieved from your DHIS2 configuration file (hibernate.properties). Click “save”. You have now connected iReport to your database.

- Go to standard reports and click “add new”, then “get report template”. Open this template in iReport. This template contains a series of report parameters which can be used to create dynamic SQL statements. These parameters will be substituted based on the report parameters which we will later select and include in the standard report. The parameters are:

These parameters can be included in SQL statements using the $P\!{periods} syntax, where “periods” represents the parameter.

- To create a SQL query in iReport, click on the “report query” button. Write or paste your query into the textarea. An example SQL query using parameters which will create a report displaying raw data values at the fourth level in the org unit hierarchy is:

    select district.name as district, chiefdom.name as chiefdom, ou.name as facility,
    bcg.value as bcg, yellowfever.value as yellowfever, measles.value as  measles
    from organisationunit ou
    left outer join _orgunitstructure ous
      on (ou.organisationunitid=ous.organisationunitid)
    left outer join organisationunit district
      on (ous.idlevel2=district.organisationunitid)
    left outer join organisationunit chiefdom
      on (ous.idlevel3=chiefdom.organisationunitid)
    left outer join (
      select sourceid, sum(cast(value as double precision)) as value
      from datavalue
      where dataelementid=359706
      and periodid=$P!{periods}
      group by sourceid) as bcg on bcg.sourceid=ou.organisationunitid
    left outer join (
      select sourceid, sum(cast(value as double precision)) as value
      from datavalue
      where dataelementid=35
      and periodid=$P!{periods}
      group by sourceid) as yellowfever on yellowfever.sourceid=ou.organisationunitid
    where ous.level=4
    and ous.$P!{organisationunit_level_column}=$P!{organisationunits}
    order by district.name, chiefdom.name, ou.name;

Notice how all parameters are used in the query, along with SQL joins of resource tables in the DHIS2 database.

- Finally, back in the add new report screen, we click on “Use JDBC data source”. This enables you to select any relative period and report parameters for your report. Relative periods are relative to today’s date. Report parameters will cause a prompt during report creation and makes it possible to dynamically select organisation units and periods to use for your report during runtime. For the example above, we must select “reporting month” under relative periods and both “reporting month” and “organisation unit” under report parameters. Click save. This will redirect you to the list of reports, where you can click the green “create” icon next to your report to render it.