Navigating the Mifos database and understanding the Mifos schema has a steep learning curve. There's a large amount of rich and powerful data in Mifos that can be difficult to extract if you're not familiar with the data model. This page is targeted towards Mifos Users, Specialists, report-writers, and developers needing help extracting information from the database.
Use the queries on this page to:
These queries can be inserted into your report designer tool or run directly from MySQL (through SQL Development, Query Browser, etc) to quickly pull up clients and other needed data.
See also: Mifos database naming conventions
Queries listed here reflect the data model for the Mifos database.
Query |
Additional Description: |
Applies to Database Version |
Date Added |
Requested By |
Submitted By |
|||
---|---|---|---|---|---|---|---|---|
Number of Loans outstanding
Number of Loans outstanding, by Branch
Number of Loans Outstanding, by Branch and by Loan Officer
|
|
|
Nov 11, 2010 |
|
John Woodloc, Ryan Whitney |
|||
Branchwise outstanding principal as of today
|
|
|
Nov 11, 2010 |
|
John Woodlock |
|||
Branch-wise new loans (count and amount) disbursed during a period
In place of adddate(curdate(),-120) and curdate(), you can place your from and to dates. |
|
|
Nov 11, 2010 |
|
Ramya Toshniwal |
|||
Loan outstanding for each member with its group name, center name, branch name i.e. full path
|
|
|
|
|
Trilok J. Pandya |
|||
Number of loans disbursed and amount disbursed during period
|
Added in display of Branch Name |
|
Nov 11, 2010 |
|
Lokesh Sajjan & Ramesh Nayak |
|||
Number of loans disbursed and amount disbursed during period, group by Branch and Loan Officer |
SELECT ac.office_id, office.DISPLAY_NAME as OFFICE_NAME, lookup_value.LOOKUP_NAME AS Title, personnel.DISPLAY_NAME AS LOAN_OFFICER, COUNT(DISTINCT(LA.ACCOUNT_ID)) LOANS_DISBURSED, SUM(COALESCE(LOAN_AMOUNT,0)) LOAN_AMOUNT_DISBURSED FROM LOAN_ACCOUNT LA INNER JOIN ACCOUNT AC ON LA.ACCOUNT_ID= AC.ACCOUNT_ID INNER JOIN customer C ON C.CUSTOMER_ID=AC.CUSTOMER_ID INNER JOIN office ON AC.OFFICE_ID = office.OFFICE_ID INNER JOIN personnel ON AC.PERSONNEL_ID = personnel.PERSONNEL_ID INNER JOIN lookup_value ON personnel.TITLE = lookup_value.LOOKUP_ID WHERE AC.ACCOUNT_STATE_ID NOT IN ('10','2','3') AND (LA.DISBURSEMENT_DATE between '2010-01-05' AND '2011-03-10') GROUP BY AC.OFFICE_ID, LOAN_OFFICER |
| | 1.6.2 | | | Ryan Whitney
|
Branchwise outstanding principal as of today
|
|
|
Nov 11, 2010 |
|
Lokesh Sajjan & Ramesh Nayak |
|
Active Loans by Date
This will work for MFIs that don't use back-dated transactions. See mailing list discussion on 7/28. |
|
|
Jul 28, 2010 |
|
Lassâad Ben Hadj |
|
Active loans as on a certain date
|
|
|
Jul 28, 2010 |
|
Sam Birney |
|
Active Clients on a particular day
|
|
|
Jul 28, 2010 |
|
Udai Gupta |
|
Client Exit information between two dates
|
|
|
Jan 13, 2011 |
|
John woodlock |
|
i want list of all clients on particular date disbursement amount, according to its loan fee, mem fee and insurance fee. |
|
|
|
|
|
|
Active loan accounts as of current date of a particular client/group/center/branch/organization:
Note: The And statements are optional and are required when you want to view any specific client/center/group/office active loan accounts. |
|
|
December 2010 |
|
SunGard |
|
List of active savings accounts as of current date for a particular client/group/center/branch/organization:
Note: The And statements are optional and are required when you want to view any specific client/center/group/office active savings accounts |
|
|
|
|
|
|
|
|
|
|
|
|
Queries listed here reflect the data model for the data warehouse being released with Leila E.
Query |
Additional Description |
Applies to Data Warehouse Version |
Date Added |
Requested By |
Submitted By |
|
---|---|---|---|---|---|---|
No. of active (in good and bad standing) loans for an mfi:
|
|
|
Nov 11, 2010 |
|
John Woodlock |
|
Active (and on hold) Clients on a Particular Day
|
|
|
Nov 7, 2010 |
|
John Woodlock |
|
|
|
|
|
|
|