Building Reports in BIRT

Installing BIRT

To customize the standard reports provided by default with Mifos or to create your own reports, you will need to download and install version 2.1.2 of the BIRT RCP (Rich Client Platform) Report Designer from Eclipse. The RCP version is required because it produces reports in the version (3.2.6) supported by the BIRT Report Viewer provided with Mifos. It also simplifies the Eclipse user interface so that only essential screens and options are presented.

To install BIRT:

  1. Download, free of charge, the BIRT RCP Report Designer version 2.1.2.
  2. Accept the default installation location (for example, C:\Program Files\birt-rcp).

Creating your own Reports

Designing your Reports

Designing your own report starts with planning it on paper or in a spreadsheet application like Excel. See the following sample: report designed in Excel.

Data Extraction and Mifos

If you are creating your own report, the data in the report can be extracted one of two ways:

  • Using an SQL query (recommended): In this approach, you create a new report in BIRT that includes an SQL query that you provide. When the report is generated, data is extracted over a database connection. The topics below describe this approach and assume a working knowledge of SQL.
  • Using a direct software connection: You can also use a direct software connection (or, in BIRT terminology, a scripted data source) to extract data. In this approach, BIRT interacts with the business logic layer in Mifos' Java code. Java knowledge is required. For more information, see Creating A BIRT Report Using A Scripted DataSource.

For a technical overview on how BIRT interacts with Mifos, see Current Reports Architecture.

Extracting Data Using SQL

To create a custom report that uses SQL to extract data, use the procedure below. For help with steps you perform in BIRT, see the following in the BIRT RCP Designer online help: BIRT Report Developer Guide > Field Guide to BIRT > Learning the Basics > Tutorial 1: Building a Simple Listing Report.

  1. Open a new report in BIRT RCP Designer.
  2. Create a data source in BIRT, using the following values in the New Data Source wizard:
  • Data Source Name: Mifos Data Source
  • Driver Class: com.mysql.jdbc.Driver (what to do if this driver isn't listed)
  • Database URL: jdbc:mysql://localhost:3306/mifos (if you're creating reports locally)
  • User Name: root
  • Password: the default is mysql
  • JNDI URL: leave blank
  1. In the Query tool of your choice, write the SQL query. See SQL Query Tips for more information.
  2. In BIRT, create a data set. In the Query window, paste in the query you wrote in step 3.
  3. Create one or more input parameters in BIRT. See About Input Parameters below for more information.

If com.mysql.jdbc.Driver isn't listed

If com.mysql.jdbc.Driver isn't listed, download it from http://dev.mysql.com/downloads/connector/j/5.0.html and place the file mysql-connector-java-5.1.5.bin.jar in ...\mifos\build\src_pkg\WEB-INF\platform\plugins\ where ... is the path to your Mifos working copy. For example, on Windows: C:\subversion\projects\mifos-trunk\mifos\build\src_pkg\WEB-INF\platform\plugins, on Mac OS X or GNU/Linux: /home/user/svn/mifos-trunk/mifos/build/src_pkg/WEB-INF/platform/plugins/.

SQL Query Tips

As you write your query, keep the following in mind:

  • Look at the Mifos database: Using the sample report you designed in Excel and an application like MySQL Query Browser, view the tables and their columns in the Mifos database to determine what you need. The automatically generated Mifos schema diagrams are another resource. Pick the database link that corresponds to your Mifos database version. To determine your Mifos database version, run the following command on your Mifos database: select * from database_version
  • Sequence is important: Put your SQL query fields in the same order in which you want them to appear in your report.
  • Hash sign not recognized: BIRT does not recognize the hash sign (#). To include comments, use the following characters: /* comment */
  • Finish your query: Make sure your query is final before you paste it into the data set you'll create in step 4.

About Input Parameters

An input parameter is a value entered by the user. A series of input parameters, called cascading parameters, can dynamically filter, or constrain, the next option's possible values. The Collection Sheet Report uses cascading paramters.

For steps on how to create input parameters (or in BIRT terminology, data-set parameters) see the following section in the BIRT RCP Designer online help: BIRT Report Developer Guide > Field Guide to BIRT > Enabling the User to Filter Data.

Creating a Receipt Template (Administrative Documents)

The Administrative Documents feature in Mifos allows you to generate a receipt for an account -- without having to use the Reports tab. You can also use this feature to generate other administrative documents specific to an account, such as vouchers and passbooks.

To create a receipt template:

  1. In BIRT, open a new template. You do not need to specify a data source -- the Mifos database will be automatically used.
  2. Use the following parameters, in the following order:
  • userId
  • account_id
  • reportName
  1. In Mifos, upload the template using the Upload new Admin document link on the Admin tab. A receipt link will automatically appear on client loan account pages. Note the template must be a BIRT report design document which has an extension of '.rptdesign'.

When you click an account's receipt link, the system automatically passes in the account ID of the loan account and generates a receipt, based on the BIRT template design you uploaded.

Localizing your Report

To add labels in another language:

  1. With your report loaded in BIRT RCP Designer, click the Layout tab.
  2. Select your report by clicking white space or a margin in your report.
  3. In the Property Editor window on the bottom, select Resources.
  4. Add resources using the following naming convention: [file name]_[language]_[country].properties. For example: EndaTransactionReport_fr_FR.properties and EndaTransactionReport_en_US.properties

Tips for populating the properties file(s):

  • Take the raw column names, such as customer_name, government_id, etc., and add them to the .properties file(s). You can get them from the output columns in the data set or from the xml in the XML Source tab.
  • If you've already re-named the column headers with user-friendly names, you can type them into the .properties file.
  • Column header text must exactly match the text in the .properties file.
  • To run the report in BIRT RCP Designer in a specific locale, on the Windows menu of BIRT RCP Designer click Preferences > Report Design > Preview > Choose your locale. Note that the file name suffix must correspond to the locale. See Java locale naming conventions.

Uploading and Editing your Report

To upload and view your report:

  1. On the Admin tab in Mifos, click Upload report templates under Manage Reports.
  2. Provide the report name, choose the Reports tab category under which you want your report to appear, then click Preview > Submit.
  3. To view the report, click your report's name on the Reports tab, select and/or enter the parameters, then click Generate.

To edit your report:

  1. On the Admin tab, click the View report templates under Manage Reports.
  2. Click Edit.

Controlling Access to Reports

Mifos allows you to control two things related to report security:

  • What Mifos users can do with reports (download, edit, delete, etc.).
  • Which report data users can see.

You handle the first by associating report-related actions with specific user roles. You handle the second by incorporating one or more parameters in your report that are based on organizational hierarchy.

Associating Report Actions with a Role

To manage the permissions related to a report:

  1. Ensure that you are logged in as admin.
  2. On the Admin tab, click Manage roles and permissions under Manage Organization.
  3. Choose a role or create a new one.
  4. Select or clear the report permissions you want associated with that role.
  5. Click Submit.

Hierarchy and Data Scoping

If a report has parameters based on organizational hierarchy, a user's access to that report is affected by her position in the organization's hierarchy. For example, if a user is assigned to the Head Office, which is the top of the hierarchy, she can see all report data. If a user is assigned to Branch A, she can only see data for Branch A. If a user is a loan officer, she can only see data for her clients.

0
Your rating: None