Collection Sheet Report

Collection Sheet Reports are used by Loan Officers for their meetings with clients. The reports provide Loan Officers with the amounts that need to be collected from each client, including loan repayments, fees, penalties, recommended savings amounts, and any other dues that need to be collected. The reports also provide the amount of money that should be disbursed to each client at the meeting.

To generate a Collection Sheet Report:

1. On the Reports tab, click Collection Sheet Report.
2. Select values for Branch, Loan Officer and Center.
3. Enter the date of the meeting for which you want to run a report.
4. Click Generate.

To create your own Collection Sheet Report template:

1. On the Admin tab, click View report templates under Manage Reports.
2. To use the default Collection Sheet Report as a starting point for creating your own template, click the Download link for Collection Sheet Report.
3. Save CollectionSheetReport.rptdesign locally.
4. Open the above file in BIRT RCP Designer and give it a new name, thus preserving a backup.
5. Edit your version of the .rptdesign file in BIRT.
6. In Mifos, use the Upload link under Manage Reports on the Admin tab to upload your new report template.

1. On the Reports tab, click Branch Cash Confirmation Report.
2. Select a Branch.
3. Enter a date.
4. Click Generate.

Branch Progress Report

The Branch Progress Report consists of the summary statistics, staff performance metrics and loan details of the office for which the report is being generated. This report is typically used by management to monitor office progress and aid decision making. Because the report can be generated for any date in the past, comparative studies and trend analysis are possible.

Mockup/Spec: Branch Progress Report Mockup

Sample PDF: Sample Branch Progress Report

To generate a Branch Progress Report:

1. On the Reports tab, click Branch Progress Report.
2. Select a Branch.
3. Enter the date as of which you want information.
4. Click Generate.

To create your own Branch Progress Report template:

1. On the Admin tab, click View report templates under Manage Reports.
2. To use the default Branch Progress Report template as a starting point for creating your own template, click the Download link for Branch Progress Report.
3. Save BranchProgressReport.rptdesign locally.
4. Open the above file in BIRT RCP Designer and give it a new name, thus preserving a backup.
5. Edit your version of the .rptdesign file in BIRT.
6. In Mifos, use the Upload link under Manage Reports on the Admin tab to upload your new report template.

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

3. In the Query tool of your choice, write the SQL query. See SQL Query Tips for more information.
4. In BIRT, create a data set. In the Query window, paste in the query you wrote in step 3.
5. Create one or more input parameters in BIRT. See About Input Parameters below for more information.

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

3. 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.

A Common BIRT Error

The following error indicates that you are using a BIRT extension of Eclipse, instead of the BIRT RCP:

		ERROR [STDERR] Caused by: Error.DesignFileException.INVALID_XML - 1 errors found!
1.)  (line = 0, tag = null)
org.eclipse.birt.report.model.parser.DesignParserException
(code =  Error.DesignParserException.UNSUPPORTED_VERSION, message : The report
file of version "3.2.14" is not supported.)

To resolve this error, install the BIRT RCP.

BIRT Resources

• BIRT online help
• birt-exchange

Known Issues

• The names of the branch, LoanOfficer, and loan product parameter that Mifos gets from the database are in alphabetical order, but in the BIRT parameter list, it has no order. This is due to a bug in BIRT that has been fixed in the latest BIRT 2.1.3 build.
• According to the business logic, the best choice for the "loan number" column in report 1 is globalAccountNum. loan account id, another logical choice, is a sequential number in the database, each loan account id corresponds to one account. loan account id means nothing outside the Mifos database; !globalAccountNum does.
• The method, "!getTotalPrincipleAmountInArrears" does not consider lateness, it only returns the principal that is overdue.
• The default value of Branch parameter is "BSK", but the default value of LoanOfficer parameter is null.
• LoanOfficer and LoanProduct should display "All".