[Mifos-developer] REPORT QUERY

[Peter oliver]

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.