Introduction

Reporting/Analytics on Bahmni can be achieved with a variety of ways. See the diagram below. One can also leverage work being done by the OpenMRS community for Reporting/Analytics (see sidebar on this page for OpenMRS links).

Bahmni Reports app or module can be accessed from the Home dashboard. Generally, the Admin user will have the privilege to access reports app. A list of configured reports appears on the Report app screen. The user can launch any of the reports sorted by Start and End date and output format.

Every Bahmni implementation has its own config module. An implementer has to configure the required reports properly so that the Reports app will show all the configured ones. The configuration file is located at openmrs/apps/reports directory of the config module.

Reports can be used in multiple ways in Bahmni

1. Standard Reports are useful to management level people

2. Custom Reports can extract data from the backend database to analyse various facts for different stakeholders.

Bahmni report module supports various output format

1. HTML
2. PDF
3. EXCEL
4. Custom EXCEL (Excel with pre-defined macro)
5. CSV

6. ODF (Openoffice format)

Various Types of Bahmni Reports

1. Generic Reports

Canned and Custom Reports have their limitations. An implementer needs to know the OpenMRS data model and Custom SQL to extract the data for custom reports. In response to customer requests, developers have to repeatedly create canned reports and duplicate the SQL from the similar report, thus increasing the maintenance. This makes life difficult for the community.

To solve this, Bahmni has introduced generic reports for various entities in OpenMRS, for example: Observations, Visits and Programs etc. Generic reports allow an implementer to extract data from the system for each entity, hence Bahmni has a report for each. Generic reports provides options to include related fields and to apply various filters. For further information, refer here. 

2. Canned Reports

These reports can be used to get specific or predefined information from Bahmni. There is minimal configuration available for these reports. For further details please refer here.

3. Custom SQL

Bahmni offers Custom SQL reports where an implementer can specify a SQL to extract the data from the system. But an implementer has to understand the complete data model of the data source and this can be difficult and cumbersome. Custom SQL Reports seek to solve this problem.

Configuration    

"hospitalActivities": {
    "name": "Hospital Activities - Number of Clients Served (New and Total)",
    "type": "MRSGeneric",
    "config": {
      "sqlPath": "/var/www/bahmni_config/openmrs/reports/hmis/number_of_clients.sql"
    }
}

sql file is to be kept in appropriate folder of config as per the sqlPath in configuration.
number_of_clients.sql

Bahmni supports CustomSQL Reports against OpenMRS,OpenELIS, Odoo, BahmniMart data sources.

For reference, here are some of the custom reports at one of our implementations.

4. Jasper Reports (Deprecated)

Few of the earlier Bahmni implementations uses this feature. In this mechanism, Bahmni gives a way to connect with Jasper Report module and an implementer will have to setup Jasper and design the form and SQL using that. Since this is one of the very old ways, it is not recommended to introduce to any site implementations.

5. Bahmni-Mart with Metabase (Reports & Analytics)

Bahmni-Mart is a separate analytics database created by pulling data from Bahmni database (Spring Batch), and is optimised for Analytics purposes. For instance, it transforms every Form into its own table, with each form instance as one row, so that creating reports from the form data becomes easy. This is the recommended way to perform Reporting/Analytics at scale, so that Bahmni EMR doesn't suffer from any performance load due to adhoc & intensive Reporting queries, and you can use a BI/Reporting tool of your choice to easily create visualisations off this secondary database. Please see here for more information. 

Version Controlling & Managing of Reports

The reports at an implementation site will be available in an implementation specific config folder. The report.json and associated SQL files in this folder are generally text based. They can be managed in standard version control systems like GitHub. It is a best practice to version control these files to maintain history and traceability across various releases of the product. Here is an example of versioning report SQLs in Github.

There is a JSON file used for configuring reports. It is located at /var/www/bahmni_config/openmrs/apps/reports/reports.json.

"config": {
   "paperSize": "A3", //The default paper size is A4. Supported sizes are A3 and A4.
   "supportedFormats": ["pdf"], //List of available formats are html, pdf, excel, csv, custom excel, ods. When not defined, all the available formats will be listed.
}

Embedding Reports

Bahmni allows one to embed the data exported by the report in an existing workbook template with formulae to create a pivot table based on the generated data. The workbook template has to be in .xls format.

The reports can be generated from the screen Home > Reports. Select format as 'CUSTOM EXCEL', upload the required excel formula template and click on 'Run Report'. A report will be generated with actual data in the sheet named 'Report' and data generated using the formula in the template in the sheet named 'Sheet1'. Please refer below to configure the Macro file to avoid uploading it every time for a report.

A excel template can be found here which contains the macro. Formulae has to be added in 'Sheet1' of the above excel template and can be uploaded to generate a report. On opening the generated excel report, the macro that is present in the above excel template will apply the formula over the actual data. 'Enable macros' should be selected on opening the generated excel report.

Example Formula: 
=SUM(Report!G9, Report!G10)   - 'Report' sheet is the sheet which would contain the raw data without applying the formulae.

Configure the macroTemplate XLS file 

Configure the macroTemplate XLS file in bahmni_config to avoid uploading the file every time while generating the report 

Steps to Configure :

1. Copy the XLS (Microsoft 1997-2000) file with macro formula to bahmni_config

2. Configure the XLS file path in bahmni_config as below in report.json

   "diabetes":{
           "name": "Diabetes",
           "type": "obsCount",
           "config": {
               "ageGroupName": "All Ages",
               "conceptNames": ["Diabetes, Intake"],
               "visitTypes": ["OPD","IPD"],
               "macroTemplatePath" : "/var/www/bahmni_config/openmrs/apps/reports/macroTemplates/Diabetes.xls"
           }
       },