Reports Improvements

Owned by Former user (Deleted)
Last updated: Jan 06, 2011 by Michael VorburgerVersion comment
12 min read

Table of Contents

Target Users

Mifos Specialists and internal MIS staff are the targeted users for creating/editing/customizing report templates. These specialists are typically very tech savvy and are comfortable with applications like Crystal Reports-- but they do not have java programming skills nor do they have advanced SQL skills. Khadija Shamte (Adept Systems), Naganand (GK), and Lassaad (Enda) match the profiles of the types of users we want to support.

MFI Deployments fall into 2 categories: those that require additional features and development work in order to satisfy their requirements and those that don't. During 2007, our focus will be primarily geared to deploying Mifos at MFIs that require little to no additional development work. In order to achieve this, the reporting module should provide enough flexibility and be easy enough to use so that the Mifos Specialists and internal MIS staff can build and customize reports without additional development support. Note-- this is also important since we're realizing that it is challenging to find java developers with the high caliber programming and communication skills necessary to contribute to Mifos

Reporting Needs

There is significant overlap on the data that MFIs report on. By building reports for 3-4 specific MFIs, we should be able to cover the 90% of the data needed by MFIs to create their reports-- provided MFIs have the flexibility to customize where and how this data is displayed in the ways described below. MFIs are passionate about the look/feel of their reports and about which data fields are displayed on which reports. They will want their reports to match the format/layout of the reports they're been using prior to Mifos. In addition, they will have particular donor reports (in specific formats) and regulatory reports (also in specific formats) that they will need to develop. The reporting module should give our targeted users the ability to develop these reports (either by customizing current reports or by building new ones) without requiring java development. Mifos will ship with a standard set of reports that have been built based on the reports used by our first 3-4 Mifos users. We do not anticipate that these reports will frequently be used "as-is" by new MFIs deploying Mifos. Instead, these built in reports will serve as a starting point upon which MFIs can modify/customize.

Step One: Build a standard set of reports to ship with Mifos

  • Ship Mifos with a set of pre-built reports: _ First set of reports based on Jitegemea's reporting requirements. We may also start with CGAP's list of standard report. _ Reports should be available without administration-- ie, parameters already defined and no upload required. * Reports will come with a pre-defined and intelligent set of filters.
    • For example, generating a report for a particular branch should involve picking the branch name from a list not typing a branchId (internal database number which is not easy for the user to find)).
    • List of branches should only show active branches-- not all offices
    • Another example, generating a report for a LO should involve selecting the branch name from a drop down and then the Loan Officer name from a drop down (which should display only loan officers associated with that branch).
    • See Data and Report Examples for examples on the type of data that will be queried in these reports.
  • Downloadable as acrobat files
  • Data limited to system user's Data Scope . * For example, Joe the LO shouldn't be able to generate reports about Mary the LO's clients-- he should only be able to generate report data on those clients within his datascope.
  • Integrated with other Mifos configuration items (ie label renaming, fiscal year/week, accounting rules, etc) * Question: is this required at the reports template level, or just in the reporting UI level?
  • Better error handling. Too many error conditions currently produce a blank page for a report, leaving the user to guess what went wrong.
  • Improved testing (see ReportsBusinessServiceTest).
  • Reports UI should be integrated into Mifos UI (similiar to how Jasper is currently integrated into the Mifos UI)

Lower priority, not needed immediately:

  • Downloadable in some sort of format that can be imported into other systems (csv, tab delimited)
  • Report interface should be localizable. * Question: is localization required at the reports template level, or just in the reporting UI level? Assuming reports themselves don't need to be localizable since MFI can make translations to the report templates if needed.
  • Reports should ultimately be accessible from other parts of the site. ie, from the Loan Detail Page, user should be able to click links and, without going to the reports module, generate loan reports for that loan. This will be especially needed for generation of payment vouchers, deposit slips, loan repayment schedules, etc, that will be easiest to generate/print directly from the account detail pages.

Step Two: Allow MFIs to Create and Customize Reports and Reports Module

As noted above, MFIs need to be able to customize the layout of reports and need to be able to add/remove fields from reports. Additionally, b/c we plan to ship Mifos with a robust set of reports, each of which contain a robust set of data and filters-- MFIs need a way to both trim down the contents of these reports and to delete/hide reports so that the report options are manageable and appropriate for their end-users (to avoid confusion). See examples in the list below. In addition, they need to be able to customize the queries used in reports to include/exclude certain data. MFIs are very flexible and creative about how they map their processes into the Mifos system-- however these work-arounds often impact reports so queries need to be editable in order to accomodate these work-arounds. In some cases, the amount of "customization" will be so extensive that the MFI will essentially be creating a brand new report. I don't see "customization a report" and "creating a new report" significantly different use cases-- they are just points along the same spectrum.

Allow MFIs to Create and Customize Reports

  • Add/remove data fields displayed on reports
  • Ability to build in field level filters into reports (ie, these filters are applied everyime a report is run). This is probably the most important functionality-- and if we can support this, then this will significantly reduce the number of MFIs that would require development for their reports needs and give them a great deal of flexibility to create reports for their use cases. _ Example: Grameen Koota excludes a particular loan product (their "emergency loan product" for which they charge zero interest) from all their loan portfolio calculations. They should be able to edit (without java skills) a portfolio summary report to exclude this loan product from various queries. This scenario of needing to filter which products should/shouldn't be included in certain queries is common. _ Adding a filter to an existing field. ie: we have a data field that displays "total outstanding loan amount"-- MFI can "copy/paste" this field into the report but filter it by gender: "total outstanding loan amount for women clients". Other examples: they want to filter by poverty status "Outstanding loans for customers marked very poor/poor/non-poor"; by loan cycle, by loan product, etc. _ Example: our Kenya MFI has both centers that contain 4-5 groups and also groups that exist outside of centers. Mifos doesn't support this mixed type of structure, so the MFI will create a dummy center for those groups that have no center. When they report on the # of centers, they want to exclude all centers that contain only 1 group. Similarly, GK created several groups to act as a "holding tank" for clients before they were assigned to their final group. When reporting on the number of groups, they wanted to exclude any groups that contain more than N clients. There will be a variety of these types of scenarios and MFIs (without using java skills) should be able to adjust their reports to support these use cases. _ Example: Excluding entities from calcualtions based on a certain value of a custom field. Another way the MFI: The MFI might use one of Mifos custom fields to mark which centers are real vs fake. Custom fields are user defined fields that can be attached to a variety of entities (centers, groups, clients, etc). In this instance, they might define a custom field as "Center Validity" and use 1 to mark fake centers. All queries calculating the number of active centers, closed centers, etc-- would not include centers with a "center validity" marked as 1. MFIs use custom fields quite regularly to deal with work-around issues. Since we can anticipate what the values of these custom fields will be, there needs to be a way in the reports editor itself to filter these queries. * Click here for more field-level filter requirements.
  • Changing report layout: _ Ability to create their own "MFI" template for reports that matches their previous report designs _ from horizontal to vertical layout * Adding organizational Logos
  • Add/remove filters and groupings to existing reports. Click here for filter and grouping examples.
  • Editing report titles

Allow MFIs to Customize Report Module

  • Ability to delete (or hide) reports from the reports module
  • UI interface to create a report titles when uploading.
  • UI interface to assign a report to an existing category
  • UI interface to edit report categories and create new report categories
  • Automatically create RolesPermissions permissions for uploaded reports and new report categories.
  • Administration of Reports UI should be moved to admin tab

Envisioned Process: When New MFIs Deploy Mifos

  • During the gap analysis process, MFIs will review standard set of reports to determine if they can be customized (as described above) to meet their needs
  • If there are required data not included in the pre-built reports, they will identify this gap
  • GF will work with the MFI to determine who/how support to query this data will be built
  • Support for querying this data will be rolled out to the Mifos application
  • Because the reporting requirements for MFIs are so similiar, the amount of development required for new reports should significantly decrease over time.
  • After 4-5 deployments, it should be very unusual for an MFI to require development for their reporting needs; the Mifos Specialist and the MFI's MIS team should be able to address all of the MFI's reporting needs with current Mifos functionality.

Reporting "Stories"

Story 1: Build Detailed "Aging Portfolio at Risk" Report

See `Reports to Build in BIRT <https://mifos.dev.java.net/files/documents/5045/57432/file_57432.dat/Reports%20to%20Build%20in%20BIRT%208%20may%2007.xls>`_ for UI layout and requirements for this report.

  • User Navigates to Reports Tab and Clicks on "Detailed Aging Portfolio at Risk" link
  • Mifos checks to ensure that user has permissions to view this report
  • User is displayed "generate report" page with drop downs to select branch and then LO with optional parameter to select loan product: _ LO drop-down has "All" option followed by the list of active loan officers. _ Loan product drop down has "All", and specific loan product instances. The drop down lists all loan products the exist for the entire MFI (even inactive ones, since there can still be active loan accounts for inactive products). * Branch and LO selections are displayed as per the user's data scope:
    • If the user is a LO, then the Branch defaults to the user's branch and the LO name defaults to the name of the user.
    • If the user is a branch manager, the Branch defaults to the user's branch and the LO drop down defaults to "All" but user can then select a specific LO from the list.
    • If the user belongs to the head office, the user must first select a branch. Once selected, the LO drop down is populated with that Branch's loan officers. The LO drop down defaults to "ALL"
    • Optional: "As of date:" field which defaults to the previous day's date, giving the user the option to generate the report for an early date. The ability to change the date of the report is a less important feature and can wait until later to implement. All reports are generated as of the end of the day specified (but may need to happen only after the batch job for calculating arrears is completed: Arrears Aging
  • User clicks "Submit" and is then displayed a PDF formatted report.
  • Report generates a PDF that displays all loans in active-bad standing state of a branch and (optional) per loan product; Loans should be grouped by Loan Officer. * Optional: If a client has multiple delinquent loans, ideally those loans would be listed next to each other-- and not scattered across the report.
  • If no results are found based on the selections, an error is displayed on the page "no results found".

Story 2: Build "Active Loans by Loan Officer" Report

See `Reports to Build in BIRT <https://mifos.dev.java.net/files/documents/5045/57432/file_57432.dat/Reports%20to%20Build%20in%20BIRT%208%20may%2007.xls>`_ for UI layout and requirements for this report.

  • User Navigates to Reports Tab and Clicks on "Active Loans by Loan Officer" link
  • Mifos checks to ensure that user has permissions to view this report
  • User is displayed "generate report" page with drop downs to select branch and then LO. These selections are displayed as per the user's data scope. _ If the user is a LO, then the Branch defaults to the user's branch and the LO name defaults to the name of the user. _ If the user is a branch manager, the Branch defaults to the user's branch and the user can then select a LO from those branches. * If the user belongs to the head office, the user must first select a branch. Once selected, the LO drop down is populated with that Branch's loan officers.
  • User clicks "Submit" and is then displayed a PDF formatted report.
  • Report displays all loans in active-good standing and active-bad standing for a loan officer as per the date the report is generated. Loans should be grouped by center with all individual loans at the end (sorry, don't have a mock-up of the report that groups by center). * Optional: If a client has multiple loans, ideally those loans would be listed next to each other. This can be implemented in a future story.
  • If no results are found based on the selections, an error is displayed on the page "no results found".

Story 3: Build UI Screens to Upload a New Report

  • User clicks "Upload report template" from Admin page
  • User is taken to a page to title a report, assign a report category (the bolded header under which the report will display on the "Reports Tab"), and select a BIRT template for upload. Note: In order to have the title entered in Mifos display on the report, the report should be built by making the title a parameter in the report. The other option is for the user to ensure they enter the same report title into Mifos as was built in the report template. See Issue #1392 .
  • User previews selection
  • User submits report for upload
  • New report title is displaed on "Reports Tab" under the appropriate category
  • Permissions should be added for this activitity
  • See UI wireframes located in SVN under \documents\ui-workspace\29_May_2007\29_May_2007\

Edit Current Reports

  • User clicks on "View reports" link from Admin page
  • User is taken to a page that lists all active and hidden reports with options to view report template, edit report details, and hide reports
  • User can Edit a report _ User can click on "Edit" and is taken to a page where they can change the report title, report category and report template. _ User can click "Submit" and changes to the report detail will be saved and those changes will be reflected on the Reports Tab and permissions page
  • User can Hide a report _ User clicks on "Hide" and is taken to a page where they confirm whether/not they want to hide the report _ User clicks on "Submit" and report is hidden from the Reports Tab and permissions for the report are also removed from the permissions page
  • See UI wireframes located in SVN under \documents\ui-workspace\29_May_2007\29_May_2007\ (Note-- the UI screen needs to be updated)

Story 5: Clean-up of issues in previously created reports

  • Populate Loan Officer drop-down on "Portfolio at Risk" report. Ensure default is set to All
  • Default Loan product to ALL on "Portfolio at Risk" report.

Story 6: Integration of Birt Viewer UI into Mifos UI

  • User clicks on "Reports" tab and sees available reports listed under their appropriate category
  • User clicks on report title and is taken to a page within the Mifos UI that displays report parameters
  • User clicks "Generate" and a PDF window pops up with the report.
  • See UI wireframes located in SVN under \documents\ui-workspace\29_May_2007\29_May_2007\ . Click on reports tab and "Detailed Aging portfolio at risk"

Story 7: Building additional reports

  • skipped - see below

Story 8: validate and document ability to ship data sources separate from Mifos application

  • MFI deploys Mifos
  • MFI downloads "report pack" containing a new datasource and 2 new reports that use the new data source
  • MFI follows instructions to deploy report pack
  • new datasource and reports are available to MFI in Mifos

Story 9: Build collection sheet report in BIRT

  • replicate the existing Jasper-based collection sheet report in the BIRT system

Story 10: Build additional reports and corresponding data source libraries

Story 11: Performance testing and tuning (including investigation of multi-threading)

  • develop performance metrics for BIRT reporting system
  • work with community to test performance of reporting
  • improve reporting performance to agreed metrics
  • investigate whether a multi-threaded approach will work to improve performance

Story 12: Bug fixes as needed

  • as bugs are identified in the BIRT reporting system, fixes are created and patches submitted

Unscheduled Work

  • Displaying Loan Officer and Product List in Alphabetical Order
  • Viewing Report Template off of "View Reports" Page (i.e. printing passbooks, etc.) – Expected in 1.1
  • Add ability to generate "Portfolio at Risk" summary data for dates in the past
  • Ability to create and edit report categories
  • Documentation for building BIRT reports
  • Performance testing
  • Ability to "activate" a hidden report
  • Scheduled Reports (including emailing reports and report links)
  • Ability to delete a report
  • Ability to run a report in the background
  • Ability to export to additional formats beyond PDF, especially HTML, CSV, and Excel
  • Full data warehouse

See also