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!
10 Answers:
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
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 :)
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 :(
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.