Jump to content
Changes to the Jaspersoft community edition download ×

  • Sample Report Using Recursive Sub Report Calls to Calculate Amortization Schedule Based on Basic Loan Information

    tchen

    By tchen

      • Features: Reports Version: v5.6 Product: JasperReports® Library
     Share

    Question:

    The User has a loan table that contains loan amount, loan term, and loan interest rate information. The User wants to have a report that, in addition to listing the basic loan information, also calculates and lists a loan amortization payment schedule (see expected report output below).

    		[toc]

    screenshot(1).png.0b12589398cb21345bd836a71cda3eec.png

    The User is asking if it is possible to use a loop in the report design to list the amortization schedule for each loan through a report calculation “solely from the 3 values supplied”.

    Answer:

    The key issue here is how to provide amortization detail data for JasperReports to layout in the report section. JasperReports does not have a built-in feature to do the calculation looping (such as an amortization schedule function). Users have to do this calculation themselves. There are a couple of approaches that a user can consider, but each has its technical challenges:

    1. If the user is an expert in SQL query coding, they can write a complex query, or a stored procedure, to produce the amortization detail data to feed the report. Please refer to this wiki article to see the complex query approach.
    2. If the user is proficient at Java coding, they can write a Java Scriptlet to calculate the amortization matrix and have JasperReports use it as the resultant dataset to generate the report.
    3. If the user feels comfortable with recursive programming concepts, they can use this technique to call a sub report repeatedly to process the amortization schedule until the principal balance reaches zero.

    For first two approaches, users can review relevant technical material and/or consult with their in-house experts to find a solution. As for the recursive sub report approach, they can review the following report sample designed to achieve the requirement.

    Design Approach:

    reportdesign.png.da196ed3eafa6832b8b30a983ac4eccb.png

    Report Design (main report):

    <?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" 
              xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
              xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" 
              name="report_loan" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" 
              rightMargin="20" topMargin="20" bottomMargin="20" 
              uuid="8c912c2c-1922-40a9-bebd-684773dbd3a2">
    <property name="ireport.zoom" value="1.0"/>
    <property name="ireport.x" value="0"/>
    <property name="ireport.y" value="0"/>
    <parameter name="calculation_precision" class="java.lang.Integer" isForPrompting="false">
        <defaultValueExpression><![CDATA[new Integer("12")]]></defaultValueExpression>
    </parameter>
    <parameter name="SUBREPORT_DIR" class="java.lang.String" isForPrompting="false">
        <defaultValueExpression><![CDATA[""]]></defaultValueExpression>
    </parameter>
    <queryString>
        <![CDATA[select 100000.00 as loan_amt, '05months' as loan_term, 4.75 as loan_rate
union all
select 20000.00 as loan_amt, '2months' as loan_term, 5.0 as loan_rate]]>
    </queryString>
    <field name="loan_amt" class="java.math.BigDecimal"/>
    <field name="loan_term" class="java.lang.String"/>
    <field name="loan_rate" class="java.math.BigDecimal"/>
    <variable name="payment_term" class="java.lang.Integer">
        <variableExpression>
            <![CDATA[new Integer($F{loan_term}.substring(0,$F{loan_term}.toUpperCase().indexOf("MONTH")).trim())]]>
        </variableExpression>
    </variable>
    <variable name="Payment_rate" class="java.math.BigDecimal">
        <variableExpression>
            <![CDATA[$F{loan_rate}.divide(new BigDecimal("1200"), $P{calculation_precision}.intValue(), BigDecimal.ROUND_HALF_UP)]]>
        </variableExpression>
    </variable>
    <variable name="payment_calc" class="java.math.BigDecimal">
        <variableExpression>
            <![CDATA[(new BigDecimal("1").setScale($P{calculation_precision}.intValue()).add($V{Payment_rate})).pow($V{payment_term}.intValue())]]>
        </variableExpression>
    </variable>
    <variable name="payment_amount" class="java.math.BigDecimal">
        <variableExpression>
            <![CDATA[$F{loan_amt}.setScale($P{calculation_precision}.intValue()).multiply($V{Payment_rate}).multiply($V{payment_calc}).divide($V{payment_calc}.subtract(new BigDecimal("1")), 2, BigDecimal.ROUND_HALF_UP)]]>
        </variableExpression>
    </variable>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="30" splitType="Stretch">
            <staticText>
                <reportElement x="0" y="0" width="555" height="20" 
                               uuid="e51a18bc-a59b-4a22-9443-ae7cb18eedb9"/>
                <textElement textAlignment="Center">
                    <font size="14" isBold="true"/>
                </textElement>
                <text><![CDATA[Sample Loan Amortization Report]]></text>
            </staticText>
        </band>
    </title>
    <pageHeader>
        <band splitType="Stretch"/>
    </pageHeader>
    <columnHeader>
        <band height="30" splitType="Stretch">
            <staticText>
                <reportElement x="0" y="0" width="100" height="20" forecolor="#3333FF" 
                               uuid="a1e41c38-084d-438d-8b2a-8b8c83d79bf1"/>
                <text><![CDATA[Loan Amt]]></text>
            </staticText>
            <staticText>
                <reportElement x="100" y="0" width="100" height="20" forecolor="#3333FF"
                               uuid="a1942898-499c-4a7a-9d6b-ec36e8558573"/>
                <text><![CDATA[Term]]></text>
            </staticText>
            <staticText>
                <reportElement x="200" y="0" width="100" height="20" forecolor="#3333FF"
                               uuid="b54aa4a5-8584-40c0-88f0-46786f657777"/>
                <text><![CDATA[Rate]]></text>
            </staticText>
        </band>
    </columnHeader>
    <detail>
        <band height="30" splitType="Stretch">
            <textField pattern="¤ #,##0.00">
                <reportElement x="0" y="0" width="100" height="20" uuid="f1c94937-3ca6-4049-bba9-771ef5584f0f"/>
                <textFieldExpression><![CDATA[$F{loan_amt}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="100" y="0" width="100" height="20" uuid="9650d09b-90f9-44a4-b500-55cf2a97b469"/>
                <textFieldExpression><![CDATA[$F{loan_term}]]></textFieldExpression>
            </textField>
            <textField pattern="#,##0.0000 %">
                <reportElement x="200" y="0" width="100" height="20" uuid="97cb9904-3e4c-499e-a3ec-ac05717c8bc5"/>
                <textFieldExpression>
                    <![CDATA[$F{loan_rate}.divide(new BigDecimal("100"), 4, BigDecimal.ROUND_HALF_UP)]]>
                </textFieldExpression>
            </textField>
        </band>
        <band height="50">
            <staticText>
                <reportElement x="75" y="0" width="100" height="20" forecolor="#009900" 
                               uuid="3316e9fe-e7e7-4020-84d6-a8f16cd8eb6b"/>
                <text><![CDATA[Paymet Amt]]></text>
            </staticText>
            <staticText>
                <reportElement x="175" y="0" width="100" height="20" forecolor="#009900" 
                               uuid="b13d5a88-4a41-48b3-a8a3-ec5e26a6bb36"/>
                <text><![CDATA[Interests Paid]]></text>
            </staticText>
            <staticText>
                <reportElement x="275" y="0" width="100" height="20" forecolor="#009900" 
                               uuid="293adaa8-8b77-4d78-aace-bb47db4f9bbe"/>
                <text><![CDATA[Principal Amt Paid]]></text>
            </staticText>
            <staticText>
                <reportElement x="375" y="0" width="100" height="20" forecolor="#009900" 
                               uuid="858d871f-9105-4b2f-bb37-5b7eca311712"/>
                <text><![CDATA[Principal Balance]]></text>
            </staticText>
            <subreport>
                <reportElement x="75" y="20" width="400" height="30" 
                               uuid="89713a65-d899-4ad9-8dcb-e2c1b91bd79d"/>
                <subreportParameter name="payment_term">
                    <subreportParameterExpression>
                        <![CDATA[$V{payment_term}]]>
                    </subreportParameterExpression>
                </subreportParameter>
                <subreportParameter name="payment_amount">
                    <subreportParameterExpression>
                        <![CDATA[$V{payment_amount}]]>
                    </subreportParameterExpression>
                </subreportParameter>
                <subreportParameter name="loan_balance">
                    <subreportParameterExpression>
                        <![CDATA[$F{loan_amt}]]>
                    </subreportParameterExpression>
                </subreportParameter>
                <subreportParameter name="payment_rate">
                    <subreportParameterExpression>
                        <![CDATA[$V{Payment_rate}]]>
                    </subreportParameterExpression>
                </subreportParameter>
                <subreportParameter name="calculation_precision">
                    <subreportParameterExpression>
                        <![CDATA[$P{calculation_precision}]]>
                    </subreportParameterExpression>
                </subreportParameter>
                <subreportParameter name="SUBREPORT_DIR"/>
                <connectionExpression>
                    <![CDATA[$P{REPORT_CONNECTION}]]>
                </connectionExpression>
                <subreportExpression>
                    <![CDATA[$P{SUBREPORT_DIR} + "report_amortization.jasper"]]>
                </subreportExpression>
            </subreport>
        </band>
    </detail>
    <columnFooter>
        <band splitType="Stretch"/>
    </columnFooter>
    <pageFooter>
        <band height="30" splitType="Stretch">
            <textField>
                <reportElement x="0" y="0" width="555" height="20" 
                               uuid="c8f13942-ed2a-454c-b895-94e9e6513239"/>
                <textElement textAlignment="Center"/>
                <textFieldExpression>
                    <![CDATA["Page "+$V{PAGE_COUNT}]]>
                </textFieldExpression>
            </textField>
        </band>
    </pageFooter>
    <summary>
        <band height="30" splitType="Stretch">
            <textField>
                <reportElement x="0" y="0" width="555" height="20" forecolor="#FF3333" 
                               uuid="9048ae79-f33a-41dc-a8bd-bb34497dfac3"/>
                <textElement>
                    <font isBold="true"/>
                </textElement>
                <textFieldExpression>
                    <![CDATA["Total number of loans processed: "+$V{REPORT_COUNT}]]>
                </textFieldExpression>
            </textField>
        </band>
    </summary>
</jasperReport>

    Report Design (sub report):

    <?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports"
              xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
              xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd"
              name="report_amortization" pageWidth="400" pageHeight="30" columnWidth="400"
              leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" 
              uuid="88eb281f-bcea-4922-a8cb-819280f551a5">
    <property name="ireport.zoom" value="1.0"/>
    <property name="ireport.x" value="0"/>
    <property name="ireport.y" value="0"/>
    <parameter name="payment_term" class="java.lang.Integer" isForPrompting="false">
        <defaultValueExpression><![CDATA[new Integer("5")]]></defaultValueExpression>
    </parameter>
    <parameter name="payment_amount" class="java.math.BigDecimal" isForPrompting="false">
        <defaultValueExpression><![CDATA[new BigDecimal("20238.13")]]></defaultValueExpression>
    </parameter>
    <parameter name="loan_balance" class="java.math.BigDecimal" isForPrompting="false">
        <defaultValueExpression><![CDATA[new BigDecimal("100000.00")]]></defaultValueExpression>
    </parameter>
    <parameter name="payment_rate" class="java.math.BigDecimal" isForPrompting="false">
        <defaultValueExpression><![CDATA[new BigDecimal("0.00395833")]]></defaultValueExpression>
    </parameter>
    <parameter name="calculation_precision" class="java.lang.Integer" isForPrompting="false">
        <defaultValueExpression><![CDATA[new Integer("12")]]></defaultValueExpression>
    </parameter>
    <parameter name="SUBREPORT_DIR" class="java.lang.String" isForPrompting="false">
        <defaultValueExpression><![CDATA[""]]></defaultValueExpression>
    </parameter>
    <queryString>
        <![CDATA[select 1 as n]]>
    </queryString>
    <field name="n" class="java.lang.Integer"/>
    <variable name="interests_paid" class="java.math.BigDecimal">
        <variableExpression>
            <![CDATA[$P{loan_balance}.setScale($P{calculation_precision}.intValue()).multiply($P{payment_rate}).divide(new BigDecimal("1"), 2, BigDecimal.ROUND_HALF_UP)]]>
        </variableExpression>
    </variable>
    <variable name="principal_paid" class="java.math.BigDecimal">
        <variableExpression>
            <![CDATA[$P{payment_amount}.setScale(2).subtract($V{interests_paid})]]>
        </variableExpression>
    </variable>
    <variable name="principal_balance" class="java.math.BigDecimal">
        <variableExpression>
            <![CDATA[$P{loan_balance}.setScale(2).subtract($V{principal_paid})]]>
        </variableExpression>
    </variable>
    <variable name="last_payment_amount" class="java.math.BigDecimal">
        <variableExpression>
            <![CDATA[$P{loan_balance}.setScale(2).add($V{interests_paid})]]>
        </variableExpression>
    </variable>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band splitType="Stretch"/>
    </title>
    <pageHeader>
        <band splitType="Stretch"/>
    </pageHeader>
    <columnHeader>
        <band splitType="Stretch"/>
    </columnHeader>
    <detail>
        <band height="30" splitType="Stretch">
            <printWhenExpression>
                <![CDATA[$P{payment_term}.intValue()>1]]>
            </printWhenExpression>
            <textField pattern="¤ #,##0.00">
                <reportElement x="0" y="0" width="100" height="20"
                               uuid="bf56f407-7d39-46b3-b867-e6ad8490312e"/>
                <textFieldExpression>
                    <![CDATA[$P{payment_amount}]]>
                </textFieldExpression>
            </textField>
            <textField pattern="¤ #,##0.00">
                <reportElement x="100" y="0" width="100" height="20"
                               uuid="392372b4-ebff-4798-a318-76438639b1ad"/>
                <textFieldExpression>
                    <![CDATA[$V{interests_paid}]]>
                </textFieldExpression>
            </textField>
            <textField pattern="¤ #,##0.00">
                <reportElement x="200" y="0" width="100" height="20"
                               uuid="c6cf095c-1d3a-4762-a03c-3468f3d03c93"/>
                <textFieldExpression>
                    <![CDATA[$V{principal_paid}]]>
                </textFieldExpression>
            </textField>
            <textField pattern="¤ #,##0.00">
                <reportElement x="300" y="0" width="100" height="20"
                               uuid="e053b4a4-8354-4101-88fc-1a1b31ceb8d7"/>
                <textFieldExpression>
                    <![CDATA[$V{principal_balance}]]>
                </textFieldExpression>
            </textField>
        </band>
        <band height="30">
            <printWhenExpression><![CDATA[$P{payment_term}.intValue()>1]]></printWhenExpression>
            <subreport>
                <reportElement x="0" y="0" width="400" height="30"
                               uuid="4e9fee79-f23c-484d-a923-67905f795059"/>
                <subreportParameter name="payment_term">
                    <subreportParameterExpression>
                        <![CDATA[new Integer($P{payment_term}.intValue()-1)]]>
                    </subreportParameterExpression>
                </subreportParameter>
                <subreportParameter name="payment_amount">
                    <subreportParameterExpression>
                        <![CDATA[$P{payment_amount}]]>
                    </subreportParameterExpression>
                </subreportParameter>
                <subreportParameter name="loan_balance">
                    <subreportParameterExpression>
                        <![CDATA[$V{principal_balance}]]>
                    </subreportParameterExpression>
                </subreportParameter>
                <subreportParameter name="payment_rate">
                    <subreportParameterExpression>
                        <![CDATA[$P{payment_rate}]]>
                    </subreportParameterExpression>
                </subreportParameter>
                <subreportParameter name="calculation_precision">
                    <subreportParameterExpression>
                        <![CDATA[$P{calculation_precision}]]>
                    </subreportParameterExpression>
                </subreportParameter>
                <connectionExpression>
                    <![CDATA[$P{REPORT_CONNECTION}]]>
                </connectionExpression>
                <subreportExpression>
                    <![CDATA[$P{SUBREPORT_DIR} + "report_amortization.jasper"]]>
                </subreportExpression>
            </subreport>
        </band>
        <band height="30">
            <printWhenExpression>
                <![CDATA[$P{payment_term}.intValue()==1]]>
            </printWhenExpression>
            <textField isStretchWithOverflow="true" pattern="¤ #,##0.00">
                <reportElement x="0" y="0" width="100" height="20"
                               uuid="8b35f2cd-ed70-4d58-8e5f-360ee68858c8"/>
                <textFieldExpression>
                    <![CDATA[$V{last_payment_amount}]]>
                </textFieldExpression>
            </textField>
            <textField pattern="¤ #,##0.00">
                <reportElement x="100" y="0" width="100" height="20"
                               uuid="561a9d65-32e9-4296-bbad-9e0d01f10195"/>
                <textFieldExpression>
                    <![CDATA[$V{interests_paid}]]>
                </textFieldExpression>
            </textField>
            <textField pattern="¤ #,##0.00">
                <reportElement x="200" y="0" width="100" height="20"
                               uuid="70b460ef-d9b2-4dfc-b0a5-7ad5509569be"/>
                <textFieldExpression>
                    <![CDATA[$P{loan_balance}]]>
                </textFieldExpression>
            </textField>
            <textField pattern="¤ #,##0.00">
                <reportElement x="300" y="0" width="100" height="20"
                               uuid="ffa3b4bc-7c0b-45e0-b619-076398d8d871"/>
                <textFieldExpression>
                    <![CDATA[new BigDecimal("0.00")]]>
                </textFieldExpression>
            </textField>
        </band>
    </detail>
    <columnFooter>
        <band splitType="Stretch"/>
    </columnFooter>
    <pageFooter>
        <band splitType="Stretch"/>
    </pageFooter>
    <summary>
        <band splitType="Stretch"/>
    </summary>
</jasperReport>

    Report Output:

    reportoutput(5).png.deec03f3fbe9a29d0e48dc43f7c72063.png

    TTC-20150328

     Share
    Go to entries

    User Feedback

    Recommended Comments

    There are no comments to display.



    Guest
    This is now closed for further comments

×
×
  • Create New...

© 2024 Cloud Software Group,
Inc. All rights reserved.

Products

Explore