Introduction

This page lists all acceptance criteria for new reports in Pentaho. These must be followed before a report can be considered done - production quality and shipped to the customer.

Terminology

• Mockups should be updated with this, but if not, all heading names should follow Mifos terminology, or if there is a label, should use the Label name.
• Example:  Loan Officers may be called Field Officers at the MFI, but the report should use Loan Officer to denote this.  (right now)..  In the future, we will want to allow the MFI to specify some of this for their reports separately.
• Example:  MFI might call "Regional Office" something like "State Office", and they can define this in Mifos Labels.  References to Regional Office should say State Office.
• The same applies also to Filter names.  Filters should grab either Mifos terminology or the label if that exists.
• Right now it is not possible to pull label names but at least then the terminology we use in Mifos should be in the report.
• File Naming
  • The name of the files for a report should have the same name as the report itself. The title of the report should match this. They should be consistent.
  • The name displayed in Pentaho should also be the same.
  • Remove the word "report" from the title.

Common Terms

• Arrears Amount - instead of just Arrears, or Amount in Arrears
• Principal Outstanding - instead of Outstanding Principal
• Loan Amount - instead of Portfolio Amount, etc
• Disbursal - instead of Disbursements

Parameters

• Filters should be listed in the order in the mockups
• They can be left to right
• Each parameter should have a "Select One" at the top of each dropdown
• Date dropdowns should have a From Date dropdown and a To Date dropdown
• Date dropdowns should default to today's date
• Reports should not run until the "View Report" button has been clicked 
• If a filter has an "ALL" selection, that should be at the top after "Select One".
• Then, all choices should be listed in alphabetical order.
• If it's at all possible, display a msg to show that a report is loading if a report is a bigger one
• Parameters should use CDF - see Loan Classification by Product report for example

Data

The mifos database is best at showing 'current' information.  The D/W database is best at showing historical information.

You can get a history of disbursed loans from the mifos database.... but if your report wants to know which loan officer was managing the clients at the time of disbursal or which branch it was in... then the D/W is the place to look as the mifos database will always show you the 'current' loan officer and branch whereas the D/W includes loan officer and customer hierarchy changes baked in.

Guidelines

If the report you are writing/specing relies on current data

1. It has to query the mifos database (actually schema because many MFI's will run with replication and its the replicated database that reports should run from).
2. The 'spec' should be as simple as possible (overwhelmingly simple if possible i.e. couple of columns or simple 'listing' reports).  Typically a very bad idea to write queries getting historical data from the mifos database as its extraordinary difficult to identify hierarchy changes and status changes (and that's not issuing a challenge).

If the report you are writing/specing is 'analytical' and does lots of number crunching over periods of time - its a D/W report

What if its a D/W report and the field you are looking for isn't there?  It can be added if it makes sense (but don't want every field in the D/W).  But if you absolutely positively have to have it....and its more important that knowing what loan officer was assigned etc.... drop the need to show the correct loan officer / customer hierarchy from the report and write it off the mifos database.

Generally, it should be easier (and quicker) to get data from the D/W database than the mifos database.  If you are new to both... the D/W is about 10 times smaller in terms of tables and columns to become familiar with.  Also, reports are more 'protected' from mifos application changes than the mifos database because there is an 'ETL' (data conversion) process in the middle which has to handle the mifos data changes and quirks.

If you are reporting on arrears or overdue loans... D/W has been optimised for that (however, if you want a quick look at 'right now'.... the mifos table loan_arrears_aging is up to date for today).

Examples

Comparing a branch/loan officer/center/group performance from one month to another - D/W database

Showing the number of disbursals so far today - mifos database

show a list of disbursed loans over the past month - D/W database but if you are happy with showing current loan officer/branch/center/group values... then you could do it from mifos database

Dashboards: current dashboard data should come from mifos database.  Dashboards showing data older than today need to look at the D/W database.

Report Design

• See Loan Classification by Product v2 mockup in India Cloud folder for design example
• Title of Report should be at the top
• There should be a heading box with the parameters that were selected.  They should just list the name of the filter, and the filter selected.
  • Example: If there is a date range to select in the parameters, the header box should just list From: 2010-01-23 To: 2010-01-24.  No need to say Date Range
  • Filters listed in the box should be aligned and no need for the left and right blue boxes.
  • If there's an Office filter, it should be listed in the box as "Office: Branch 1" instead of "Branch:" in some reports
• If a report has more than 7 columns, the report should be in Landscape mode. Else, Portrait mode
• Report should always fit on A4 sheet
• If there are multiple rows of data, have alternating shading of the rows for easier viewing
• Headings of tables should follow the example
• There should be page numbers on each page.
• If a table spans more than one page, the headings should be repeated on the subsequent pages.
• Versioning - the version of the report should be displayed in the bottom left corner of each page.  This helps in determining which report the MFI is looking at in case there are problems.  These versions should be tagged w/ the report in the git repository.
• In the footer also list who ran the report.
• In the footer list report date as well
• Example footer: printed by: Kay on 2010-01-11.
• MFI Name should be listed on the top right (same row as the Mifos logo) - there is no need to call out "MFI Name" - just listing the name of Head Office should be enough
• Totals at the bottom should be bolded but have a white background.  (No dark blue)
• Negative numbers should be listed with (#) - ie, a -500 should be listed as (500) in the report.
• Fonts and sizes should be consistent across each type.  For example, all column headings in one report should have the same font and size.  All parameters listed in the heading box should also be consistent (but not necessarily consistent with column headings).
• Capitalization should also be consistent.  If there is a "# of centers" and there's also a "# of Groups" then "# of centers" should be changed to "# of Centers".  Also column headings should be capitalized.  IE "# of Groups" instead of "# of groups" or "# of GROUPS"

Localization

• Each report should be localizable.  This means that there should be a place with all the strings that can be translated and then plugged in for a specific language.  The reports themselves do not need to be already translated (yet)
• (P2 - post 1.0) Filters should be localizable as well.
• The properties file should be outside of the report zip:
  • You have to use resource-labels, resource-fields and resource-messages. However, you shouldn't set 'resource-identifier' field in these items.
  • You have to add the following property to your report's settings.xml:

    <property name="org.pentaho.reporting.engine.classic.core.ResourceBundle"> org.mifos.bi.reports.yourReportName </property>

  • Please add your properties files to mifosbi repository under reports/i18n/org/mifos/bi/reports/
  • You have to put (copy or link) this org directory in the classpath of your report-designer/biserver-ce

Output Formats

• Reports must be available in PDF and ideally Excel if the report allows.  If the report is one table with clearly defined rows and columns, it should be optimized for Excel.
• Optimized for Excel means if there's a table of information, each column is a heading and the columns are clearly defined and one follow the other (no random columns in between).  Same for rows of data
• It is possible to do simple functions on a column of data in the Excel format (like summary).
• It is possible to sort and filter by columns in Excel.
  • table containing data in the generated xls file do not contain any unnecessary columns/rows. This means, that we can simply select the whole table and use the standard Excel functionality to sort/filter data (Data->Sort and Data->Filter).

Acceptance Tests

• Acceptance Tests must be written
• http://mifosforge.jira.com/wiki/display/MIFOS/Pentaho+Report+Testing