Why is my details band not repeating?

[VORiAND]

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[sELECTproduct.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,priceINNER JOIN product ONprice.`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>[/code]

What am I missing? Thank you!

[C-Box]

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

[VORiAND]

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`[/code]

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

[hozawa]

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

[VORiAND]

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 :(

[hozawa]

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.

[VORiAND]

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]

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?

[hozawa]

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.

[VORiAND]

The solution was this: 

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

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

[Friendly User]

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.