DHIS 2 Excel Reporting

- Administration - There are six function of this module. - - - Configuration: Set a path to the directory where the excel template files are stored. - - - List of reports: Allows users to create reports and its items and to assign the organisation units or the organisation unit groups to the reports. There are four types of report, as follows: a report containing standard data elements, a report listing organisation unit groups as rows, a report listing period of time as columns, a report generating data elements combined with categories - - - Data Entry Status (or view the status of the data entry): View the data entry status to know about data entered, reports finished or not finished yet with data entry. In order to view the data entry status, adding data sets which is equivalent to the data entry is mandatory. - - - Excel items: In many situations, health workers in the field may not have good Internet access, and will therefore need to enter data "offline" and transmit this data via email or other means, for subsequent importation into the DHIS database. The Excel Report data import feature allows users to import data from a pre-designed Excel templates directly into the DHIS database automatically. Alternatively, selected elements may be imported. It supports three types of report, includes a report containing standard data elements, a report listing organisation unit groups as rows, a report generating data elements combined with categories. - - - Excel Template Management : Manage Template Excel files which are used to export reports, includes upload, download, delete and rename files. - - - Clean temporary directory: Every time a user generates a report, the system creates an Excel file in the temporary directory (excelreporttemplate directory). This directory would be larger and larger after some time and occupy a lot of memory. This function will clean those files from temporary directory. - - -

- Configuration - The directory configuration is to set the path of the directory containing the Excel Template (.xls) file. - In order to set the directory, choose Administrator > Configuration> Set path and specify the path. The procedure is outlined in more detail below. - - - Click Administration to open Administrator page. - - - - - - Click Configuration button - - - Set a path to the folder containing excel file - An Excel template directory is simply a directory that will be used to contain all the necessary files. It is recommended to create this folder inside the DHIS2_HOME folder. - - - - Click OK to save - - -

- List of reports - The function allows users to create reports and its items and to assign the organisation units or the organisation unit groups to the reports -

- Types of report - Before generating reports, there is a need to create Excel report template files. Reports will be generated in a standard format according to the structure defined in the template. Currently, there are four report types that the module can support. - - - Standard reports contain default data elements and indicators - Standard reports have a fixed model in which number of the columns and the rows are unchangeable. Normally, this report is generated by only one organisation unit or one organisation unit group at a specified period of time. An example is show below. - - - - - - Row-based reports with multiple organisational - This type of report displays data elements’ name in the fixed columns, and organisation units are be displayed automatically according to the selected parents organisation unit in a period of time. These organisation units are also grouped together following the administrative level, such as: province, district, commune, etc. - - - - - - Reports list time periods in the columns - This report display data values according to the fixed data element names, and the list of time periods are displayed automatically. - - - - - Reports with data elements and categories - This type of report displays data values which are combined of data elements and category combination. Data element names display as rows and category options are displayed as columns. Data elements’ names, codes may be displayed automatically as well as the sequence numbers of the rows - - - - - -

- Create a new report in DHIS2 with Excel report template file - When the Excel report template file is available, creating a new report and the relevant report items are required. A step-by-step procedure is provided below. - - - To create a new report, choose Administration --> List Of Report --> Add --> Fill the information form --> OK - - - To create report items in the report choose Administration --> List Of Report --> Choose a report --> Report Item --> Add --> Fill information form --> OK - - -

- Create new report - Click List Of Reports - - - - Add new report: Click Add button to add new report - - - - Fill information of report in this form: - - - - - - Name *: Enter Name of report - - - Excel Template*: Enter name of Excel report template file. (include extension) - - - Report Type *: Choose report type - - - Normal : to create reports include just data elements or indicator - - - Category : to create reports with data values are combined of data elements and categories - - - Period column listing: to create reports which list data values according to time period of specified organisation unit. - - - Organisation unit listing: reports which list data values according to organisation units. - - - - - Period position row and Period position column: Row and Column Number in Excel template file where to put Period value. - - - Organisation Unit Row and Organisation Unit Column: Row and Column Number in Excel template file where to put Organisation Unit Name. - - - Click OK button to create new report - - - - - - There are buttons that can be used for various actions as depicted below: - - - - - - Report Associations for assigning reports to organisation units - - - Report Item for creating report items in the report - - - Remove for removing report - - - Edit for editing report’s information. - - - Organisation unit Group : For choose which organisation group would be list in the report. This button only appear when the Report Type is Organisation Unit Listing - - - Data element groups : This button for create Data element group and these groups of data elements would be list in the report. This button only available when report type is Category. - - -

- Create report items - Click Create Report Item button - - - - Click Add button to open a form - - - - Fill the report item’s information - - - - - - Name*: Enter report item name (Ex: Total person joined HIA) - - - Item Type*: Chose item types - - - Data element : if this report item is data element - - - Indicator: : if this report item is indicator - - - Excel formula: if this item describes the excel formula - - - Data element Name: if report item is list of data element’s names - - - Data element Code: If report item is list of data element’s codes - - - Serial : If report item is list of auto generating sequence no - - - Organisation: if report item is list of organisation unit. - - - - - Period Type*: Chose period type which data values of that report item would be aggregated and displays according to period parameter that user will choose when generate report. (Last 3 months, Last 6 months, So far this year, Quarterly, Six-Monthly, Yearly). - - - Expression *: Click Expression button to choose Data element, Indicator, Or just type excel formula if Item type is Excel formula. - - - - - - - The category option would be default if data element hasn’t been combined with any category combo. Other options are described below: - - - Sheet No*: type sheet no where this report item would be display in excel template file - - - Row*: row number refers to cell where report item would be display in Excel file. - - - Column*: column number refers to cell where report item would be display in Excel file. - - - Click OK button to create report item. - - - - - - New report item has been created - - - - - There must be at least one period type in a report. - - Copy selected items to button: To copy the report items to excel items of an excel item group - - From list of reports page, Add new report - - - From source report, select report items need to be copied. - - - Click the Copy selected items to button - - - List of report: chose a destination report - - - Sheet No: Choose the Sheet No which report Item would be display. - - - Click OK button to copy - - - - - Copy selected items to other reports for quickly developing many reports with the same data elements but with the different periods. - Copy to excel items button: To copy the report items when the new report has an item which can be reused from the existing report. - - - From source report, select report items need to be copied. - - - Click the Copy to excel items button - - - List of groups: chose a destination report - - - Sheet No: Choose the Sheet No which report Item would be display. - - - Click OK button to copy - - - - - Copy selected items to Excel item groups for quickly developing many groups with the same data elements. - -

- Assign report -

- Assign report to Org Units - Click Report Associations button: - - - - Chose Org Units to assign and click Save button - - - - Select Org Unit groups which would be available in the report by clicking on each organisational unit. There is a difference between Select Org Units to assign to report for OrgUnit generate reports later, which select org unit groups are list of OrgUnit groups would be available in reports. - This function only appears in Organization Unit Listing report type. - Click Organisation Unit group button. - - - - Select Organisation unit groups, arrange sequence of these groups, and click OK to save. - - - -

- Assign report to user role - To manage the authority of using the reports, we should assign reports to user roles. This allows the administrator to decide which users are able to view certain reports. - - - From Main menu, choose Maintenance > Users - - - From Left menu, chose User Role - - - - - - Click Edit button to edit User role’s information - - - - - - Select Excel reports, click Save button to assign excel reports to user role. - - - - - -

- Data Entry Status - This function allows users to see which datasets are entered with data and how the data values are entered. This helps save time for the health manager to follow the completeness of the data entered. In particular, there is a need of viewing data set entry status. - Add data set to view - - - - Data Entry Status page would be displayed. Click Add button to add data set. - - - - Select the desired data set and Organisation Unit from the menu. - - - -

- Excel items - The sub function allows users to import data from a pre-designed Excel templates directly into the DHIS database automatically. Alternatively, selected elements may be imported. - - - -

- Create a template to import - - - To create a group, choose Administration --> Excel items --> Add --> Fill the information form --> OK. - - - To create items in the grorp, choose Administration --> Excel items --> Choose a group --> Excel items --> Fill information form -> OK. - - Creating a Excel items group which contains Excel items. A Excel item correspond with a data cell into imputted template Excel files. A step-by-step procedure is provided below. - - - - - - Name *: Enter Name of report. - - - Report Type *: Choose report type - - Normal : to create group include just data elements or indicator - - - Category : to create group with data values are combined of data elements and categories - - - Organisation unit listing : groups which list data values according to organisation units. - - - - - Period Type *: Type of period to import data values. - - - Click OK button to create new report. - There are buttons that can be used for various actions as depicted below. - - - - - - Associations for assigning a group to organisation units. - - - Excel item group for creating report items in the report. - - - Remove for removing excel item group. - - - Edit for editing information of group. - - - Organisation unit Group for choose which organisation group would be list in the group. This button only appear when the Group Type is Organisation Unit Listing - - - Data element groups for create Data element group and these groups of data elements would be list in the report. This button only available when report type is Category. - - -

- Create excel items - Click Excel item buttton to create items. - Click Add button to open a form. - - - Fill information of an excel items. - - Name*: Enter item name - - - Expression *: Click Expression button to choose Data element, Indicator, Or just type excel formula if Item type is Excel formula. - - - - - - - The category option would be default if data element hasn’t been combined with any category combo. Other options are described below: - - - Sheet No*: type sheet no where this item would be display in excel template file - - - Row*: row number refers to cell where item would be display in Excel file. - - - Column*: column number refers to cell where item would be display in Excel file. - - - Click OK button to create item. - - - Copy selected items to button: To copy the report items when the new report has an item which can be reused from the existing report - - - - From List of groups page, select items need to be copied. - - - Click the Copy selected items to button. - - - List of groups: chose a destination report. - - - Sheet No: Choose the Sheet No which report Item would be display. - - - Click OK button to copy. - - - - Copy selected items to other groups for quickly developing many groups with the same data elements but with the different periods - -

- Excel Template Management - This is used to manage Template Excel files which are used to export reports, includes upload, download, delete and rename files. - - - - To upload template file, choose Browse button. - - - Choose a file from shown dialog - - - Click Open to choose file, and click Upload button to upload. If to upload successfully, a message shown : - - - No upload if the chosen file is not excel file. - - There three operation functions - - - Remove For removing report - - - Edit for editing report’s information. - - - Download for dowloading template file. - - -

- - <emphasis role="italic">Clean temporary directory</emphasis> - - This allows the user to clean temporary directory. - - - From Main menu, choose Maintenance > Clean temporary directory - - - - - -

- Generate Report - There are six functions of this module. - - - Generate Report: Allow users to generate reports according to parameters such as time period, organisation unit, organisation unit group and to generate group of reports to many sheets of a single Excel file. - - - Generate advanced report: Generates reports by OrgUnit group. - - - Import excel files: Imports data values from an Excel file to the DHIS database. - - - Data Set completed reports: Generates the report which show status of completeness of entering data . - - - Data entry Status: View data entry status. - - - Individual reports: Generate user defined reports which list data elements and a list of periods which can be chosen by the user. - - -

- Generate report - To generate a report we just follow these simple steps. - - - Click Generate Report - - - Select Org Unit - - - Select report Group - - - Select Report from list - - - Select period - - - Select period - - - Click Generate Report button - - - - - -

- Generate advanced report - This Generate Advanced Report function allow users to generate a report according to the parameters such as: Report group name, Report name, selected period, and organisation unit group name. The difference between Generate Advanced Report function and Generate Report function is that the advanced function is capable of generating reports according to groups of Org Units withing the same level as depicted below. - - - -

- - <emphasis role="bold">Import excel files</emphasis> - - This feature is current still in development. More information on this feature will be added soon. - The EXCEL REPORTING module is continuing develop and the documentation of it for users will be updated. If there is any other type of report template which are not supported by DHIS, feel free to send the requirements. All feedback will be appreciated, please inform us at - dhis2-documenters@lists.launchpad.net - . -

- Data set complete reports - When users finish entering data for a data set, they need to click the COMPLETE button to identify that the data set is completely entered. The View Data set complete report function takes care of this completeness. There are two ways of viewing data set complete reports: View as Period or Dataset. When viewing by Period, users can see complete status of 1 dataset in many periods of time. When view by Dataset , users can see many data sets in only a specified period. This function is only used for management users in the upper Organisation Unit. - To view data set complete reports, do steps below - - - From the left menu, click Dataset complete reports - - - Choose Orgunit name. (Parent Organisation Units) - - - Select Period Type - - - Select View Type. - - - Select data set (Select only 1 data set if view by Period) - - - Select Period (Select only 1 period if view by Dataset) - - - Click View Report button - - - - - - Example of view report by Period - - - - Example of view report by data set - - - -

- Data entry status report - This function allows users to see which datasets are entered with data and how the data values are entered. This helps save time for the health manager to follow the completeness of the data entered. In particular, there is a need of viewing data set entry status. - View Data entry status - From Left menu, click Data Entry Status > Select Org Unit name to view. - - - - The Data entry status is displayed as percentage. The relative percentage of data elements that were entered in the form. -

- Generate an individual report - This function allows users to generate the reports with data elements and a list of period as their individual decision. Another way, user can generate a dirty report by selecting data elements, periods to generate a simple excel report without excel template file or creating report or report items. Then, user can bookmark this report for generating later. The steps to generate individual report are listed below - - - Click Individual report function from the left menu - - - Select organisation unit - - - Select data elements - - - Select category option of selected data element - - - Click select button - - - Select period type - - - Select periods - - - Click select button - - - Click generate report button - - - Click Book mark button for generate later - - - - - - - - -