Hi,
We are considering implementing the Branch Report of Mifos using BIRT. We
were going through the existing implementation which uses Jasper Reports
and we have some questions regarding the "Staff Summary" section of the
report,
- There is a column PAR% (Portfolio At Risk). It is mapped to a field
"portfolioatrisk" which does not seem to be a percentage value. I've
reproduced the query and also broken it down into parts for clarity. Can
someone confirm that? We've also seen this column is empty in the PDF of
the report.
(select
truncate(((sum(loanage.overdue_principal+loanage.overdue_interest)/
sum(loanage.unpaid_principal+loanage.unpaid_interest))*100),5) as par from
loan_arrears_aging loanage,
(select customer_id,loan_officer_id from customer where
branch_id=$P{branchid} and discriminator='CLIENT' ) custid where
loanage.customer_id=custid.customer_id
and loanage.days_in_arrears>30 and custid.loan_officer_id=p.personnel_id
group by custid.loan_officer_id ) as portfolioatrisk
This can be broken down as:
(select truncate(PERC ,5) as par from loan_arrears_aging loanage,
TEMP_CUST_ID where loanage.customer_id=custid.customer_id
and loanage.days_in_arrears>30 and custid.loan_officer_id=p.personnel_id
group by custid.loan_officer_id) as portfolioatrisk
where:
PERC = ((NUM / DEN)*100)
NUM = sum(loanage.overdue_principal+loanage.overdue_interest)
DEN = sum(loanage.unpaid_principal+loanage.unpaid_interest)
TEMP_CUST_ID = (select customer_id,loan_officer_id from customer where
branch_id=$P{branchid} and discriminator='CLIENT' ) custid
- When determining customer level (client, group or center), is there a
difference between using customer_level and discriminator columns?
- In above query, is it really necessary to have 5 digits after decimal
point as indicated by the truncate() function?
- When calculating TMG (Total Monthly Group), the below query seems to do
a redundant join with the personnel table:
select count(cust.customer_id) TMG
from personnel pers1,customer cust
where pers1.personnel_id = cust.loan_officer_id
and cust.customer_level_id=2 and
month(cust.mfi_joining_date)=month(current_date)
and pers1.personnel_id=p.personnel_id -- THIS LINE is redundant??
group by pers1.personnel_i as thismonthtotalgroup
Pramod Biligiri,
ThoughtWorks-------------------------------------------------------------------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2008.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
