DHIS 2 MS EXCEL PIVOT TABLE TUTORIAL

Step 1: Export data to datamart

Start DHIS 2, go to Data Mart module, and export all value to data mart.
Also you need to go to the Maintenance section in datamart and "Generate Structures".

These actions will create three new tables in your database;
aggregateddatavalue
aggregatedindicatorvalue
orgunitstructure

If you join either of the two value tables with the orgunitsructure table you will get the whole orgunit hierarchy in the same view together with the values so that the pivot tables can automatically aggregate the data when zooming in and out of the hierarchy.

NOTE: the three tables above are all based on IDs and you will also need to join in the the proper names of orgunits, data elements, indicators and periods form their respective tables (organisationunit, dataelement etc.). I recommend that you wither create a new table (your OLAP fact table) based on these joins or just create a view in your database. Either option will make your life much easier when setting up the pivot tables in either OLAP cube, in Excel Pivot Tables or in OpenOffice Data Pilot.

Step 2: Create a data source (ODBC)

• Open Control Panel --> Administrative tools --> Data Source (ODBC).
• In User DNS tab, add a new data source:
  • Click 'Add' button.
  • Choose 'PostgreSQL Unicode' (NOTE that you will have to download and install the postgres odbc driver first (go here).
  • Fill information fixed with your system. E.g.:
    • Data Source: dhis2_psql
    • Description: DHIS 2 data source in PostgreSQL, used for Excel Pivot Tables
    • Database: dhis2
    • SSL mode: prefer
    • Server: localhost
    • Port: 5432
    • Username: dhis
    • Password:

Step 3: Begin designing the pivot table in MS Excel

NOTE: Before starting this step you should install MS Query which is part of the MS office installation package, but usually not selected in standard installations.

• Start MS Excel, create new document.
• Data --> PivotTable and PivotChartReport.
• External data source --> next
• Get data --> choose the data source name which we made in previous step.
• The first time you do this the first query window you get is quite poor, so click cancel and wait for Microsoft Query to open.
• Choose your view or fact table and select the fields you need
• When you have the result set you need (you should be able to see the values in the result set table with your selected fields) you select Return to Excel in the Top menu->File
• Go next and open the layout window
• Grab a drop necessary fields for your table
  Notice: Later you can modify the field name by right clicking on it and opening "Field settings", add a title, some notices and so on for a better pivot table.
• Additional step for indicator tables:
• Your indicator values should be calculated by Excel using the formula "Factor*(Numerator/Denominator)" to make sure Excel aggregates the values properly. This can be done by inserting a calculated field into the Data field area. In the layout window in the wizard just put any of the three fields in data field, e.g. numerator (the wizard does not accept an empty data field). Then close and when you can see the table in normal view select the numerator field and in the top menu go to Insert->Calculated field. In the new window you can give your field the name e.g. IndicatorValue and specify the formula based on the available fields in the list (factor, numerator and denominator): Factor*(Numerator/Denominator)

Step 4: Update your tables with new months of data

• Use data mart module to export data to datamart.
• Open xls pivot table file, move the cursor to a pivot cell, right-click and choose "Refresh data", or go to the top menu Data->Refresh data.

Need to edit pivot table connection details

Excel will not let you edit the connection name (DNS), database name etc. so in order to do this you must install a handy little addin for Excel called PivotPlay