12.2. Create a pivot table

  1. In the Apps menu, click Pivot Table.

  2. In the menu to the left, select the dimension items you want to analyse, for example data elements or indicators.

  3. Click Layout and arrange the data dimensions as columns, rows and filters.

    You can keep the default selection if you want.

  4. Click Update.

Example 12.1. Pivot table based on the demo database.

In this example, indicators are listed as columns and periods as rows.

Example pivot table with data elements on columns and periods as rows.


12.2.1. Select dimension items

The left menu lists sections for all available data dimensions. From each section you can select any number of dimension items. As an example, you can open the section for data elements and select any number of data elements from the available list. You can select an item by marking it and clicking on the arrow in the section header or simply double-clicking on the item. Before you can use a data dimension in your pivot table you must at least select one dimension item. If you arrange a dimension as columns or rows but do not select any dimension items, the dimension is ignored.

You must choose at least one data dimension type to create a pivot table. The available types are described in this table:

Table 12.1. Data dimension types

Data dimension typeDefinitionExamples
IndicatorsAn indicator is a calculated formula based on data elements.Coverage of immunization across a specific district.
Data elementsRepresents the phenomenon for which data has been captured.Number of malaria cases; number of BCG doses given.
Data setsA collection of data elements grouped for data collection. You can select :
  • Reporting rates: the percentage of actual reports compared to the expected number of reports

  • Reporting rates on time: the reporting rates based on timely form submissions. A timely submission must happen within a number of days after the reporting period.

  • Actual reports: the actual number of reports

  • Actual reports on time: the actual number of reports based on timely form submissions. A timely submission must happen within a number of days after the reporting period.

  • Expected reports: the number of expected reports based on organisation units where the data set and the reporting frequency has been assigned.

Reporting rates for immunization and morbidity forms.
Event data itemsA data element that is part of a program representing events that have been captured.Average weight and height for children in a nutrition program.
Program indicatorsA calculated formula based on data elements in a program representing events. Average BMI score for children in a nutrition program.


You can combine these dimensions to display for example aggregate data with reporting rates, or event data items together with program indicators, all in the same pivot tables. For the "data element" data dimension, you are also able to select "Totals" and "Details", which will allow you to view different category combination options together on the same pivot table.

For the period dimension you can choose between using fixed periods or relative periods. An example of a fixed period is "January 2012". To select fixed periods start by selecting a period type from the period type list. You can then select periods from the list of available periods.

Relative periods are periods relative to the current date. Examples of relative periods are "Last month", "Last 12 months", "Last 5 years". Relative periods can be selected by ticking the check-boxes next to each period. The main advantage of using relative periods is that when you save a pivot table favorite, it will stay updated with the latest data as time goes by without the need for constantly updating it.

For the organisation unit dimension you can select any number of organisation units from the hierarchy. To select all organisation units below a specific parent organisation unit, right click and click "Select all children". To manually select multiple organisation units, click and hold the Ctrl key while clicking on organisation units. You can tick "User org unit", "User sub-units" or "User sub-x2-units" in order to dynamically insert the organisation unit or units associated with your user account. This is useful when you save a pivot table favorite and want to share it with other users, as the organisation units linked with the other user's account will be used when viewing the favorite.

Selection of fixed and relative periods.

Dynamic dimensions can consist of organisation unit group sets, data element group sets, or category option group sets which have been configured with the type of "Disaggregation". Once the group sets have been configured, they will be come available in the pivot tables, and can be used as additional analysis dimensions, for instance to analyse aggregate data by Type of organisation unit or Implementing partner. Dynamic dimensions work the same as fixed dimensions.

[Tip]Tip

Some dynamic dimensions may contain many items. This can cause issues with certain browsers due to the length of the URL when many dimension members are selected. A special "All" check box is available for dynamic dimensions, which allows you to include all available dimensions implicitly in your pivot table, without specifying each and every dimension member.

12.2.2. Modify pivot table layout

After selecting data dimensions it is time to arrange your pivot table. Click "Layout" in the top menu to open the layout screen. In this screen you can position your data dimensions as table columns, rows or filters by clicking and dragging the dimensions from the dimensions list to the respective column, row and filter lists. You can set any number of dimensions in any of the lists. For instance, you can click on "Organisation units" and drag it to the row list in order to position the organisation unit dimension as table rows. Note that indicators, data elements and data set reporting rates are part of the common "Data" dimension and will be displayed together in the pivot table. For instance, after selecting indicators and data elements in the left menu, you can drag "Organisation Unit" from the available dimensions list to the row dimension list in order to arrange them as rows in the pivot table.

The table layout screen.

After you have set up your pivot table you can click "Update" to render your pivot table, or click "Hide" to hide the layout screen without any changes taking effect. Since we in our example have selected both the period and organisation unit dimension as rows, the pivot table will generate all combinations of the items in these dimensions and produce a table like this:

Pivot table where organisation units and periods are repeated on rows.