Why is my details band not repeating?

0

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!

VORiAND's picture
16
Joined: Nov 15 2018 - 7:26am
Last seen: 4 months 6 days ago

10 Answers:

0

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

C-Box's picture
2204
Joined: Jul 19 2006 - 5:58pm
Last seen: 2 hours 48 min ago
0

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's picture
16
Joined: Nov 15 2018 - 7:26am
Last seen: 4 months 6 days ago
0

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

hozawa's picture
53182
Joined: Apr 24 2010 - 4:31pm
Last seen: 5 months 1 week ago
0

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's picture
16
Joined: Nov 15 2018 - 7:26am
Last seen: 4 months 6 days ago
0

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's picture
53182
Joined: Apr 24 2010 - 4:31pm
Last seen: 5 months 1 week ago
0

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's picture
16
Joined: Nov 15 2018 - 7:26am
Last seen: 4 months 6 days ago
0

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's picture
16
Joined: Nov 15 2018 - 7:26am
Last seen: 4 months 6 days ago
0

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's picture
53182
Joined: Apr 24 2010 - 4:31pm
Last seen: 5 months 1 week ago
0

The solution was this: 

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

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

VORiAND's picture
16
Joined: Nov 15 2018 - 7:26am
Last seen: 4 months 6 days ago
0

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's picture
Joined: Oct 8 2009 - 5:59am
Last seen: 13 hours 8 min ago
Feedback