Search code examples
jasper-reportssubreport

Issue in genearting the sub report details?

I had created the report it is getting the values for first name and last name and invoice id but when I add the sub report the other values are not getting it is just printing the headings. Please solve my problem and suggest me.

It is printing as below image.

Main report code:

   <?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="Usage Rport" pageWidth="595" pageHeight="842" columnWidth="595" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" >
<property name="com.jasperassistant.designer.GridHeight" value="12"/>
<property name="com.jasperassistant.designer.GridWidth" value="12"/>
<property name="com.jasperassistant.designer.SnapToGrid" value="false"/>
<property name="com.jasperassistant.designer.Grid" value="false"/>
<property name="ireport.zoom" value="1.0"/>
<property name="ireport.x" value="0"/>
<property name="ireport.y" value="0"/>
<import value="org.joda.time.DateTime"/>
<parameter name="invoice_id" class="java.lang.Integer"/>
<parameter name="entity_id" class="java.lang.Integer"/>
<parameter name="entityPhoneCountryCode" class="java.lang.String"/>
<parameter name="entityPhoneAreaCode" class="java.lang.String"/>
<parameter name="entityPhoneContry" class="java.lang.String"/>
<parameter name="invoiceDate" class="java.lang.String"/>
<parameter name="invoiceDueDate" class="java.lang.String"/>
<parameter name="SUBREPORT_DIR" class="java.lang.String" isForPrompting="false">
    <defaultValueExpression><![CDATA["D:\\Ravi Workspace\\Openbrm\\open-brm-openbrm-2.0\\descriptors\\reports\\usage\\"]]></defaultValueExpression>
</parameter>
<queryString>
    <![CDATA[select co.first_name,co.last_name,b.id as user_id from swiss_cdr_event e join order_process o join base_user b on b.id=e.user_id and e.order_id = o.order_id join contact co on e.user_id=co.user_id where o.invoice_id=$P{invoice_id} and b.entity_id=$P{entity_id} order by e.call_start_date asc;]]>
</queryString>
<field name="first_name" class="java.lang.String"/>
<field name="last_name" class="java.lang.String"/>
<field name="user_id" class="java.lang.Integer"/>
<title>
    <band height="20"/>
</title>
<pageHeader>
    <band height="111">
        <textField>
            <reportElement x="5" y="13" width="200" height="15" />
            <textElement>
                <font size="10" isBold="true"/>
            </textElement>
            <textFieldExpression><![CDATA[$F{first_name}+" " +$F{last_name}]]></textFieldExpression>
        </textField>
        <staticText>
            <reportElement x="185" y="13" width="140" height="15" />
            <textElement textAlignment="Left">
                <font isBold="true"/>
            </textElement>
            <text><![CDATA[Rechnungs-Nr. ]]></text>
        </staticText>
        <staticText>
            <reportElement x="368" y="13" width="100" height="15" />
            <textElement>
                <font isBold="true"/>
            </textElement>
            <text><![CDATA[Jahr / Monat]]></text>
        </staticText>
        <staticText>
            <reportElement x="379" y="28" width="52" height="15" />
            <textElement>
                <font isBold="true"/>
            </textElement>
            <text><![CDATA[datiert]]></text>
        </staticText>
        <textField>
            <reportElement x="252" y="13" width="100" height="15" />
            <textElement>
                <font isBold="true"/>
            </textElement>
            <textFieldExpression class="java.lang.Integer"><![CDATA[$P{invoice_id}]]></textFieldExpression>
        </textField>
        <subreport>
            <reportElement x="434" y="28" width="119" height="18" />
            <subreportParameter name="invoiceId">
                <subreportParameterExpression><![CDATA[$P{invoice_id}]]></subreportParameterExpression>
            </subreportParameter>
            <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
            <subreportExpression><![CDATA[$P{SUBREPORT_DIR} + "run-details.jasper"]]></subreportExpression>
        </subreport>
        <subreport>
            <reportElement x="435" y="9" width="161" height="15" />
            <subreportParameter name="invoiceId">
                <subreportParameterExpression><![CDATA[$P{invoice_id}]]></subreportParameterExpression>
            </subreportParameter>
            <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
            <subreportExpression><![CDATA[$P{SUBREPORT_DIR} + "invoice-period-31.jasper"]]></subreportExpression>
        </subreport>
        <staticText>
            <reportElement x="185" y="28" width="100" height="15" />
            <textElement>
                <font isBold="true"/>
            </textElement>
            <text><![CDATA[Gesprächsnachweis]]></text>
        </staticText>
        <subreport>
            <reportElement x="6" y="57" width="590" height="43" />
            <subreportParameter name="invoiceId">
                <subreportParameterExpression><![CDATA[$P{invoice_id}]]></subreportParameterExpression>
            </subreportParameter>
            <subreportParameter name="entityId">
                <subreportParameterExpression><![CDATA[$P{entity_id}]]></subreportParameterExpression>
            </subreportParameter>
            <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
            <subreportExpression><![CDATA[$P{SUBREPORT_DIR} + "usage-31-de-details.jasper"]]></subreportExpression>
        </subreport>
    </band>
</pageHeader>
<pageFooter>
    <band height="33">
        <textField>
            <reportElement x="195" y="15" width="80" height="15" />
            <textElement textAlignment="Right"/>
            <textFieldExpression><![CDATA["Seite "+$V{PAGE_NUMBER}+" von "]]></textFieldExpression>
        </textField>
        <textField evaluationTime="Report">
            <reportElement x="275" y="15" width="40" height="15" />
            <textFieldExpression><![CDATA[" " + $V{PAGE_NUMBER}]]></textFieldExpression>
        </textField>
    </band>
</pageFooter>
<lastPageFooter>
    <band height="33">
        <textField>
            <reportElement x="205" y="18" width="80" height="15" />
            <textElement textAlignment="Right"/>
            <textFieldExpression><![CDATA["Seite "+$V{PAGE_NUMBER}+" von "]]></textFieldExpression>
        </textField>
        <textField evaluationTime="Report">
            <reportElement x="285" y="18" width="40" height="15" />
            <textFieldExpression><![CDATA[" " + $V{PAGE_NUMBER}]]></textFieldExpression>
        </textField>
    </band>
</lastPageFooter>
<summary>
    <band height="20"/>
</summary>

Subreport code:

   <?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="Usage Rport" pageWidth="595" pageHeight="842" whenNoDataType="AllSectionsNoDetail" columnWidth="595" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" >
<property name="com.jasperassistant.designer.GridHeight" value="12"/>
<property name="com.jasperassistant.designer.GridWidth" value="12"/>
<property name="com.jasperassistant.designer.SnapToGrid" value="false"/>
<property name="com.jasperassistant.designer.Grid" value="false"/>
<import value="org.joda.time.DateTime"/>
<parameter name="invoice_id" class="java.lang.Integer"/>
<parameter name="entity_id" class="java.lang.Integer"/>
<parameter name="entityCity" class="java.lang.String"/>
<parameter name="customerCity" class="java.lang.String"/>
<parameter name="entityPhoneCountryCode" class="java.lang.String"/>
<parameter name="entityPhoneAreaCode" class="java.lang.String"/>
<parameter name="entityPhoneContry" class="java.lang.String"/>
<parameter name="invoiceDate" class="java.lang.String"/>
<parameter name="invoiceDueDate" class="java.lang.String"/>
<parameter name="SUBREPORT_DIR" class="java.lang.String" isForPrompting="false">
    <defaultValueExpression><![CDATA["D:\\Ravi Workspace\\Openbrm\\open-brm-openbrm-2.0\\descriptors\\reports\\usage\\"]]></defaultValueExpression>
</parameter>
<queryString>
    <![CDATA[select e.calling_number,e.duration,e.call_start_date,e.cost,e.destination_number,e.destination_descr,co.first_name,co.last_name,b.id as user_id from swiss_cdr_event e join order_process o join base_user b on b.id=e.user_id and e.order_id = o.order_id join contact co on e.user_id=co.user_id where o.invoice_id=$P{invoice_id} and b.entity_id=$P{entity_id} order by e.call_start_date asc;]]>
</queryString>
<field name="calling_number" class="java.lang.String"/>
<field name="destination_number" class="java.lang.String"/>
<field name="duration" class="java.lang.Integer"/>
<field name="cost" class="java.math.BigDecimal"/>
<field name="call_start_date" class="java.sql.Timestamp"/>
<field name="destination_descr" class="java.lang.String"/>
<field name="first_name" class="java.lang.String"/>
<field name="last_name" class="java.lang.String"/>
<field name="user_id" class="java.lang.Integer"/>
<variable name="total" class="java.math.BigDecimal" calculation="Sum">
    <variableExpression><![CDATA[$F{cost}]]></variableExpression>
</variable>
<title>
    <band height="111"/>
</title>
<columnHeader>
    <band height="33">
        <staticText>
            <reportElement x="12" y="10" width="62" height="15" />
            <textElement>
                <font size="10" isBold="true"/>
            </textElement>
            <text><![CDATA[Datum/Uhrzeit]]></text>
        </staticText>
        <staticText>
            <reportElement x="240" y="10" width="85" height="15" />
            <textElement>
                <font size="10" isBold="true"/>
            </textElement>
            <text><![CDATA[Destination]]></text>
        </staticText>
        <staticText>
            <reportElement x="450" y="10" width="42" height="15" />
            <textElement textAlignment="Left" verticalAlignment="Middle">
                <font size="10" isBold="true"/>
            </textElement>
            <text><![CDATA[Menge]]></text>
        </staticText>
        <staticText>
            <reportElement x="90" y="10" width="62" height="15" />
            <text><![CDATA[Anruf von]]></text>
        </staticText>
        <staticText>
            <reportElement x="163" y="10" width="49" height="14" />
            <text><![CDATA[Service]]></text>
        </staticText>
        <staticText>
            <reportElement x="334" y="10" width="100" height="15" />
            <text><![CDATA[Rufnummer]]></text>
        </staticText>
        <staticText>
            <reportElement x="514" y="10" width="47" height="15" />
            <text><![CDATA[Betrag]]></text>
        </staticText>
    </band>
</columnHeader>
<detail>
    <band height="20">
        <textField>
            <reportElement x="360" y="3" width="193" height="16" />
            <textElement textAlignment="Right" verticalAlignment="Middle">
                <font size="9"/>
            </textElement>
            <textFieldExpression><![CDATA[new DecimalFormat("#,##0.00").format($F{cost})]]></textFieldExpression>
        </textField>
        <textField>
            <reportElement x="12" y="3" width="96" height="16" />
            <textElement>
                <font size="9"/>
            </textElement>
            <textFieldExpression><![CDATA[new SimpleDateFormat("dd-MM-yyyy").format($F{call_start_date})]]></textFieldExpression>
        </textField>
        <textField>
            <reportElement positionType="Float" stretchType="RelativeToTallestObject" x="242" y="3" width="121" height="16" />
            <textElement>
                <font size="9"/>
            </textElement>
            <textFieldExpression><![CDATA[$F{destination_descr}]]></textFieldExpression>
        </textField>
        <textField>
            <reportElement x="60" y="3" width="96" height="16" />
            <textElement>
                <font size="9"/>
            </textElement>
            <textFieldExpression><![CDATA[new SimpleDateFormat("HH:mm:ss").format($F{call_start_date})]]></textFieldExpression>
        </textField>
        <textField>
            <reportElement x="450" y="4" width="100" height="16" />
            <textElement>
                <font size="9"/>
            </textElement>
            <textFieldExpression class="java.lang.Integer"><![CDATA[$F{duration}]]></textFieldExpression>
        </textField>
        <subreport>
            <reportElement x="302" y="0" width="129" height="20" />
            <subreportParameter name="invoiceId">
                <subreportParameterExpression><![CDATA[$P{invoice_id}]]></subreportParameterExpression>
            </subreportParameter>
            <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
            <subreportExpression><![CDATA[$P{SUBREPORT_DIR} + "entity-details-31.jasper"]]></subreportExpression>
        </subreport>
        <textField>
            <reportElement x="98" y="0" width="90" height="15" />
            <textFieldExpression><![CDATA[$P{entityCity}]]></textFieldExpression>
        </textField>
    </band>
</detail>
<summary>
    <band height="80">
        <staticText>
            <reportElement x="242" y="6" width="92" height="15" />
            <text><![CDATA[Total]]></text>
        </staticText>
        <textField>
            <reportElement x="431" y="6" width="100" height="15" />
            <textElement textAlignment="Right">
                <font size="9" isBold="true"/>
            </textElement>
            <textFieldExpression><![CDATA[$V{total} == null ? "0.00" : new DecimalFormat ("#,##0.00").format($V{total})]]></textFieldExpression>
        </textField>
    </band>
</summary>

enter image description here

Solution

  • As looking into your jrxml, You also have to pass $P{entity_id} to your sub_report. Currently you are passing only invoice id $P{invoice_id}. As your sub report sql is working on two parameter $P{invoice_id} and $P{entity_id}. if you are not passing it will pick default value or null (if no default value).

    One more suggestion, sub report should contain only those band which is required and arrange your text field properly otherwise you will get excel column formating issue when you try to download in excel.

    When you click on subreport and select properties then you will image like in first.

    enter image description here

    Now click on parameters in subreport tab of properties. Please see the second image.

    enter image description here

    there are three subreport in your main jrxml.

    1) run-details.jrxml Subreport parameter map in main jrxml invoiceId : $P{invoice_id}

    2) invoice-period-31.jrxml

    Subreport parameter map in main jrxml invoiceId : $P{invoice_id}

    3) usage-31-de-details.jrxml

    Subreport parameter map in main jrxml invoiceId : $P{invoice_id}

    I can see in 3 subreport parameter name is invoice_id. So you should change to Subreport parameter map in main jrxml invoice_id : $P{invoice_id}

    And Also 3 sub report is working on entity_id parameter. you also have to pass that value. Now Subreport parameter map in main jrxml invoice_id : $P{invoice_id} entity_id : $P{entity_id}

    I am not sure what parameter name is used in another subreport.