Why is my details band not repeating?

Posted on April 2, 2019 at 8:02am

Hi All!

I have one products label report, where I want to print all the selected products, with barcode and name. This was working fine until now, there was two labels beside each other and many under each other, so it filled the A4 paper.

But something changed and I don't know what... Right now it only prints the last resulting row as far as I can tell, not more, only this one. I'm searching since hours, but can't fnd the settings which is leading to this...

This is my report code:

<?xml version="1.0" encoding="UTF-8"?>
 
<!-- Created with Jaspersoft Studio version 6.6.0.final using JasperReports Library version 6.6.0  -->
 
<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="products_label" columnCount="2" printOrder="Horizontal" pageWidth="595" pageHeight="842" columnWidth="267" columnSpacing="20" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="899f8e9c-f50e-4b4d-8793-c60f859f5da3">
 
<property name="com.jaspersoft.studio.data.sql.tables" value=""/>
 
<property name="com.jaspersoft.studio.data.defaultdataadapter" value="MANUFACT_ADAPTER"/>
 
<style name="barcode-style" mode="Opaque" forecolor="#000000" hTextAlign="Center" hImageAlign="Center" vTextAlign="Bottom" vImageAlign="Bottom" markup="styled" fontName="barcode" fontSize="8"/>
 
<parameter name="id_list" class="java.lang.String">
 
<parameterDescription><![CDATA[]]></parameterDescription>
 
</parameter>
 
<parameter name="price" class="java.lang.String"/>
 
<queryString>
 
<![CDATA[SELECT
 
product.ID,
 
LOWER(c.COMPANY_NAME) AS COMPANY_NAME,
 
    price1.GROSS_PRICE as price1,
 
    price2.GROSS_PRICE as price2,
 
    price3.GROSS_PRICE as price3,
 
product.`item_nr`,
 
product.`NAME`,
 
IF(product.`EAN` REGEXP ('^[0-9]+$'),
 
IF(LENGTH(product.`EAN`) > 12,
 
SUBSTRING(product.`EAN`, 1, 12),
 
                NULL),
 
NULL) AS EAN_NORM,
 
price.`NET_UNIT_PRICE`,
 
(price.`NET_UNIT_PRICE` + (price.`VAT_RATE`/100 * price.`NET_UNIT_PRICE`)) AS "GROSS_PRICE",
 
price.`PRICELIST`
 
FROM (SELECT PRODUCT_ID, (price.`NET_UNIT_PRICE` + (price.`VAT_RATE`/100 * price.`NET_UNIT_PRICE`)) AS "GROSS_PRICE" FROM price WHERE pricelist = 1) price1,
 
     (SELECT PRODUCT_ID, (price.`NET_UNIT_PRICE` + (price.`VAT_RATE`/100 * price.`NET_UNIT_PRICE`)) AS "GROSS_PRICE" FROM price WHERE pricelist = 2) price2,
 
     (SELECT PRODUCT_ID, (price.`NET_UNIT_PRICE` + (price.`VAT_RATE`/100 * price.`NET_UNIT_PRICE`)) AS "GROSS_PRICE" FROM price WHERE pricelist = 3) price3,
 
     (SELECT DISTINCT NAME AS COMPANY_NAME from company LIMIT 0,1) c,
 
price
 
INNER JOIN product ON
 
price.`PRODUCT_ID` = product.`ID`
 
WHERE product.`ID` in  ( $P{id_list} ) and price.`PRICELIST` =   $P{price}   AND price1.PRODUCT_ID = price.`PRODUCT_ID`
 
     AND price3.PRODUCT_ID = price.`PRODUCT_ID`
 
     AND price2.PRODUCT_ID = price.`PRODUCT_ID`
 
ORDER BY product.`EAN`]]>
 
</queryString>
 
<field name="ID" class="java.lang.Integer">
 
<property name="com.jaspersoft.studio.field.label" value="ID"/>
 
<property name="com.jaspersoft.studio.field.tree.path" value="product"/>
 
</field>
 
<field name="COMPANY_NAME" class="java.lang.String">
 
<property name="com.jaspersoft.studio.field.label" value="COMPANY_NAME"/>
 
<property name="com.jaspersoft.studio.field.tree.path" value="company"/>
 
</field>
 
<field name="price1" class="java.math.BigDecimal">
 
<property name="com.jaspersoft.studio.field.label" value="price1"/>
 
</field>
 
<field name="price2" class="java.math.BigDecimal">
 
<property name="com.jaspersoft.studio.field.label" value="price2"/>
 
</field>
 
<field name="price3" class="java.math.BigDecimal">
 
<property name="com.jaspersoft.studio.field.label" value="price3"/>
 
</field>
 
<field name="item_nr" class="java.lang.String">
 
<property name="com.jaspersoft.studio.field.label" value="item_nr"/>
 
<property name="com.jaspersoft.studio.field.tree.path" value="product"/>
 
</field>
 
<field name="NAME" class="java.lang.String">
 
<property name="com.jaspersoft.studio.field.label" value="NAME"/>
 
<property name="com.jaspersoft.studio.field.tree.path" value="product"/>
 
</field>
 
<field name="EAN_NORM" class="java.lang.String">
 
<property name="com.jaspersoft.studio.field.label" value="EAN_NORM"/>
 
</field>
 
<field name="NET_UNIT_PRICE" class="java.math.BigDecimal">
 
<property name="com.jaspersoft.studio.field.label" value="NET_UNIT_PRICE"/>
 
<property name="com.jaspersoft.studio.field.tree.path" value="price"/>
 
</field>
 
<field name="GROSS_PRICE" class="java.math.BigDecimal">
 
<property name="com.jaspersoft.studio.field.label" value="GROSS_PRICE"/>
 
</field>
 
<field name="PRICELIST" class="java.lang.String">
 
<property name="com.jaspersoft.studio.field.label" value="PRICELIST"/>
 
<property name="com.jaspersoft.studio.field.tree.path" value="price"/>
 
</field>
 
<background>
 
<band splitType="Stretch"/>
 
</background>
 
<detail>
 
<band height="151">
 
<property name="com.jaspersoft.studio.unit.height" value="cm"/>
 
<property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.FreeLayout"/>
 
<textField>
 
<reportElement x="5" y="10" width="255" height="70" uuid="25652973-1537-4d37-a5a2-7b42a49d97de"/>
 
<textElement>
 
<font fontName="DejaVu Sans" size="20" isBold="false"/>
 
</textElement>
 
<textFieldExpression><![CDATA[(($F{NAME} != null) ? $F{NAME} : "")]]></textFieldExpression>
 
</textField>
 
<line>
 
<reportElement x="-7" y="-1" width="28" height="1" uuid="d3ca5612-8578-4d0e-91fb-52486bb4392d">
 
<property name="com.jaspersoft.studio.unit.width" value="cm"/>
 
<property name="com.jaspersoft.studio.unit.height" value="pixel"/>
 
</reportElement>
 
<graphicElement>
 
<pen lineWidth="0.6" lineStyle="Solid"/>
 
</graphicElement>
 
</line>
 
<line>
 
<reportElement key="" x="-7" y="-1" width="1" height="28" uuid="0f02b6db-f6fe-4840-9bf7-a1148dff0658">
 
<property name="com.jaspersoft.studio.unit.width" value="cm"/>
 
<property name="com.jaspersoft.studio.unit.height" value="cm"/>
 
</reportElement>
 
<graphicElement>
 
<pen lineWidth="0.6" lineStyle="Solid"/>
 
</graphicElement>
 
</line>
 
<line>
 
<reportElement key="" x="274" y="123" width="1" height="28" uuid="0efc54d4-0caf-4fe2-a0c4-c7e2d2225964">
 
<property name="com.jaspersoft.studio.unit.width" value="pixel"/>
 
<property name="com.jaspersoft.studio.unit.height" value="cm"/>
 
<property name="com.jaspersoft.studio.unit.y" value="pixel"/>
 
</reportElement>
 
<graphicElement>
 
<pen lineWidth="0.6" lineStyle="Solid"/>
 
</graphicElement>
 
</line>
 
<line>
 
<reportElement x="246" y="150" width="28" height="1" uuid="b8b0504f-e9c0-4f77-bef5-9e9722b20283">
 
<property name="com.jaspersoft.studio.unit.width" value="cm"/>
 
<property name="com.jaspersoft.studio.unit.height" value="pixel"/>
 
</reportElement>
 
<graphicElement>
 
<pen lineWidth="0.6" lineStyle="Solid"/>
 
</graphicElement>
 
</line>
 
<textField>
 
<reportElement x="140" y="111" width="120" height="30" uuid="ac22f508-b234-423f-9bfa-9a55df558dd1"/>
 
<textElement textAlignment="Right" verticalAlignment="Middle">
 
<font fontName="DejaVu Sans" size="18"/>
 
</textElement>
 
<textFieldExpression><![CDATA[( $F{GROSS_PRICE} != null) ? new java.text.DecimalFormat("#,##0.##").format($F{GROSS_PRICE} .setScale(0, BigDecimal.ROUND_HALF_UP).intValue()) + " Ft" : "0 Ft"]]></textFieldExpression>
 
</textField>
 
<componentElement>
 
<reportElement x="0" y="111" width="140" height="30" uuid="92b7f4ee-135c-4b31-a92c-a1ee25832a33">
 
<printWhenExpression><![CDATA[( $F{EAN_NORM} != null )]]></printWhenExpression>
 
</reportElement>
 
<c:EAN13 xmlns:c="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd" textPosition="bottom" checksumMode="auto">
 
<c:codeExpression><![CDATA[($F{EAN_NORM} != null ? $F{EAN_NORM} : null)]]></c:codeExpression>
 
</c:EAN13>
 
</componentElement>
 
<textField>
 
<reportElement x="5" y="80" width="255" height="30" uuid="ae7748b8-812a-4f47-96bb-5bc9c7d33d08"/>
 
<textElement textAlignment="Right" verticalAlignment="Middle"/>
 
<textFieldExpression><![CDATA[($F{COMPANY_NAME} .contains("bétalox") ? new java.text.DecimalFormat("#,##0.##").format($F{price1}) + " * " + new java.text.DecimalFormat("#,##0.##").format($F{price3}) : "")]]></textFieldExpression>
 
</textField>
 
</band>
 
</detail>
 
</jasperReport>

What am I missing? Thank you!

JasperReports® Server

VORiAND

Joined: Nov 15 2018 - 7:26am

Last seen: 1 year 7 months ago

10 Answers:

Posted on April 2, 2019 at 12:51pm

I've just putted your design into my good old iReport 5.5.1 (removed uuid's of course ) and run it against the EmptyDataSource with 10 empty records..

so no problem so far:

I just placed the red colored REPORT_COUNT variable to show where the records appear.

seems to be a problem with JasperStudio 6.6 and/or your data!??!?! Are you sure that all wanted records are really fetched from the query??

regards

C-Box

2383

Joined: Jul 19 2006 - 5:58pm

Last seen: 2 months 1 week ago

Posted on April 2, 2019 at 1:34pm

Hm... If I take the SQL query and put it into MySQLWorkbench AND input the parameters as I enter in the preview:

SELECT
    product.ID,
    LOWER(c.COMPANY_NAME) AS COMPANY_NAME,
    price1.GROSS_PRICE AS price1,
    price2.GROSS_PRICE AS price2,
    price3.GROSS_PRICE AS price3,
    product.`item_nr`,
    product.`NAME`,
            IF(product.`EAN` REGEXP ('^[0-9]+$'),
            IF(LENGTH(product.`EAN`) > 12,
                SUBSTRING(product.`EAN`, 1, 12),
                NULL),
        NULL) AS EAN_NORM,
    price.`NET_UNIT_PRICE`,
    (price.`NET_UNIT_PRICE` + (price.`VAT_RATE`/100 * price.`NET_UNIT_PRICE`)) AS "GROSS_PRICE",
    price.`PRICELIST`
FROM (SELECT PRODUCT_ID, (price.`NET_UNIT_PRICE` + (price.`VAT_RATE`/100 * price.`NET_UNIT_PRICE`)) AS "GROSS_PRICE" FROM price WHERE pricelist = 1) price1,
     (SELECT PRODUCT_ID, (price.`NET_UNIT_PRICE` + (price.`VAT_RATE`/100 * price.`NET_UNIT_PRICE`)) AS "GROSS_PRICE" FROM price WHERE pricelist = 2) price2, 
     (SELECT PRODUCT_ID, (price.`NET_UNIT_PRICE` + (price.`VAT_RATE`/100 * price.`NET_UNIT_PRICE`)) AS "GROSS_PRICE" FROM price WHERE pricelist = 3) price3, 
     (SELECT DISTINCT NAME AS COMPANY_NAME FROM company LIMIT 0,1) c,
     price
     INNER JOIN product ON 
     price.`PRODUCT_ID` = product.`ID`
     WHERE product.`ID` IN  (115,116,117) AND price.`PRICELIST` =   1   
     AND price1.PRODUCT_ID = price.`PRODUCT_ID`
     AND price3.PRODUCT_ID = price.`PRODUCT_ID`
     AND price2.PRODUCT_ID = price.`PRODUCT_ID`
     ORDER BY product.`EAN`

I get three rows, so not only one. I am missing really something I guess :)

VORiAND

Joined: Nov 15 2018 - 7:26am

Last seen: 1 year 7 months ago

Posted on April 2, 2019 at 3:41pm

Jaspersoft Studio 6.6.0 seems to have problem with barcode. I've versioned down to 6.5.1 when using barcodes.

hozawa

70035

Joined: Apr 24 2010 - 4:31pm

Last seen: 1 year 8 months ago

Posted on April 3, 2019 at 1:42am

I downgraded my Jasper version in my webapp to 6.5.1:

<dependency>
<groupId>net.sf.jasperreports</groupId>
<artifactId>jasperreports</artifactId>
<version>6.5.1</version>
</dependency>
<dependency>
<groupId>net.sf.jasperreports</groupId>
<artifactId>jasperreports-fonts</artifactId>
<version>6.0.0</version>
</dependency>

Changed nothing, there is only one product label in the resulting pdf :(

Also downgraded my JasperSoft Studio to 6.5.1 version and the preview there is sadly also the same :(

VORiAND

Joined: Nov 15 2018 - 7:26am

Last seen: 1 year 7 months ago

Posted on April 3, 2019 at 1:53am

Can you try the following?

From Jaspersoft Studio, open the jrxml file.

Click on the "Dataset and Query Dialog" icon.

At the bottom, select the "Data preview" tab.

Click on the "Refresh Preview Data" to see if all rows are properly being read.

hozawa

70035

Joined: Apr 24 2010 - 4:31pm

Last seen: 1 year 8 months ago

Posted on April 3, 2019 at 2:08am

Oh, my god... You are right. It only delivers one row, but why and how?! I debugged my SQL query hundred times in MySQLWorkbench and the results are there okay.

VORiAND

Joined: Nov 15 2018 - 7:26am

Last seen: 1 year 7 months ago

Posted on April 3, 2019 at 2:37am

Okay, so the problem is with the list handling. If I remove this from the Query: "AND product.`ID` in ( $P{id_list} )"

the preview is giving me more results. I enter parameters like this: "1,2,3". Isn't this so correct?

VORiAND

Joined: Nov 15 2018 - 7:26am

Last seen: 1 year 7 months ago

Posted on April 3, 2019 at 3:21pm

Check which jdbc driver you're using. Be sure to use the driver for the version of MySQL that you're using. Better to use MySQL driver instead of MariaDB or one from TIBCO Jaspersoft.

hozawa

70035

Joined: Apr 24 2010 - 4:31pm

Last seen: 1 year 8 months ago

Posted on April 4, 2019 at 1:16am

The solution was this:

replace this: product.`ID` in ( $P{id_list} )

with this: product.`ID` in ( $P!{id_list} )

VORiAND

Joined: Nov 15 2018 - 7:26am

Last seen: 1 year 7 months ago

Posted on April 4, 2019 at 3:34pm

If you want to use IN operator without a problem then use $X{IN, column, parameter} operator:

http://jasperreports.sourceforge.net/sample.reference/query/index.html

You would need to change the $P{id_list} to java.util.Collection in that case though.

Friendly User

28285

Joined: Oct 8 2009 - 5:59am

Last seen: 4 hours 10 min ago

Main menu

Why is my details band not repeating?

10 Answers:

Tags

Main menu

You are here

Why is my details band not repeating?

10 Answers:

Tags