IMPORTANT: If you are planning on using your BIRT reports on the pentaho platform you will have to use BIRT Report Designer version 2.1.0 as the most recent designers are not yet supported by pentaho.
How to install BIRT
Very simple. Just download the zip file and unzip to a desired location. Then run BIRT.exe from the installation folder. You will need java jre to run BIRT.
Download the latest BIRT designer
, or Pentaho compatible BIRT designer v. 2.1.0 if you are going to use the pentaho for viewing the reports in stead of the integrated birt viewer in DHIS 2].
Main steps in designing a BIRT report
- Add a new data source. Connect to the datamart database in 1.4 using an ODBC connection or directly to the DHIS 2 using jdbc connection for your DBMS of choice (e.g. MySQL)
- Add the datasets you need. A dataset in BIRT contains the data you can use in your reports. It is basically a result set returned based on a SQL query you have to define. The fields in the dataset can be dragged and dropped into your report design. You typically need one dataset for your values plus one datasets for each of your report parameters (see below).
- Add the report parameters needed. If you like to make your report interactive, meaning dependent on some user input when the report is generated you can add report parameters like Period, District or Indicator, basically any fields you would like to filter your report on. For each parameter you typically need a corresponding dataset containing the values that the users can choose from (in a drop-down list or similar). After adding all the parameters you will have to go back to your main dataset (the one with the actual data to display in the report) and add your report parameters as default values to your datasets parameters. This is how the users selected values (report parameters) are bound to the '?'s in your query.
- Start designing your report. A useful first step is to drag and drop the whole dataset containing your values as this will give you a table with all the fields in the dataset. Then you can easily just remove the table columns that you don't need.
Useful hints
How to create "dynamic" datasets filtered by the report parameters?
When you create the query for your dataset you can add a where clause where you specify e.g. indicator = ? and period = ?. Then you will have to define one (dataset) parameter for each question mark in your where clause (still in edit dataset window).
How to create report parameters?
The report parameters give the user an option to filter the report, e.g. select a specific month or an indicator. E.g. if you have a monthly report displaying one indicator for all districts you need the two report parameters "indicator" and "month". For each of these you would like to present the user with a list of possible indicators and possible months based on what data you have available in the database. To retrieve these values from the database you will have to create a new dataset for each of the parameters you need, in this case one dataset that retrieves all indicator names available (should be from the same table/view as you fetch the values so that all indicators actually has data) and another dataset that retrieves all months available (again from the view that gave you the values, e.g. PivotSource_IndicatorOU4). When you have the datasets ready you can add a new report parameter. The value list should then be dynamic and picked from the datasets you just made and from the specific column that contains the names you need.
How to bind the report parameter to the dataset parameter?
After creating the report parameters you can open the dataset you want to bind to (the one containing the actual data for the report) and in the parameter section of the edit dataset window you have to specify the report parameter as the default value of the dataset parameter. In our case we have two parameters, "indicator" and "month" and these can be found in the expression builder (when you click on the button in the default value field) under the Report Parameters. To check that all these parameter bindings work you can click on "Preview results" in your dataset, then it should use the default values you have given the report parameters and display a filtered result set.
How to design a report with specific data elements or indicators as column headers (e.g. if you need to "copy" the design of a paper-based report)?
The normal PivotSource views in the DHIS datamart have DataElementName or IndicatorName as a column heading and on each row you can find the specific names. This is useful when you want a straightforward list of data element or indicator values (especially if you use the SortOrder to sort that list) as rows in a table. However, if you like to put specific data elements or indicators as column headings, e.g you want to make an EPI report with the various vaccines as columns and districts as rows, then you cannot use the normal PivotSource queries/views as you would need the data element names as column headings also in the view. One approach that we have used is to create a crosstab view in the datamart where e.g. all data elements or indicators in one specific DataElement and Indicator group appear as column headings in addition to the orgunit and period information. The create crosstab query wizard in Access is very helpful as it helps you through the more complicated crosstab steps in an easy way. As far as we know there is no similar support in MySQL to create crosstab queries like what you have in Access. If you have any hints on how to set up similar crosstabs in MySQl or general SQL we would be happy if you could post them here.
Anyway, after creating the crosstab query you can add a new dataset in BIRT based on the crosstab query which will give you specific data elements like "BCG < 1 year" as columns/fields in the dataset that you can drag and drop e.g. into your report table columns.
Grouping of orgunit data in a table-based report
Let's say you want to make a report for 1 indicator and 1 period for all districts. You want to list districts as rows in a table with the indicator value next to the district name, and then after all districts in each of the provinces you want to display the province subtotals. So your report will have two columns and a dynamic number of rows based on the number of rows in your result set (one for each of the districts that have data for that specific indicator+period combination).
If district is OrgUnit3 and province is OrgUnit2 in your dataset then you need to drag and drop OrgUnit4 into the detail field for your first columnso that the district names will be displayed at each row. In the next detail field you need to add a dynamic text field that will contain your indicator formula (basically [NumxFactor]/[DenominatorValue]). To add the province (OrgUnit2) subtotals after all districts for each of the provinces you need to use the Table Group functionality. Right click inside your Table and select Insert Group. In the Edit group window the important field is Group On, here you must select OrgUnit2 from the list of available dataset fields. This means that it will do a group by on your dataset on the OrgUnit 2 column. After closing the wndow you need to decide whether you want the [Orgunit2] field in the group header (above your districts) or in the group footer field (below the districts for the given orgunit2). Just as you did for the districts you need to put the orgunit name in the detail cell of the first column and the indicator expression in the detail cell of the second column. To put the indicatorvalue again drag and drop a dynamic text field to the detail cell and double click on it to open the Expression builder window. To get the sum of all the districts in the given group (OrgUnit2) you can use the built-in BIRT function (in the Total section) called runningSum(). Just put the [Numxfactor] and [DenominatorValue] inside the parenthesis of the function (one for each field). The dataset fields you can get via the Column Bindings section in the Expression builder window.
BIRT charts
These instructions explain how to design a pie chart for e.g. a top 10 diseases graph. Other charts are very similar to design.
First, drag the chart icon from the left menu palette onto the report page. Then you will soon get the chart edit page where you define your chart:
- Bind to dataset to access your data fields
In "Select Dataset:" section, look for "Use Dataset" and select your dataset in the drop down list.
- Assign fields to category and value dimensions (or to axis when designing bar charts)
Right click on the available fields in the dataset and assign to category and value, or drag and drop in the text fields. Typically data element or indicator.
- For percentages in stead of cases:
Click on Data Binding in the dataset area and the fields from your dataset will appear. Add a new field by writing a name (e.g. Percentage), a type (Float) and in the Expression field you click on the little button to open the expression window where you can write in your formula. In this example formula the no of cases are provided by the SumOfEntryNumber field:
100 * ( row["SumOfEntryNumber"]/Total.sum(row["SumOfEntryNumber"]) )
Now drag your new Percentage field into the Value area of the chart. Next to it you can define how many decimals you want by clicking on the 0.0# button. For one decimal only select Standard and put 1 in the Fraction Digits field.
- The remaining steps are rather self-explanatory, fill in titles etc, and click "Finish". In "Preview" you can view your chart.
BIRT manual (Help section)
We recommend that you read the very useful manual found under the HELP section in the BIRT Designer.
Want to run your BIRT reports inside DHIS 2?
Have a look at this howto
BIRT report in Pentaho?
Then please follow the instructions on this page:
Pentaho DHIS guidelines
