16.4. Standard reports

16.4.1. What is a standard report?

A standard report is a manually designed report that presents data in a manually specified layout. Standard reports can be based either on report tables or SQL queries. Both approaches are described in the following sections. The main advantage of using report tables is that of simplicity - no special development skills are required. In cases where you have special requirements or need to utilize additional parts of the DHIS2 database you might want to use a SQL based standard report. In any case you will be able to utilize report parameters in order to create dynamic reports. The following guide will use the report table approach, while the SQL approach is covered towards the end.

16.4.2. Designing Standard reports in iReport

Jasper iReport Designer is a tool for creating reports that can be used as Standard Reports in DHIS2. The tool allows for the creation of standard report templates that can easily be exported from DHIS2 with up to date data. The process of creating reports involves four major steps:

  1. A report table must be created in DHIS2 with the indicators/data elements/datasets to be used in the report.

  2. You have to run the report table and download the design file (Click the "Download as JRXML" button).

  3. Open the downloaded .jrxml file using the free software Jasper iReport Designer to edit the layout of the report.

  4. The edited report can then be uploaded to DHIS2 to be used as a standard report.

If you want to preview your report during the design in iReport, you actually have to upload your file to DHIS2 to see how it looks.

These four steps will be describe in detail in the coming sections. In general, when you are making standard reports you should have a clear idea of how it should look before you even make the report table, as how the report table is designed has implications for how the report can be formatted in iReport. For example, what crosstab dimensions are selected in the report table has consequences for what crosstabs are available for the standard report, and it has consequences for what types of charts you can make. Download and open the design file

Note: If you have not created a report table yet, you have to do so. See section "How to create report tables" to do so.

Locate your desired report table and run it by clicking the green circle with a white arrow inside. When the report is shown, click the "Download as JRXML" button to download the design file. Then open that file in the Jasper iReport Designer software. Editing the report

You are now ready to edit the layout of the report. The main iReport window consists of a "Report Inspector" to the left, the report document in the middle, a "Palette" area on the upper right hand side and a "Properties" area on the lower right hand side. The "Report Inspector" are used for selecting and examining the various properties of the report, and when selecting an item in the inspector, the "Properties" panel changes to display properties relating to the selection. The "Palette" is used for adding various elements, e.g. text boxes, images and charts to the document.

Overview of the main iReport window

Note: If you cannot see the Palette or Properties sidebar, you can enable them from the menu item called "Window" on the menu bar.

The iReport document is divided into seven main bands, divided by layout separators (the blue lines). These lines are used to decide how big each of the areas should be on the report.

The areas all have different purposes:

  • Title - area for the title of the report

  • Page header - area for the page header

  • Column header - area for column headers (for the table)

  • Detail 1 - area where the actual report data will be placed

  • Column footer - area to make footer of the table

  • Page footer - area for the page footer

  • Summary - elements in this area will be placed at the end of the report

By default you will see that only the Title, Column Header and the Detail 1 bands have data. For most reports this is OK. The Title band is suitable for a title and e.g. a chart. Data fields entered into the Detail 1 area will be iterated over to create a table. For example, if a field called "dataelementname" is placed in the Detail 1 band, all data elements in the report table will be listed here. We'll come back to data fields management just a little below.

The unused bands in the report are shrinked to add more space for your report data. You can however increase/decrease the band height as you like. There are two ways to do that. The first way is simply to drag the blue band-line as shown below.

Drag the blue lines to change the band heights

The other way to adjust the band height is to select a band in the "Report Inspector", and then adjust the "Band height" value in the "Detail 1 - properties" area in the lower right corner.

Adjust the size of layout areas by changing the Band height property

As the fields are already present on the report, you probably don't want to do anything than just fix the layout and drag fields around. You can also resize the fields by dragging the side, top or bottom lines. If you want to change the text in the column headers, you simply double click the field and change the text.

To add the a field to the table, we simply drag it to the Detail 1 band from the "Report Inspector". The column header will be added automatically.

By double clicking the box, the text can be edited. The format of the text, such as size, font and alignment, can be adjusted with the tools above the document.

Double click to edit the text, and change its appearance

NOTE: Fields starting with "$F" present values that are retrieved from the database every time the report is run. The values here will vary, so do not change these fields unless you want a static value here! Text

There are two types of text in iReport: «Text labels» and «Text fields» (data fields). They work in different ways, and should be used for different purposes. The main point is that text fields are just placeholders that will be filled with the correct text from the report table when the report is run, while text labels will stay the way they are when the report is run. Static text

Static text are text plain text labels that can be edited normally. There are two ways to edit text labels:

  • By double clicking in the text box

  • By using the Static text properties in the Properties panel

Click to edit text label Text fields

Text fields are formulas that will be filled from the report table when the report is run. Unlike static text, these can not be edited in a normal way. However, they can be manipulated in various ways to ensure that the desired output will be produced. There are three ways to edit the text fields:

  • By right clicking on the text box and selecting Edit expression

  • By double clicking the text field (not recommended, as this will not bring up the expression editor)

  • By using the Text field properties in the Properties panel

Click to open Expression editor

Click to open Expression editor

Text fields can represent either numbers or text, so that they can be used both for showing for example names of district or for numeric values. It is therefore important the Expression class, seen in the Text field properties matches the Text field expression. For the default text fields in the .jrxml file downloaded from DHIS2 this is not a problem, but it is important when making new text fields. The two most important Expression classes are java.lang.Double for numbers and java.lang.String for text. Example

For example, let us say you have a quarterly report where you would like to add a new column with the yearly total. You therefore add a new Static text field to the column header band, and a Text field to the details band in. By default, new Text fields are set to java.lang.String (text). However, the yearly total column will be filled with numbers. We therefore have to change the Expression class for the new text field to java.lang.Double:

Click to show expression class options

Choose desired class

When we edit the text field expression, we see the Expression editor window with all the available columns from the report table. We can see here that each of these are marked with what type they are - text or number. What we need to make sure of is therefore that the expression class we choose for the text field matches the actual expression.

Expression editor Filtering the table rows

In the default table exported from DHIS2, there are some rows that it might be better to leave out of the table, and some that it would be preferable to have at the end. For example, when making a table based on a report table with the «parent organisation unit» parameter, the default table might have a row with the national level somewhere in between all the regions. In iReport, this can be changed so that the «parent organisation unit» appears at the bottom of the table. This involves two steps that will be explained below. Note that this will not work where there is only one organisation units, and it is therefore most useful when using the «parent organisation unit» or «grand parent organisation unit» parameters in the report table. Hiding the «parameter organisation unit» from the table

We exclude the "parameter organisation unit" from the table by using a property in the Details band called "Print when expression". To set a Print when expression, start by selecting the Detail band in the Report inspector, then edit the Print when expression in the properties panel.

Select Detail 1 in the Report Inspector

Open the Expression Editor by clicking next to Print When Expression

The Expression editor window should now appear. What we must do is to create an expression that checks if the row being generated is the row with the organisation unit given as a parameter. The report table contains a column that we can use for this called organisation_unit_is_parent. To exclude the row with the parameter organisation unit, double click on organisation_unit_is_parent in the list to copy it to the expression area, then add .equals("No") at the end so that the code is:


This tells the report engine to only print table rows where the organisation unit is not the parent organisation unit.

Code to skip parent organisation unit Putting the "param organisation unit" at the bottom of the table

Instead of removing the "param organisation unit" from the table entirely, it is also possible to put it at the bottom (or top) of the table. This is done by using the sort functionality explained in the next section, and choosing to sort first by "organisation_unit_is_parent". Other sorting options can be added in addition to this, for example to make a list where the param organisation unit is at the bottom of the table, with the other organisation units listed alphabetically above it. Hiding other rows

Using the expression editor it is also possible to exclude other rows from the table, in addition to the parent organisation unit as was explained above. In Ghana, for example, all regions have a «fake district» which is the name of the region in square brackets. This can also be excluded from the table using the Print when expression that was introduced above. To to this, follow the instructions above to bring up the Expression editor window. Then, we use Java expressions to test whether or not the row should be hidden. Example - removing rows with organisation units starting with [

Example - removing rows with organisation units starting with [

($F{organisationunitname}.charAt( 0 ) != '[')

This makes the report skip any rows where the first character of the organisation unit name is [.

It is also possible to combine several of these expressions. To do this we put the expressions in a parenthesis with the two characters && in between. For example, to make a table that leaves both organisation units whose name starts with [ and the parent organisation unit, we can use the following expression:

($F{organisationunitname}.charAt( 0 ) != '[')&&$F{organisation_unit_is_parent}.equals("No")

Combining expressions in the Expression editor Sorting

Often you will be making reports where the first column is organisation unit names. However, it can be a problem that the list of organisation units are not sorted alphabetically. This can be fixed in iReport through a few simple steps.

In the report inspector, right click on the name of the report (by default this is dpt) and select Edit query.

Choose Edit Query

A Report query window will appear. Click on the Sort options button.

Choose Sort Options...

A Sorting window as show below will appear. Here, we can add our sorting options. Click the Add field button. Another small window will show up, with a drop down menu where you can choose Sort by organisationunitname to have the table sorted alphabetically by name.

Choose Add

Choose sort parameter

Click OK - Close - OK to close the three windows. The table should now be sorted. Changing indicator/data element names

By default, the reports from DHIS2 uses the short names for indicators and data elements in reports and charts. In some cases these are not always very meaningful for third parties, but with some work they can be given custom names through iReport. This is useful for example if you are making a report with indicators as rows and periods as column, or for charts with indicators.

Shortnames - before and after

To change the names of an indicator or data element, we have to edit its «expression» or formula, for example by right clicking the text box and choosing Edit expression to bring up the Expression editor.

Choose Edit Expression

Next, we have to insert some Java code. In the following example, we will be replacing the shortname of three indicators with their proper names. The code searches for the shortname, and then replaces it with a proper name.

Expression editor, showing replacing shortnames

($F{indicatorname}.equals("Bed Util All")) ? "Bed Utilisation - All Wards"


($F{indicatorname}.equals("Bed Util Mat")) ? "Bed Utilisation - Maternity"


($F{indicatorname}.equals("Bed Util Ped")) ? "Bed Utilisation - Paediatric"



From this, we can see a pattern that is reusable for more general cases.

  • For each indicator or data element we want to change the name for, we need one line

  • Each line is separated by a colon :

  • We finish the expression with a «regular» line

Each line has the same format, where the red text is the shortname, the blue text is what we want to insert instead.

Code for replacing shortnames. Shortname in red, new name in blue

The same expressions can be used for example when having indicator names along the category axis of a chart. Adding horizontal totals

By using the expression editor, it is possible to add a column to the table with totals for each row. In the following example, we will make a table with three months as columns as well as a column with the totals for the three months.

We start by dragging a text label into the table header and changing its text to "Total", and dragging a text field into the details row.

As was discussed in the section on "Text field", we have to change the properties of the new text field so that it can display numbers. To do this, change the "Expressions Class" in the properties panel to "java.lang.Double".

Right click the text field and choose "Edit Expression". This will bring up the "Expressions editor". As the expression, we want to sum up all the columns. In this case we have three value expressions we want to sum up: "September", "October 2010", "November 2010". The name of these fields will vary depending on the crosstab dimension you have chosen in the report table. In our case, the expression we make is "$f{September}+$f{October 2010}+$f{November 2010}":

Each row of the table have a totals column to the right. Groups of tables

There are cases when it can be useful to have several tables in one report. This can be done using Report groups. Using this functionality, one can for example create a report one table for each indicator, or one table of each organisation unit. In the following, we will go through the steps needed to make a report with three indicators, each represented in one table. It is important that the report table does not crosstab on indicators when we want to make groups of tables based on indicators.

In our example, the .jrxml file downloaded from DHIS2 will by default have one column for organisation unit and on for indicators (assuming we have chosen periods as the only crosstab dimension). We start by removing the indicator column, since this in not needed in our case, and realign the other fields to fit the report.

Delete column

Next, we create out Report group. Go to the report inspector, right click on the report name (dpt is the default) and choose Add Report Group.

Add Report Group

A window will appear, with a report group wizard. Select a name for the group, in this case we choose «Indicator». In the drop down menu, we can select what columns in the report table we want the groups to be based on. So, if we wanted one table for each organisation unit, we would choose organisation unit name as the report object to group according to. However, since we are grouping by indicators in this example, we choose indicatorname. Then click next.

Choose grouping parameter - here, indicators

The next step is to select whether or not we want a separate Group header and Group footer band for each report group. In this case, we choose to include both. Click Finish, and the group bands should appear in the report.

Add group header/footer

If you upload and run the report, it will now create one table for each indicator. However, it will not look very good as there will be no header row over each table - only one header at the top of each page. Also, there is no indication as to which table is showing which indicator. In the following, we will fix this.

Instead of having the title row in the column header, we can instead move it to the Group header. This will make the heading show up above each individual table. Furthermore, we can add a heading to each table with the name of the indicator.

Move the column headers from the Column header band to the Indicator group header band.

Move header row to column header

Next, add a text field to the Indicator group heading band, and edit it’s expression to display the indicator name.

Add text field to use as heading

Edit text field expression to show indicator name

The report should now have three tables, one for each indicator. Each table will have a heading with the name of the indicator, and also a table header row.

Example of finished report table Sorting and grouping

When using grouping, some precautions must be taken with regards to sorting. Notably, when adding sorting parameters, whatever parameter is used as basis for the grouping must come first. Thus if you are grouping the report by indicator, and want sort the organisation units alphabetically, you have to choose to sort first by indicator, then by organisation unit name as shown below. For instructions on how to add sorting, see the sorting section above.

Sorting by indicator and organisation unit name Charts

By default, a 3D bar chart is included in the .jrxml file that is downloaded from DHIS 2. This is set up so that only data from the «parameter organisation unit» (often the parent or grand parent) is used. Usually, this is a good solution. Since it is the default, we will start by looking at bar charts, before looking at line charts. Bar charts

Example bar chart

Bar charts are the default chart type in DHIS2. In this section, we will look at how to make a bar charts like the one above, comparing the value of one indicator in several districts. To edit the default chart in iReport, right click on it and choose Chart data.

Choose Edit Chart Data

A window will appear. By default, the Filter expression is filled in so that only data for the parent organisation unit will be displayed. If for some reason you do not want this, simply delete the text in the text box. In this case we do NOT want the filter, as we are making a chart showing a comparison across districts. To continue, click the details tab.

Select the Details tab

Under details, you see the list of series for the chart. By default, one series is created per crosstab column. In this case, we are looking at data for one indicator for the whole of 2010, for a number of districts. The indicator is along the crosstab dimension.

List of data series

To make changes to a series, select it and click modify. Another window will appear where there are four areas that can be edit. The three first are required, but it is sufficient to add an empty quote («») in one of the first two.

The data series editor

The first box is a text field where the name of the series can be inserted or edited. This is the field that will be used to fill the text in the legend box (shown below).

Chart legend

However, if you want to have the name of each bar along the x-axis of the chart instead of using the legend, this can be done by adding whatever text you want to present in the Category expression field, or by inserting an expression to have it filled automatically when the report is run. In this case, we want to have one bar for each organisation unit. We therefore edit the category expression by clicking on the button to the right.

Editing the category expression

As the expression, we chose organisationunitname, as shown below.

Choose organisation unit name.

When we are finished, the series editor should look like below. Click OK, then Close to close the Chart Details window.

Series editor.

If you add a good description in the Category expression area, you can leave out the legend box. This is done in the Report properties panel of iReport, where you can also edit many other details of the chart.

Checkbox to show/hide chart legend

We can also add a title to the chart, for example the name of the indicator. This is also done in the Chart properties panel, under Title expression.

Edit chart title

The Expression editor window will appear, where you can enter the title. Note that the title must be in quotes, as shown below.

Expression editor

The chart is now ready. Line charts

Line charts can be useful in many circumstances. However, to make line charts the report data (report table) must be suited for it. Thus if you want to make a line chart, it is important that the report table does not have periods in the crosstab dimension. Examples where this is useful is if you are making a report for a single organisation unit with one or more indicators, or if you are making a report with one indicator and one or more organisation units.

Below, we will go though the steps needed to make a report with a line chart showing the development of three indicators over one year, for one organisation unit. We start by making a report table with the choices shown below:

Report table - crosstab dimensions

Report table - periods and parameters

When we open the resulting .jrxml-file in iReport, the default line chart is included. Since we want to make a line chart, we delete this chart and drag a new chart element into the report from the Palette panel.

Delete the default chart

Drag in new chart element

As soon as we drag the Chart element into the report, a window will appear. We choose the Line chart, as shown below.

Choose Line chart

A chart wizard will appear. Click next in the first step, then Finish in the next - we will add the data later.

Choose Next

Choose Finish

Next, adjust the size and position of the chart in your report. Then, we will add one data series for each of our three indicators. Right-click on the chart and choose Chart data. If you are making a chart with one indicator and several organisation units, you probably want to make a filter expression so that only data from the parameter/parent organisation unit is used in the chart. To do this, add this line to the Filter expression area:


In our example, we only have on organisation unit, so this is not necessary. Next, click the details tab to see a list of the series in the chart. For now, this list is empty, but we will add one series for each of our three indicators. To add a series, click the Add button.

Choose the Details tab

Choose Add

In the window that appears, enter the name of the first of the indicators in the Series expression window. Remember to put the name in quotes. In the category expression (along the x-axis) we want the months, so we use the button next to the field to open the Expression editor and add periodname.

Edit the category expression

Choose periodname as the expression

In the value expression, we add the actual data values for our first indicator. Use the Expression editor again to do this. When we are finished, the window should look like the one below, only with different names according to the indicator.

The finished category series

You can then Click OK to close the window. Follow the same steps to add a series for the other indicators.

Chart details after adding three series

Close the window, and the data for the line chart should be ready. However, some additional adjustments might be needed - most of these can be found in the Line chart properties panel. For example, when making a month by month chart as we have in example, there is often not enough space for the month names along the category axis. This can be fixed by rotating the labels by for example -40 degrees, by using the property Category Axis Tick Label Rotation.

Changing the appearance of the category labels

Many other options are available to give the chart the desired look.

The finished line chart Adding the Report to DHIS2

We can now switch to DHIS2 and import our report. Go to the Report Module in DHIS2, and select "Standard Report". In the "Standard Report" screen, click "Add new", or edit an existing one.

In the following screen, there are several actions we need to take. First, enter a name for the new "Standard Report". Second, for design, click "Choose File" and find the .jrxml-file you have edited in iReport. Then we select the report table that we used as a basis for the report in iReport. Click add, and it should move to the "Selected report tables" area. Finally, click save.

Upload the file and choose the right report table

The report is now available as a "Standard Report" in DHIS2:

The report is available as a Standard Report Some final guidelines

  • Use the same version of iReport and DHIS2's version of Jasper reports. See the About page in DHIS2 for the Jasper version in use.

  • Use report tables with cross tab dimensions as your data source for your report designs. This will make it a lot easier to design reports where you need to put specific indicators, periods, or orgunits on columns.

  • Learn from others, there are many DHIS2 report designs for Jasper on launchpad, see http://bazaar.launchpad.net/~DHIS2-devs-core/DHIS2/trunk/files/head:/resources/

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

  • periods - string of comma-separated identifiers of the relative periods

  • period_name - name of the reporting period

  • organisationunits - identifier of the selected organisation units

  • organisationunit_name - name of the reporting organisation unit

  • organisationunit_level - level of the reporting organisation unit

  • organisationunit_level_column - name of the corresponding column in the _orgunitstructure resource table

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.

16.4.4. Designing HTML based standard reports

A standard report can be designed using purely HTML and JavaScript. This requires a little bit of development experience in the mentioned subjects. The benefit of HTML based standard reports is that it allows for maximum flexibility. Using HTML you can design exactly the report you want, positioning tables, logos and values on the page according to your design needs. You can write and save your standard report design in a regular text file. To upload your HTML based standard report to DHIS2 do the following:

  • Navigate to standard reports and click "Add new".

  • Give the report a name.

  • Select "HTML report" as type.

  • If you want to you can download a report template by clicking on "Get HTML report template".

  • Select desired relative periods - these will be available in JavaScript in your report.

  • Select report parameters - these will be available in JavaScript in your report.

The report template, which you can download after selecting report type, is a useful starting point for developing HTML based standard reports. It gives you the basic structure and suggests how you can use JavaScript and CSS in the report. JavaScript and CSS can easily be included using standard script and style tags.

If you selected relative periods when creating the standard report you can access these in JavaScript like this:

var periods = dhis2.report.periods; // An array with period identifiers
var period = periods[0];

If you selected the organisation unit report parameter when creating the standard report you can access the selected organisation unit in JavaScript like this:

var orgUnit = dhis2.report.organisationUnit; // An object
var id = orgUnit.id; 
var name = orgUnit.name;
var code = orgUnit.code;

When designing these reports you can utilize the analytics Web API resource in order to retrieve aggregated data in JavaScript. Have a look in the Web API chapter in this guide for a closer description. As a complete, minimal example you can retrieve analytics data after the report has been loaded and use that data to set the inner text of an HTML element like this:

<script type="text/javascript">
$( document ).ready( function() {
    $.get( "../api/analytics?dimension=dx:FnYCr2EAzWS;eTDtyyaSA7f&dimension=pe:THIS_YEAR&filter=ou:ImspTQPwCqd", function( json ) {
        $( "#bcg" ).html( json.rows[0][2] );
        $( "#fic" ).html( json.rows[1][2] );
    } );    
} );

<div>BGG coverage: <span id="bcg"></span></div>
<div>FIC coverage: <span id="fic"></span></div>

A few other tips: To include graphics you can convert an image to SVG and embed that SVG content directly in the report - DHIS2 is based on HTML 5 where SVG tags are valid markup. To include charts and maps in your report you can use the charts and maps resources in the Web API. You can use the full capability of the Web API from JavaScript in your report - it may be useful to read through the Web API chapter to get an overview of all available resources.