Hi,
If you use the parameter like startDateSelect then use the '${startDate}'
where you need the input to go
Similarly If you use the parameter like endDateSelect then use the
'${endDate}' where you need the input to go
For example from the above example you shared you can use
WHERE
l.disbursedon_date <=*'${endDate}' *
AND l.loan_status_id = 300
ORDER BY
l.disbursedon_date DESC
and use the parameter *endDateSelect *to enter the date in the reporting
section
Similarly you have to do it on all places where you need the input values
to be used in the report.
[image: image.png]
Regards,
Bharath
Lead Implementation Analyst | Mifos Initiative
Skype: live:cbharath4| Mobile: +91.7019635592
http://mifos.org <http://facebook.com/mifos>
<http://www.twitter.com/mifos>
On Thu, Mar 20, 2025 at 4:29 PM Peter oliver <peteroliver...@gmail.com>
wrote:
> Hello @all,
>
> I am trying to create a loan report in the mifos system using the Query
> below but it only pulls the hardcoded "transaction_date" filter despite
> adjusting thetransaction_date parameter to "@filter_date".
>
> Query:
>
> SELECT
> l.account_no AS 'Loan Account Number',
> c.display_name AS 'Client Name',
> lp.name AS 'Loan Product',
> l.principal_disbursed_derived AS 'Disbursed Amount',
> (l.principal_disbursed_derived - COALESCE(t.principal_paid, 0)) AS
> 'Outstanding Principal',
> (COALESCE(s.interest_accrued, 0) - COALESCE(t.interest_paid, 0)) AS
> 'Outstanding Interest',
> (COALESCE(s.fee_accrued, 0) - COALESCE(t.fee_paid, 0)) AS 'Outstanding
> Fees',
> ((l.principal_disbursed_derived - COALESCE(t.principal_paid, 0)) +
> (COALESCE(s.interest_accrued, 0) - COALESCE(t.interest_paid, 0)) +
> (COALESCE(s.fee_accrued, 0) - COALESCE(t.fee_paid, 0))) AS 'Total
> Outstanding',
> l.disbursedon_date AS 'Disbursement Date'
> FROM
> m_loan l
> JOIN
> m_client c ON l.client_id = c.id
> JOIN
> m_product_loan lp ON l.product_id = lp.id
> LEFT JOIN (
> SELECT
> loan_id,
> SUM(principal_portion_derived) AS principal_paid,
> SUM(interest_portion_derived) AS interest_paid,
> SUM(fee_charges_portion_derived) AS fee_paid
> FROM
> m_loan_transaction
> WHERE
> transaction_date <= '2024-12-31'
> AND is_reversed = 0
> GROUP BY
> loan_id
> ) t ON l.id = t.loan_id
> LEFT JOIN (
> SELECT
> loan_id,
> SUM(interest_amount) AS interest_accrued,
> SUM(fee_charges_amount) AS fee_accrued
> FROM
> m_loan_repayment_schedule
> WHERE
> duedate <= '2024-12-31'
> GROUP BY
> loan_id
> ) s ON l.id = s.loan_id
> WHERE
> l.disbursedon_date <= '2024-12-31'
> AND l.loan_status_id = 300
> ORDER BY
> l.disbursedon_date DESC
>
>
> How can i make the transaction date open to allow any preferred date
> filters?
>
> Regards.
> Mifos-developer mailing list
> mifos-develo...@lists.sourceforge.net
> Unsubscribe or change settings at:
> https://lists.sourceforge.net/lists/listinfo/mifos-developer
_______________________________________________
Mifos-users mailing list
Mifos-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/mifos-users
