28.4 Standard reports

28.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.

28.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.

28.4.2.1 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.

28.4.2.2 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.

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 contracted 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.

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.

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.

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!

28.4.2.3 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.

28.4.2.3.1 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

28.4.2.3.2 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

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.

28.4.2.3.2.1 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:

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.

28.4.3 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.

28.4.3.1 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.

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:

$F{organisation_unit_is_parent}.equals("No")

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

28.4.3.2 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.

28.4.3.3 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.

28.4.3.3.1 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")

28.4.4 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.

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

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.

Click OK - Close - OK to close the three windows. The table should now be sorted.

28.4.5 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.

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.

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.

($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"
:
$F{indicatorname}

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.

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

28.4.6 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 will have a totals column to the right.

28.4.7 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.

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.

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.

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.

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.

Next, add a text field to the Indicator group heading band, and edit it’s expression to display the 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.

28.4.7.1 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.