Versions Compared

Old Version 7

changes.mady.by.user johnwoodlock

Saved on

compared with

New Version 8

changes.mady.by.user johnwoodlock

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Panel

select wb.past_due_description, 
count(distinct(hla.customer_key)) as clients, count(distinct(hla.loan_account_key)) as loans,
sum(total_in_arrears) as total_in_arrears,sum(total_outstanding) as total_outstanding

from hist_loan_arrears hla
join dim_date endperiod on endperiod.date_key = hla.as_of_date_key
join dim_arrears_band_weekly wb on wb.arrears_band_weekly_key = hla.arrears_band_weekly_key
join dim_personnel lo on lo.personnel_key = hla.loan_officer_key
join dw_mfi_configuration config on config.mfi_configuration_key = 1

where endperiod.date_value = date('2010-03-31')
and lo.display_name  = 'M P SmithXXX'
and hla.days_in_arrears > config.loan_lateness_days

group by wb.past_due_description
order by wb.arrears_band_weekly_key

...

In the mifos standard reports being developed there's a little of the vanilla reporting above, quite a bit of the loan arrears reporting but mostly ite customer and account based.  It's currently a little more involved than the vanilla but the 2 main positives are that this 1) still better than querying the mifos application database and, most importantly, 2) it caters for customer hierarchy changes (loan officer movements, group and client transfers etc) as well as customer and account status changes.  Basically,historical reporting like comparing one time period to another is made possible.
The main tables (dim_customer, dim_loan and dim_savings) each have links a full mifos hierarchy... branch, loan officer, center... but the thing which is different from normal mifos tables is that one customer, for example a client, will have a number of entries in dim_customer.  Each time its hierarchy changes (e.g. client or group transfer, loan officer assignment) or it has a status change a new entry is added.  The entry are valid for a period of time which is denoted by a pair of date fields called valid_from and valid_to.  If you want to find the unique version of the client that is relevent on 2010-01-01 you need to add the following... 
and valid_from <= date('2010-01-01')
and valid_to > date('2010-01-01')
It took me a little while to get used to this.  Also some of the queries felt a little complicated but still better than working with the mifos application database structure.  Below are a couple of examples from the standard mifos reports.  If you have the Pentaho Report Designer you can get the .prpt files from git://mifos.git.sourceforge.net / mifos/bi and look at the queries.

a) as of 2010-01-01, how many centers did loan officer XXX manager

Panel

select count(distinct(customer_id)) as centers_managed

from dim_personnel p
join dim_customer c on c.loan_officer_key = p.personnel_key

where p.display_name = 'XXX' 
and p.valid_from <= date('2010-01-01')
and p.valid_to > date('2010-01-01')

and c.customer_status = 'CenterStatus-Active'
and c.valid_from <= date('2010-01-01')
and c.valid_to > date('2010-01-01')

...

Panel

select count(distinct(c.customer_id)) as clients_with_loans
from dim_personnel p
join dim_customer c on c.loan_officer_key = p.personnel_key
join dim_loan l on l.customer_key = c.customer_key
where p.display_name = 'XXX' 
and p.valid_from <= date('2010-01-01')
and p.valid_to > date('2010-01-01')

and c.customer_level_id = 1

and l.loan_status in ('AccountState-ActiveInGoodStanding', 'AccountState-ActiveInBadStanding')
and l.valid_from <= date('2010-01-01')
and l.valid_to > date('2010-01-01')

c) as of 2010-01-01, what were the repayments on active loans that loan officer XXX dealt with.  Here's where I struggle a bit.  It's a little struggle technically but really its a bigger struggle to make sure you are asking the right question.  The query below only look at active loans managed by a loan office at a certain point in time... 

(

select

ifnull(sum(principal_amount),0.0)

as

principal_paid,

ifnull(sum(interest_amount),0.0)

as

interest_paid

from

fact_loan_repayments

paid


join

dim_loan

l

on

l.loan_account_key

=

paid.loan_account_key


join

dim_personnel

p

on

p.personnel_key

=

paid.loan_officer_key


join

dim_date

d

on

d.date_key

=

paid.action_date_key

where

p.personnel_id

= ${accounts_summary_personnel_id}&nbsp; and

= 'XXX'
and p.valid_from

<=

date(${accounts_summary_end_date}) and

 date('2010-01-01')
and p.valid_to

> date(${accounts_summary_end_date}) and

> date('2010-01-01')

and d.date_value

&nbsp;<= date(${accounts_summary_end_date}) and

 <= date('2010-01-01')and l.loan_status

in

('AccountState-ActiveInGoodStanding',

'AccountState-ActiveInBadStanding')


and

l.valid_from

<=

date(${accounts_summary_end_date}) and

 date('2010-01-01')
and l.valid_to

> date(${accounts_summary_end_date})) b,

> date('2010-01-01')

Panel
Wiki Markup