mathieu.ververken's Content - Jaspersoft Community

Add one extra field - error

mathieu.ververken replied to mathieu.ververken's topic in Products

CODE 2: <?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="MarginsReport" pageWidth="792" pageHeight="612" orientation="Landscape" whenNoDataType="AllSectionsNoDetail" columnWidth="792" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" uuid="f4f863e0-cf8a-47e0-8388-cb12b147627f"> <property name="ireport.zoom" value="1.0"/> <property name="ireport.x" value="0"/> <property name="ireport.y" value="0"/> <property name="ireport.callouts" value="##Wed Mar 11 12:36:56 MST 2015"/> <subDataset name="ProductTreeInfo" uuid="69478527-2a9e-419f-a71f-9b3ee82608f8"> <parameter name="locationGroupID" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["%"]]></defaultValueExpression> </parameter> <parameter name="salesPerson" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["%"]]></defaultValueExpression> </parameter> <parameter name="ckIncludeShipping" class="java.lang.String" isForPrompting="false"> <parameterDescription><![CDATA[60,900]]></parameterDescription> <defaultValueExpression><![CDATA["900"]]></defaultValueExpression> </parameter> <parameter name="customerID" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["%"]]></defaultValueExpression> </parameter> <parameter name="ckIncludeReturns" class="java.lang.String" isForPrompting="false"> <parameterDescription><![CDATA[20,900]]></parameterDescription> <defaultValueExpression><![CDATA["20"]]></defaultValueExpression> </parameter> <parameter name="productNum" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["%"]]></defaultValueExpression> </parameter> <parameter name="dateRange2" class="java.util.Date" isForPrompting="false"> <defaultValueExpression><![CDATA[new Date()]]></defaultValueExpression> </parameter> <parameter name="dateRange1" class="java.util.Date" isForPrompting="false"> <parameterDescription><![CDATA[This Month]]></parameterDescription> <defaultValueExpression><![CDATA[new Date()]]></defaultValueExpression> </parameter> <parameter name="soNum" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["%"]]></defaultValueExpression> </parameter> <parameter name="cbGroupBy" class="java.lang.String"> <parameterDescription><![CDATA[]]></parameterDescription> <defaultValueExpression><![CDATA["soitem.productnum"]]></defaultValueExpression> </parameter> <parameter name="productTree1" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["0"]]></defaultValueExpression> </parameter> <parameter name="productTree2" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["%"]]></defaultValueExpression> </parameter> <parameter name="colProduct" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <parameter name="colUnitCost" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <parameter name="colQty" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <parameter name="colUnitPrice" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <parameter name="colTotalPrice" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <parameter name="colCOGS" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <parameter name="colMargins" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <parameter name="colMargins%" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <queryString> <![CDATA[sELECT $P!{cbGroupBy} AS groupby, so.num AS sonum, customer.name AS custname, soitem.productnum, soitem.description, (case when soitem.uomid != product.uomid then ((postsoitem.qty*uomconversion.multiply)/uomconversion.factor) else postsoitem.qty end) as qty, postsoitem.postedtotalcost, (CASE WHEN soitem.uomid != product.uomid then ((soitem.unitprice + (CASE WHEN soitem.adjustamount > 0 THEN (soitem.adjustamount / soitem.qtytofulfill) ELSE 0 END))/uomconversion.multiply)*uomconversion.factor else (soitem.unitprice + (CASE WHEN soitem.adjustamount > 0 THEN (soitem.adjustamount / soitem.qtytofulfill) ELSE 0 END)) end) as unitprice, COALESCE(uom.code, miscuom.code) AS uomcode, producttree.name AS producttreenode FROM so INNER JOIN customer ON so.customerid = customer.id INNER JOIN soitem ON so.id = soitem.soid LEFT JOIN product ON soitem.productid = product.id INNER JOIN postsoitem ON soitem.id = postsoitem.soitemid INNER JOIN postso ON postsoitem.postsoid = postso.id LEFT JOIN uom ON product.uomid = uom.id left JOIN uom as miscuom on soitem.uomid = miscuom.id LEFT JOIN producttotree ON soitem.productid = producttotree.productid LEFT JOIN producttree ON producttotree.producttreeid = producttree.id LEFT JOIN uomconversion on product.uomid = uomconversion.touomid and soitem.uomid = uomconversion.fromuomid WHERE UPPER(so.num) LIKE UPPER($P{soNum}) AND UPPER(so.salesman) LIKE UPPER($P{salesPerson}) AND so.customerid LIKE $P{customerID} AND postso.postdate BETWEEN $P{dateRange1} AND $P{dateRange2} AND soitem.typeid IN (10,11,12,21,30,31,50,80,$P{ckIncludeShipping},$P{ckIncludeReturns}) AND soitem.productnum LIKE $P{productNum} AND so.locationgroupid IN ($P!{locationGroupID}) AND (COALESCE(producttreeid,0) IN ($P!{productTree1}) OR COALESCE(producttreeid,0) LIKE $P{productTree2}) ORDER BY 1, soitem.productnum, soitem.uomid, so.num]]> </queryString> <field name="GROUPBY" class="java.lang.String"/> <field name="SONUM" class="java.lang.String"/> <field name="CUSTNAME" class="java.lang.String"/> <field name="PRODUCTNUM" class="java.lang.String"/> <field name="DESCRIPTION" class="java.lang.String"/> <field name="QTY" class="java.lang.Double"/> <field name="POSTEDTOTALCOST" class="java.lang.Double"/> <field name="UNITPRICE" class="java.lang.Double"/> <field name="UOMCODE" class="java.lang.String"/> <field name="PRODUCTTREENODE" class="java.lang.String"/> <field name="SALESPERSON" class="java.lang.String"/> <variable name="ProductTotalCost" class="java.lang.Double" resetType="Group" resetGroup="PRODUCTNUM" calculation="Sum"> <variableExpression><![CDATA[$F{POSTEDTOTALCOST}]]></variableExpression> </variable> <variable name="ProductTotalPrice" class="java.lang.Double" resetType="Group" resetGroup="PRODUCTNUM" calculation="Sum"> <variableExpression><![CDATA[$F{QTY} * $F{UNITPRICE}]]></variableExpression> </variable> <variable name="GroupByTotalCost" class="java.lang.Double" resetType="Group" resetGroup="GroupBy" calculation="Sum"> <variableExpression><![CDATA[$F{POSTEDTOTALCOST}]]></variableExpression> </variable> <variable name="GroupByTotalPrice" class="java.lang.Double" resetType="Group" resetGroup="GroupBy" calculation="Sum"> <variableExpression><![CDATA[$F{QTY} * $F{UNITPRICE}]]></variableExpression> </variable> <variable name="ProductGrandTotalCost" class="java.lang.Double" calculation="Sum"> <variableExpression><![CDATA[$F{POSTEDTOTALCOST}]]></variableExpression> </variable> <variable name="ProductGrandTotalPrice" class="java.lang.Double" calculation="Sum"> <variableExpression><![CDATA[$F{QTY} * $F{UNITPRICE}]]></variableExpression> </variable> <group name="GroupBy"> <groupExpression><![CDATA[$F{GROUPBY}]]></groupExpression> </group> <group name="PRODUCTNUM"> <groupExpression><![CDATA[$F{PRODUCTNUM}]]></groupExpression> </group> <group name="UOMCODE"> <groupExpression><![CDATA[$F{UOMCODE}]]></groupExpression> </group> </subDataset> <subDataset name="ProductInfo" uuid="e27e8375-ef55-48e8-b6c3-e450f710115a"> <parameter name="locationGroupID" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["%"]]></defaultValueExpression> </parameter> <parameter name="salesPerson" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["%"]]></defaultValueExpression> </parameter> <parameter name="ckIncludeShipping" class="java.lang.String" isForPrompting="false"> <parameterDescription><![CDATA[60,900]]></parameterDescription> <defaultValueExpression><![CDATA["900"]]></defaultValueExpression> </parameter> <parameter name="customerID" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["%"]]></defaultValueExpression> </parameter> <parameter name="ckIncludeReturns" class="java.lang.String" isForPrompting="false"> <parameterDescription><![CDATA[20,900]]></parameterDescription> <defaultValueExpression><![CDATA["20"]]></defaultValueExpression> </parameter> <parameter name="productNum" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["%"]]></defaultValueExpression> </parameter> <parameter name="dateRange2" class="java.util.Date" isForPrompting="false"> <defaultValueExpression><![CDATA[new Date()]]></defaultValueExpression> </parameter> <parameter name="dateRange1" class="java.util.Date" isForPrompting="false"> <parameterDescription><![CDATA[This Month]]></parameterDescription> <defaultValueExpression><![CDATA[new Date()]]></defaultValueExpression> </parameter> <parameter name="soNum" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["%"]]></defaultValueExpression> </parameter> <parameter name="cbGroupBy" class="java.lang.String"> <parameterDescription><![CDATA[]]></parameterDescription> <defaultValueExpression><![CDATA["soitem.productnum"]]></defaultValueExpression> </parameter> <parameter name="productTree1" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["0"]]></defaultValueExpression> </parameter> <parameter name="productTree2" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["%"]]></defaultValueExpression> </parameter> <parameter name="colProduct" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <parameter name="colUnitCost" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <parameter name="colQty" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <parameter name="colUnitPrice" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <parameter name="colTotalPrice" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <parameter name="colCOGS" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <parameter name="colMargins" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <parameter name="colMargins%" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <queryString> <![CDATA[sELECT (CASE WHEN $P!{cbGroupBy} = '' THEN soitem.description ELSE $P!{cbGroupBy} END) AS groupby, so.num AS sonum, customer.name AS custname, (CASE WHEN soitem.typeid = 11 THEN 'Misc. Sale' ELSE (CASE WHEN soitem.typeid = 21 THEN 'Misc. Credit' ELSE soitem.productnum END) END) AS productnum, soitem.description, SUM((case when soitem.uomid != product.uomid then ((postsoitem.qty*uomconversion.multiply)/uomconversion.factor) else postsoitem.qty end)) AS qty, SUM(postsoitem.postedtotalcost) AS postedtotalcost, (CASE WHEN soitem.uomid != product.uomid then ((soitem.unitprice + (CASE WHEN soitem.adjustamount > 0 THEN (soitem.adjustamount / soitem.qtytofulfill) ELSE 0 END))/uomconversion.multiply)*uomconversion.factor else (soitem.unitprice + (CASE WHEN soitem.adjustamount > 0 THEN (soitem.adjustamount / soitem.qtytofulfill) ELSE 0 END)) end) as unitprice, COALESCE(uom.code, miscuom.code) AS uomcode FROM so INNER JOIN customer ON so.customerid = customer.id INNER JOIN soitem ON so.id = soitem.soid LEFT JOIN product ON soitem.productid = product.id INNER JOIN postsoitem ON soitem.id = postsoitem.soitemid INNER JOIN postso ON postsoitem.postsoid = postso.id LEFT JOIN uom ON product.uomid = uom.id LEFT JOIN uom as miscuom on soitem.uomid = miscuom.id LEFT JOIN uomconversion ON product.uomid = uomconversion.touomid AND soitem.uomid = uomconversion.fromuomid WHERE UPPER(so.num) LIKE UPPER($P{soNum}) AND UPPER(so.salesman) LIKE UPPER($P{salesPerson}) AND so.customerid LIKE $P{customerID} AND postso.postdate BETWEEN $P{dateRange1} AND $P{dateRange2} AND soitem.typeid IN (10,11,12,21,30,31,50,80,$P{ckIncludeShipping},$P{ckIncludeReturns}) AND soitem.productnum LIKE $P{productNum} AND so.locationgroupid IN ($P!{locationGroupID}) GROUP BY soitem.productnum, uomcode, sonum, custname, soitem.description, soitem.unitprice, groupby, soitem.typeid, product.uomid, uomconversion.multiply, uomconversion.factor, unitprice ORDER BY 1, productnum, uomcode, sonum, custname, soitem.description, soitem.unitprice]]> </queryString> <field name="GROUPBY" class="java.lang.String"/> <field name="SONUM" class="java.lang.String"/> <field name="CUSTNAME" class="java.lang.String"/> <field name="PRODUCTNUM" class="java.lang.String"/> <field name="DESCRIPTION" class="java.lang.String"/> <field name="QTY" class="java.lang.Double"/> <field name="POSTEDTOTALCOST" class="java.lang.Double"/> <field name="UNITPRICE" class="java.lang.Double"/> <field name="UOMCODE" class="java.lang.String"/> <field name="SALESPERSON" class="java.lang.String"/> <variable name="ProductTotalCost" class="java.lang.Double" resetType="Group" resetGroup="PRODUCTNUM" calculation="Sum"> <variableExpression><![CDATA[$F{POSTEDTOTALCOST}]]></variableExpression> </variable> <variable name="ProductTotalPrice" class="java.lang.Double" resetType="Group" resetGroup="PRODUCTNUM" calculation="Sum"> <variableExpression><![CDATA[$F{QTY} * $F{UNITPRICE}]]></variableExpression> </variable> <variable name="GroupByTotalCost" class="java.lang.Double" resetType="Group" resetGroup="GroupBy" calculation="Sum"> <variableExpression><![CDATA[$F{POSTEDTOTALCOST}]]></variableExpression> </variable> <variable name="GroupByTotalPrice" class="java.lang.Double" resetType="Group" resetGroup="GroupBy" calculation="Sum"> <variableExpression><![CDATA[$F{QTY} * $F{UNITPRICE}]]></variableExpression> </variable> <variable name="ProductGrandTotalCost" class="java.lang.Double" calculation="Sum"> <variableExpression><![CDATA[$F{POSTEDTOTALCOST}]]></variableExpression> </variable> <variable name="ProductGrandTotalPrice" class="java.lang.Double" calculation="Sum"> <variableExpression><![CDATA[$F{QTY} * $F{UNITPRICE}]]></variableExpression> </variable> <group name="GroupBy"> <groupExpression><![CDATA[$F{GROUPBY}]]></groupExpression> </group> <group name="PRODUCTNUM"> <groupExpression><![CDATA[$F{PRODUCTNUM}]]></groupExpression> </group> <group name="UOMCODE"> <groupExpression><![CDATA[$F{UOMCODE}]]></groupExpression> </group> </subDataset> <parameter name="dateRange1" class="java.util.Date" isForPrompting="false"> <parameterDescription><![CDATA[This Month]]></parameterDescription> <defaultValueExpression><![CDATA[new Date()]]></defaultValueExpression> </parameter> <parameter name="dateRange2" class="java.util.Date" isForPrompting="false"> <defaultValueExpression><![CDATA[new Date()]]></defaultValueExpression> </parameter> <parameter name="productNum" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["%"]]></defaultValueExpression> </parameter> <parameter name="path" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["../SalesOrder/"]]></defaultValueExpression> </parameter> <parameter name="module" class="java.lang.Object" isForPrompting="false"> <defaultValueExpression><![CDATA[null]]></defaultValueExpression> </parameter> <parameter name="ckIncludeShipping" class="java.lang.String" isForPrompting="false"> <parameterDescription><![CDATA[60,900]]></parameterDescription> <defaultValueExpression><![CDATA["900"]]></defaultValueExpression> </parameter> <parameter name="ckIncludeReturns" class="java.lang.String" isForPrompting="false"> <parameterDescription><![CDATA[20,900]]></parameterDescription> <defaultValueExpression><![CDATA["20"]]></defaultValueExpression> </parameter> <parameter name="salesPerson" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["%"]]></defaultValueExpression> </parameter> <parameter name="customerID" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["%"]]></defaultValueExpression> </parameter> <parameter name="locationGroupID" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["%"]]></defaultValueExpression> </parameter> <parameter name="soNum" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["%"]]></defaultValueExpression> </parameter> <parameter name="cbGroupBy" class="java.lang.String"> <parameterDescription><![CDATA[soitem.productnum,Product,producttree.name,Product Tree,customer.name,Customer]]></parameterDescription> <defaultValueExpression><![CDATA["soitem.productnum"]]></defaultValueExpression> </parameter> <parameter name="productTree1" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["0"]]></defaultValueExpression> </parameter> <parameter name="productTree2" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["%"]]></defaultValueExpression> </parameter> <parameter name="colProduct" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <parameter name="colUnitCost" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <parameter name="colQty" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <parameter name="colUnitPrice" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <parameter name="colTotalPrice" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <parameter name="colCOGS" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <parameter name="colMargins" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <parameter name="colMargins%" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <queryString> <![CDATA[sELECT company.name AS company FROM company WHERE id = 1]]> </queryString> <field name="COMPANY" class="java.lang.String"/> <variable name="DateFormat" class="java.lang.String" resetType="None"> <variableExpression><![CDATA[(System.getProperty("REPORT_DATE_FORMAT"))]]></variableExpression> </variable> <background> <band splitType="Stretch"/> </background> <pageHeader> <band splitType="Stretch"/> </pageHeader> <detail> <band height="86"> <printWhenExpression><![CDATA[$P{cbGroupBy}.equals("customer.name")]]></printWhenExpression> <componentElement> <reportElement uuid="397cf7c1-f875-499a-a12f-5af3222bc192" key="table" x="0" y="0" width="732" height="86"/> <jr:table xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd"> <datasetRun subDataset="ProductInfo" uuid="3d765615-02b8-4a6c-9cca-d4c57f366378"> <datasetParameter name="locationGroupID"> <datasetParameterExpression><![CDATA[$P{locationGroupID}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="salesPerson"> <datasetParameterExpression><![CDATA[$P{salesPerson}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="ckIncludeShipping"> <datasetParameterExpression><![CDATA[$P{ckIncludeShipping}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="customerID"> <datasetParameterExpression><![CDATA[$P{customerID}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="ckIncludeReturns"> <datasetParameterExpression><![CDATA[$P{ckIncludeReturns}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="productNum"> <datasetParameterExpression><![CDATA[$P{productNum}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="dateRange2"> <datasetParameterExpression><![CDATA[$P{dateRange2}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="dateRange1"> <datasetParameterExpression><![CDATA[$P{dateRange1}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="soNum"> <datasetParameterExpression><![CDATA[$P{soNum}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="cbGroupBy"> <datasetParameterExpression><![CDATA[$P{cbGroupBy}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="productTree1"> <datasetParameterExpression><![CDATA[$P{productTree1}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="productTree2"> <datasetParameterExpression><![CDATA[$P{productTree2}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="colProduct"> <datasetParameterExpression><![CDATA[$P{colProduct}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="colUnitCost"> <datasetParameterExpression><![CDATA[$P{colUnitCost}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="colQty"> <datasetParameterExpression><![CDATA[$P{colQty}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="colUnitPrice"> <datasetParameterExpression><![CDATA[$P{colUnitPrice}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="colCOGS"> <datasetParameterExpression><![CDATA[$P{colCOGS}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="colMargins"> <datasetParameterExpression><![CDATA[$P{colMargins}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="colMargins%"> <datasetParameterExpression><![CDATA[$P{colMargins%}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="colTotalPrice"> <datasetParameterExpression><![CDATA[$P{colTotalPrice}]]></datasetParameterExpression> </datasetParameter> <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression> </datasetRun> <jr:columnGroup width="1174" uuid="28b68d85-d5a6-4462-859d-6c1599746e35"> <jr:column width="90" uuid="f12cd1df-15e3-41df-ac91-facec8190a40"> <jr:tableHeader height="0" rowSpan="1"/> <jr:columnHeader height="16" rowSpan="1"> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement uuid="84a77d2e-0ad8-42a7-99a9-99abd9f4a254" x="0" y="0" width="90" height="16"/> <box> <bottomPen lineWidth="2.0"/> </box> <textElement markup="none"> </textElement> <textFieldExpression><![CDATA["Customer"]]></textFieldExpression> </textField> </jr:columnHeader> <jr:columnFooter height="0" rowSpan="1"/> <jr:detailCell height="14" rowSpan="1"> <textField> <reportElement uuid="2ff6f464-c81c-403d-a136-7eda88165004" x="0" y="0" width="90" height="14"/> <textElement/> <textFieldExpression><![CDATA[$F{CUSTNAME}]]></textFieldExpression> </textField> </jr:detailCell> </jr:column> <jr:column width="85" uuid="0d6bd9ba-b6f3-4ece-8466-bdc17341517b"> <jr:tableHeader height="0" rowSpan="1"/> <jr:columnHeader height="16" rowSpan="1"> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement uuid="7ae1056c-2216-471d-9073-2794bd8e9051" x="0" y="0" width="85" height="16"/> <box> <bottomPen lineWidth="2.0"/> </box> <textElement markup="none"> </textElement> <textFieldExpression><![CDATA["Product nr"]]></textFieldExpression> </textField> </jr:columnHeader> <jr:columnFooter height="0" rowSpan="1"/> <jr:detailCell height="14" rowSpan="1"> <textField> <reportElement uuid="60cd5162-43a2-4502-8345-09d83a10364a" x="0" y="0" width="85" height="14"/> <textElement/> <textFieldExpression><![CDATA[$F{PRODUCTNUM}]]></textFieldExpression> </textField> </jr:detailCell> </jr:column> <jr:column width="369" uuid="9da2a37f-789f-41d7-8e2d-ca0eba9e2e94"> <jr:tableHeader height="0" rowSpan="1"/> <jr:columnHeader height="16" rowSpan="1"> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement uuid="f24d5fc5-e60c-423d-9c6f-efcd92977b02" x="0" y="0" width="369" height="16"/> <box> <pen lineStyle="Solid"/> <topPen lineStyle="Solid"/> <leftPen lineStyle="Solid"/> <bottomPen lineWidth="2.0" lineStyle="Solid"/> <rightPen lineStyle="Solid"/> </box> <textElement markup="none"> </textElement> <textFieldExpression><![CDATA["Product description"]]></textFieldExpression> </textField> </jr:columnHeader> <jr:columnFooter height="0" rowSpan="1"/> <jr:detailCell height="14" rowSpan="1"> <textField> <reportElement uuid="2fa5f8bb-9780-45d7-978d-087693fa0912" x="0" y="0" width="369" height="14"/> <textElement/> <textFieldExpression><![CDATA[$F{DESCRIPTION}]]></textFieldExpression> </textField> </jr:detailCell> </jr:column> <jr:column width="90" uuid="22a8714a-e612-4f7d-9e9d-10dcbc3686ad"> <jr:tableHeader height="0" rowSpan="1"/> <jr:tableFooter height="0" rowSpan="1"/> <jr:columnHeader height="16" rowSpan="1"/> <jr:columnFooter height="0" rowSpan="1"/> <jr:detailCell height="14" rowSpan="1"> <textField> <reportElement uuid="36f81e58-de5f-4145-952f-10410e1fe4c7" x="0" y="0" width="90" height="14"/> <textElement/> <textFieldExpression><![CDATA[$F{SALESPERSON}]]></textFieldExpression> </textField> </jr:detailCell> </jr:column> <jr:column width="90" uuid="d4b8b5ed-420a-4a70-b8a4-93b0c01af98f"> <jr:tableHeader height="0" rowSpan="1"/> <jr:tableFooter height="0" rowSpan="1"/> <jr:columnHeader height="16" rowSpan="1"/> <jr:columnFooter height="0" rowSpan="1"/> <jr:detailCell height="14" rowSpan="1"/> </jr:column> <jr:column width="90" uuid="975b954f-be10-4b0b-b4ad-22a66212caa5"> <jr:tableHeader height="0" rowSpan="1"/> <jr:columnHeader height="16" rowSpan="1"> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement uuid="939f5abb-6a4f-44e1-b5c5-ec1ea7eb2a18" x="0" y="0" width="90" height="16"/> <box> <bottomPen lineWidth="2.0"/> </box> <textElement markup="none"> </textElement> <textFieldExpression><![CDATA["SO nr"]]></textFieldExpression> </textField> </jr:columnHeader> <jr:columnFooter height="0" rowSpan="1"/> <jr:detailCell height="14" rowSpan="1"> <textField> <reportElement uuid="0760ed2d-a0a7-487c-a9a5-1bfff6f22af7" x="0" y="0" width="90" height="14"/> <textElement/> <textFieldExpression><![CDATA[$F{SONUM}]]></textFieldExpression> </textField> </jr:detailCell> </jr:column> <jr:columnGroup width="360" uuid="7bfd772a-992b-4c05-b19e-0448de5d6278"> <jr:column width="90" uuid="4bd6be5f-3468-4ab8-972c-5f25ac8ea4cf"> <printWhenExpression><![CDATA[$P{colUnitCost}]]></printWhenExpression> <jr:columnHeader height="16" rowSpan="1"> <box> <bottomPen lineWidth="1.0"/> </box> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement uuid="248c1506-4a3b-4c89-827b-ac51c9e3c383" x="0" y="0" width="90" height="16"/> <box> <bottomPen lineWidth="2.0"/> </box> <textElement textAlignment="Right" markup="none"> </textElement> <textFieldExpression><![CDATA["Unit Cost"]]></textFieldExpression> </textField> </jr:columnHeader> <jr:detailCell height="14" rowSpan="1"> <textField isStretchWithOverflow="true" pattern="#,##0.00###" isBlankWhenNull="true"> <reportElement uuid="befe9f41-baf6-4fc6-95a1-37244e3cbbf2" x="0" y="0" width="90" height="14"/> <textElement textAlignment="Right"> </textElement> <textFieldExpression><![CDATA[$F{POSTEDTOTALCOST} / $F{QTY}]]></textFieldExpression> </textField> </jr:detailCell> </jr:column> <jr:column width="90" uuid="a579f110-9084-42ae-ba76-c3e4bd18eee5"> <printWhenExpression><![CDATA[$P{colQty}]]></printWhenExpression> <jr:columnHeader height="16" rowSpan="1"> <box> <bottomPen lineWidth="1.0"/> </box> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement uuid="0a7b1e73-703a-4cdd-b3ea-96c4b98bdd56" x="0" y="0" width="70" height="16"/> <box rightPadding="15"> <bottomPen lineWidth="2.0"/> </box> <textElement textAlignment="Right" markup="none"> </textElement> <textFieldExpression><![CDATA["Qty"]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement uuid="6fbc99a3-0ce1-47e0-8e83-9cb4f977340f" x="70" y="0" width="20" height="16"/> <box rightPadding="15"> <bottomPen lineWidth="2.0"/> </box> <textElement textAlignment="Right" markup="none"> </textElement> <textFieldExpression><![CDATA["U"]]></textFieldExpression> </textField> </jr:columnHeader> <jr:detailCell height="14" rowSpan="1"> <textField isStretchWithOverflow="true" pattern="#,##0.#####" isBlankWhenNull="true"> <reportElement uuid="2f6ecab2-5e5d-4881-a1f3-466781da090a" x="0" y="0" width="70" height="14"/> <box rightPadding="3"/> <textElement textAlignment="Right"> </textElement> <textFieldExpression><![CDATA[$F{QTY}]]></textFieldExpression> </textField> <textField isBlankWhenNull="true"> <reportElement uuid="de988f6f-74fa-4681-a180-a4df3871a31b" x="70" y="0" width="20" height="14"/> <textElement> </textElement> <textFieldExpression><![CDATA[$F{UOMCODE}]]></textFieldExpression> </textField> </jr:detailCell> </jr:column> <jr:column width="90" uuid="9cc1c8f2-aff5-4a24-bb01-317bfbb1c2d7"> <printWhenExpression><![CDATA[$P{colUnitPrice}]]></printWhenExpression> <jr:columnHeader height="16" rowSpan="1"> <box> <bottomPen lineWidth="1.0"/> </box> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement uuid="03ceea93-cd96-423f-b94e-2d63a450ba80" x="0" y="0" width="90" height="16"/> <box> <bottomPen lineWidth="2.0"/> </box> <textElement textAlignment="Right" markup="none"> </textElement> <textFieldExpression><![CDATA["Unit Price"]]></textFieldExpression> </textField> </jr:columnHeader> <jr:detailCell height="14" rowSpan="1"> <textField isStretchWithOverflow="true" pattern="#,##0.00###" isBlankWhenNull="true"> <reportElement uuid="938a1bdd-b02d-4ed0-a944-02e369dc30f7" x="0" y="0" width="90" height="14"/> <textElement textAlignment="Right"> </textElement> <textFieldExpression><![CDATA[$F{UNITPRICE}]]></textFieldExpression> </textField> </jr:detailCell> </jr:column> <jr:column width="90" uuid="9fe7e67e-a522-425f-8f7e-387b3fce08ad"> <printWhenExpression><![CDATA[$P{colTotalPrice}]]></printWhenExpression> <jr:columnHeader height="16" rowSpan="1"> <box> <bottomPen lineWidth="1.0"/> </box> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement uuid="89c15dae-9df7-4506-bba8-28c48ef6f92c" x="0" y="0" width="90" height="16"/> <box> <bottomPen lineWidth="2.0"/> </box> <textElement textAlignment="Right" markup="none"> </textElement> <textFieldExpression><![CDATA["Total Price"]]></textFieldExpression> </textField> </jr:columnHeader> <jr:detailCell height="14" rowSpan="1"> <textField isStretchWithOverflow="true" pattern="#,##0.00###" isBlankWhenNull="true"> <reportElement uuid="176fc28c-86de-4f4c-bb8a-748eadd54aaf" x="0" y="0" width="90" height="14"/> <textElement textAlignment="Right"> </textElement> <textFieldExpression><![CDATA[$F{QTY} * $F{UNITPRICE}]]></textFieldExpression> </textField> </jr:detailCell> </jr:column> </jr:columnGroup> </jr:columnGroup> <jr:column width="90" uuid="90dd48ef-35fe-4c04-bbb8-d26e3e941644"> <printWhenExpression><![CDATA[$P{colCOGS}]]></printWhenExpression> <jr:tableFooter height="0" rowSpan="1"/> <jr:groupHeader groupName="PRODUCTNUM"> <jr:cell height="0" rowSpan="1"> <box> <bottomPen lineWidth="1.0" lineColor="#CCCCCC"/> </box> </jr:cell> </jr:groupHeader> <jr:groupHeader groupName="GroupBy"> <jr:cell height="0" rowSpan="1"> <box> <bottomPen lineWidth="1.0"/> </box> </jr:cell> </jr:groupHeader> <jr:groupFooter groupName="PRODUCTNUM"> <jr:cell height="0" rowSpan="1"> <box> <topPen lineWidth="1.0" lineColor="#CCCCCC"/> <bottomPen lineWidth="0.0"/> </box> </jr:cell> </jr:groupFooter> <jr:groupFooter groupName="GroupBy"> <jr:cell height="0" rowSpan="1"> <box> <topPen lineWidth="1.0"/> </box> </jr:cell> </jr:groupFooter> <jr:columnHeader height="16" rowSpan="1"> <box> <bottomPen lineWidth="1.0"/> </box> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement uuid="b01ac94c-9c93-43e3-aae6-d174de4be9f0" x="0" y="0" width="90" height="16"/> <box> <bottomPen lineWidth="2.0"/> </box> <textElement textAlignment="Right" markup="none"> </textElement> <textFieldExpression><![CDATA["COGS"]]></textFieldExpression> </textField> </jr:columnHeader> <jr:detailCell height="14" rowSpan="1"> <textField isStretchWithOverflow="true" pattern="#,##0.00" isBlankWhenNull="true"> <reportElement uuid="ed76023d-59af-4fc0-a2cd-6d25261d319a" x="0" y="0" width="90" height="14"/> <textElement textAlignment="Right"> </textElement> <textFieldExpression><![CDATA[$F{POSTEDTOTALCOST}]]></textFieldExpression> </textField> </jr:detailCell> </jr:column> <jr:column width="90" uuid="20724816-aab5-44dd-a800-df9f4408384b"> <printWhenExpression><![CDATA[$P{colMargins}]]></printWhenExpression> <jr:tableFooter height="0" rowSpan="1"/> <jr:groupHeader groupName="PRODUCTNUM"> <jr:cell height="0" rowSpan="1"> <box> <bottomPen lineWidth="1.0" lineColor="#CCCCCC"/> </box> </jr:cell> </jr:groupHeader> <jr:groupHeader groupName="GroupBy"> <jr:cell height="0" rowSpan="1"> <box> <bottomPen lineWidth="1.0"/> </box> </jr:cell> </jr:groupHeader> <jr:groupFooter groupName="PRODUCTNUM"> <jr:cell height="0" rowSpan="1"> <box> <topPen lineWidth="1.0" lineColor="#CCCCCC"/> <bottomPen lineWidth="0.0"/> </box> </jr:cell> </jr:groupFooter> <jr:groupFooter groupName="GroupBy"> <jr:cell height="0" rowSpan="1"> <box> <topPen lineWidth="1.0"/> </box> </jr:cell> </jr:groupFooter> <jr:columnHeader height="16" rowSpan="1"> <box> <bottomPen lineWidth="1.0"/> </box> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement uuid="3a1052e7-2b6c-427d-9e60-71a040a16dbc" x="0" y="0" width="90" height="16"/> <box> <bottomPen lineWidth="2.0"/> </box> <textElement textAlignment="Right" markup="none"> </textElement> <textFieldExpression><![CDATA["Margins"]]></textFieldExpression> </textField> </jr:columnHeader> <jr:detailCell height="14" rowSpan="1"> <textField isStretchWithOverflow="true" pattern="#,##0.00" isBlankWhenNull="true"> <reportElement uuid="577b1151-819c-4c56-ab2e-2596ee03bbb1" x="0" y="0" width="90" height="14"/> <textElement textAlignment="Right"> </textElement> <textFieldExpression><![CDATA[($F{UNITPRICE} * $F{QTY}) - $F{POSTEDTOTALCOST}]]></textFieldExpression> </textField> </jr:detailCell> </jr:column> <jr:column width="92" uuid="1a41def9-dd1a-45cc-b66d-68ef1b27443c"> <printWhenExpression><![CDATA[$P{colMargins%}]]></printWhenExpression> <jr:tableFooter height="0" rowSpan="1"/> <jr:groupHeader groupName="PRODUCTNUM"> <jr:cell height="0" rowSpan="1"> <box> <bottomPen lineWidth="1.0" lineColor="#CCCCCC"/> </box> </jr:cell> </jr:groupHeader> <jr:groupHeader groupName="GroupBy"> <jr:cell height="0" rowSpan="1"> <box> <bottomPen lineWidth="1.0"/> </box> </jr:cell> </jr:groupHeader> <jr:groupFooter groupName="PRODUCTNUM"> <jr:cell height="0" rowSpan="1"> <box> <topPen lineWidth="1.0" lineColor="#CCCCCC"/> <bottomPen lineWidth="0.0"/> </box> </jr:cell> </jr:groupFooter> <jr:groupFooter groupName="GroupBy"> <jr:cell height="0" rowSpan="1"> <box> <topPen lineWidth="1.0"/> </box> </jr:cell> </jr:groupFooter> <jr:columnHeader height="16" rowSpan="1"> <box> <bottomPen lineWidth="1.0"/> </box> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement uuid="0fd8c5a1-2e2c-4d65-a7ad-49739ae9c221" x="0" y="0" width="92" height="16"/> <box> <bottomPen lineWidth="2.0"/> </box> <textElement textAlignment="Right" markup="none"> </textElement> <textFieldExpression><![CDATA["Margins %"]]></textFieldExpression> </textField> </jr:columnHeader> <jr:detailCell height="14" rowSpan="1"> <textField isStretchWithOverflow="true" pattern="#,##0.00" isBlankWhenNull="true"> <reportElement uuid="9ce0b14b-03e6-4ff7-8c25-5de411618f8a" x="0" y="0" width="92" height="14"/> <textElement textAlignment="Right"> </textElement> <textFieldExpression><![CDATA[($F{UNITPRICE} > 0) ? ((($F{UNITPRICE} * $F{QTY}) - $F{POSTEDTOTALCOST}) / ($F{QTY} * $F{UNITPRICE})) : 0 / 100]]></textFieldExpression> </textField> </jr:detailCell> </jr:column> </jr:table> </componentElement> </band> </detail> <pageFooter> <band splitType="Stretch"/> </pageFooter> </jasperReport>

Add one extra field - error

mathieu.ververken replied to mathieu.ververken's topic in Products

CODE 1: <?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="MarginsReport" pageWidth="792" pageHeight="612" orientation="Landscape" whenNoDataType="AllSectionsNoDetail" columnWidth="792" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" uuid="f4f863e0-cf8a-47e0-8388-cb12b147627f"> <property name="ireport.zoom" value="1.0"/> <property name="ireport.x" value="0"/> <property name="ireport.y" value="0"/> <property name="ireport.callouts" value="##Wed Mar 11 12:36:56 MST 2015"/> <subDataset name="ProductTreeInfo" uuid="69478527-2a9e-419f-a71f-9b3ee82608f8"> <parameter name="locationGroupID" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["%"]]></defaultValueExpression> </parameter> <parameter name="salesPerson" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["%"]]></defaultValueExpression> </parameter> <parameter name="ckIncludeShipping" class="java.lang.String" isForPrompting="false"> <parameterDescription><![CDATA[60,900]]></parameterDescription> <defaultValueExpression><![CDATA["900"]]></defaultValueExpression> </parameter> <parameter name="customerID" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["%"]]></defaultValueExpression> </parameter> <parameter name="ckIncludeReturns" class="java.lang.String" isForPrompting="false"> <parameterDescription><![CDATA[20,900]]></parameterDescription> <defaultValueExpression><![CDATA["20"]]></defaultValueExpression> </parameter> <parameter name="productNum" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["%"]]></defaultValueExpression> </parameter> <parameter name="dateRange2" class="java.util.Date" isForPrompting="false"> <defaultValueExpression><![CDATA[new Date()]]></defaultValueExpression> </parameter> <parameter name="dateRange1" class="java.util.Date" isForPrompting="false"> <parameterDescription><![CDATA[This Month]]></parameterDescription> <defaultValueExpression><![CDATA[new Date()]]></defaultValueExpression> </parameter> <parameter name="soNum" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["%"]]></defaultValueExpression> </parameter> <parameter name="cbGroupBy" class="java.lang.String"> <parameterDescription><![CDATA[]]></parameterDescription> <defaultValueExpression><![CDATA["soitem.productnum"]]></defaultValueExpression> </parameter> <parameter name="productTree1" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["0"]]></defaultValueExpression> </parameter> <parameter name="productTree2" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["%"]]></defaultValueExpression> </parameter> <parameter name="colProduct" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <parameter name="colUnitCost" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <parameter name="colQty" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <parameter name="colUnitPrice" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <parameter name="colTotalPrice" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <parameter name="colCOGS" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <parameter name="colMargins" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <parameter name="colMargins%" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <queryString> <![CDATA[sELECT $P!{cbGroupBy} AS groupby, so.num AS sonum, customer.name AS custname, soitem.productnum, soitem.description, (case when soitem.uomid != product.uomid then ((postsoitem.qty*uomconversion.multiply)/uomconversion.factor) else postsoitem.qty end) as qty, postsoitem.postedtotalcost, (CASE WHEN soitem.uomid != product.uomid then ((soitem.unitprice + (CASE WHEN soitem.adjustamount > 0 THEN (soitem.adjustamount / soitem.qtytofulfill) ELSE 0 END))/uomconversion.multiply)*uomconversion.factor else (soitem.unitprice + (CASE WHEN soitem.adjustamount > 0 THEN (soitem.adjustamount / soitem.qtytofulfill) ELSE 0 END)) end) as unitprice, COALESCE(uom.code, miscuom.code) AS uomcode, producttree.name AS producttreenode FROM so INNER JOIN customer ON so.customerid = customer.id INNER JOIN soitem ON so.id = soitem.soid LEFT JOIN product ON soitem.productid = product.id INNER JOIN postsoitem ON soitem.id = postsoitem.soitemid INNER JOIN postso ON postsoitem.postsoid = postso.id LEFT JOIN uom ON product.uomid = uom.id left JOIN uom as miscuom on soitem.uomid = miscuom.id LEFT JOIN producttotree ON soitem.productid = producttotree.productid LEFT JOIN producttree ON producttotree.producttreeid = producttree.id LEFT JOIN uomconversion on product.uomid = uomconversion.touomid and soitem.uomid = uomconversion.fromuomid WHERE UPPER(so.num) LIKE UPPER($P{soNum}) AND UPPER(so.salesman) LIKE UPPER($P{salesPerson}) AND so.customerid LIKE $P{customerID} AND postso.postdate BETWEEN $P{dateRange1} AND $P{dateRange2} AND soitem.typeid IN (10,11,12,21,30,31,50,80,$P{ckIncludeShipping},$P{ckIncludeReturns}) AND soitem.productnum LIKE $P{productNum} AND so.locationgroupid IN ($P!{locationGroupID}) AND (COALESCE(producttreeid,0) IN ($P!{productTree1}) OR COALESCE(producttreeid,0) LIKE $P{productTree2}) ORDER BY 1, soitem.productnum, soitem.uomid, so.num]]> </queryString> <field name="GROUPBY" class="java.lang.String"/> <field name="SONUM" class="java.lang.String"/> <field name="CUSTNAME" class="java.lang.String"/> <field name="PRODUCTNUM" class="java.lang.String"/> <field name="DESCRIPTION" class="java.lang.String"/> <field name="QTY" class="java.lang.Double"/> <field name="POSTEDTOTALCOST" class="java.lang.Double"/> <field name="UNITPRICE" class="java.lang.Double"/> <field name="UOMCODE" class="java.lang.String"/> <field name="PRODUCTTREENODE" class="java.lang.String"/> <variable name="ProductTotalCost" class="java.lang.Double" resetType="Group" resetGroup="PRODUCTNUM" calculation="Sum"> <variableExpression><![CDATA[$F{POSTEDTOTALCOST}]]></variableExpression> </variable> <variable name="ProductTotalPrice" class="java.lang.Double" resetType="Group" resetGroup="PRODUCTNUM" calculation="Sum"> <variableExpression><![CDATA[$F{QTY} * $F{UNITPRICE}]]></variableExpression> </variable> <variable name="GroupByTotalCost" class="java.lang.Double" resetType="Group" resetGroup="GroupBy" calculation="Sum"> <variableExpression><![CDATA[$F{POSTEDTOTALCOST}]]></variableExpression> </variable> <variable name="GroupByTotalPrice" class="java.lang.Double" resetType="Group" resetGroup="GroupBy" calculation="Sum"> <variableExpression><![CDATA[$F{QTY} * $F{UNITPRICE}]]></variableExpression> </variable> <variable name="ProductGrandTotalCost" class="java.lang.Double" calculation="Sum"> <variableExpression><![CDATA[$F{POSTEDTOTALCOST}]]></variableExpression> </variable> <variable name="ProductGrandTotalPrice" class="java.lang.Double" calculation="Sum"> <variableExpression><![CDATA[$F{QTY} * $F{UNITPRICE}]]></variableExpression> </variable> <group name="GroupBy"> <groupExpression><![CDATA[$F{GROUPBY}]]></groupExpression> </group> <group name="PRODUCTNUM"> <groupExpression><![CDATA[$F{PRODUCTNUM}]]></groupExpression> </group> <group name="UOMCODE"> <groupExpression><![CDATA[$F{UOMCODE}]]></groupExpression> </group> </subDataset> <subDataset name="ProductInfo" uuid="e27e8375-ef55-48e8-b6c3-e450f710115a"> <parameter name="locationGroupID" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["%"]]></defaultValueExpression> </parameter> <parameter name="salesPerson" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["%"]]></defaultValueExpression> </parameter> <parameter name="ckIncludeShipping" class="java.lang.String" isForPrompting="false"> <parameterDescription><![CDATA[60,900]]></parameterDescription> <defaultValueExpression><![CDATA["900"]]></defaultValueExpression> </parameter> <parameter name="customerID" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["%"]]></defaultValueExpression> </parameter> <parameter name="ckIncludeReturns" class="java.lang.String" isForPrompting="false"> <parameterDescription><![CDATA[20,900]]></parameterDescription> <defaultValueExpression><![CDATA["20"]]></defaultValueExpression> </parameter> <parameter name="productNum" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["%"]]></defaultValueExpression> </parameter> <parameter name="dateRange2" class="java.util.Date" isForPrompting="false"> <defaultValueExpression><![CDATA[new Date()]]></defaultValueExpression> </parameter> <parameter name="dateRange1" class="java.util.Date" isForPrompting="false"> <parameterDescription><![CDATA[This Month]]></parameterDescription> <defaultValueExpression><![CDATA[new Date()]]></defaultValueExpression> </parameter> <parameter name="soNum" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["%"]]></defaultValueExpression> </parameter> <parameter name="cbGroupBy" class="java.lang.String"> <parameterDescription><![CDATA[]]></parameterDescription> <defaultValueExpression><![CDATA["soitem.productnum"]]></defaultValueExpression> </parameter> <parameter name="productTree1" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["0"]]></defaultValueExpression> </parameter> <parameter name="productTree2" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["%"]]></defaultValueExpression> </parameter> <parameter name="colProduct" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <parameter name="colUnitCost" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <parameter name="colQty" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <parameter name="colUnitPrice" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <parameter name="colTotalPrice" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <parameter name="colCOGS" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <parameter name="colMargins" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <parameter name="colMargins%" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <queryString> <![CDATA[sELECT (CASE WHEN $P!{cbGroupBy} = '' THEN soitem.description ELSE $P!{cbGroupBy} END) AS groupby, so.num AS sonum, customer.name AS custname, (CASE WHEN soitem.typeid = 11 THEN 'Misc. Sale' ELSE (CASE WHEN soitem.typeid = 21 THEN 'Misc. Credit' ELSE soitem.productnum END) END) AS productnum, soitem.description, SUM((case when soitem.uomid != product.uomid then ((postsoitem.qty*uomconversion.multiply)/uomconversion.factor) else postsoitem.qty end)) AS qty, SUM(postsoitem.postedtotalcost) AS postedtotalcost, (CASE WHEN soitem.uomid != product.uomid then ((soitem.unitprice + (CASE WHEN soitem.adjustamount > 0 THEN (soitem.adjustamount / soitem.qtytofulfill) ELSE 0 END))/uomconversion.multiply)*uomconversion.factor else (soitem.unitprice + (CASE WHEN soitem.adjustamount > 0 THEN (soitem.adjustamount / soitem.qtytofulfill) ELSE 0 END)) end) as unitprice, COALESCE(uom.code, miscuom.code) AS uomcode FROM so INNER JOIN customer ON so.customerid = customer.id INNER JOIN soitem ON so.id = soitem.soid LEFT JOIN product ON soitem.productid = product.id INNER JOIN postsoitem ON soitem.id = postsoitem.soitemid INNER JOIN postso ON postsoitem.postsoid = postso.id LEFT JOIN uom ON product.uomid = uom.id LEFT JOIN uom as miscuom on soitem.uomid = miscuom.id LEFT JOIN uomconversion ON product.uomid = uomconversion.touomid AND soitem.uomid = uomconversion.fromuomid WHERE UPPER(so.num) LIKE UPPER($P{soNum}) AND UPPER(so.salesman) LIKE UPPER($P{salesPerson}) AND so.customerid LIKE $P{customerID} AND postso.postdate BETWEEN $P{dateRange1} AND $P{dateRange2} AND soitem.typeid IN (10,11,12,21,30,31,50,80,$P{ckIncludeShipping},$P{ckIncludeReturns}) AND soitem.productnum LIKE $P{productNum} AND so.locationgroupid IN ($P!{locationGroupID}) GROUP BY soitem.productnum, uomcode, sonum, custname, soitem.description, soitem.unitprice, groupby, soitem.typeid, product.uomid, uomconversion.multiply, uomconversion.factor, unitprice ORDER BY 1, productnum, uomcode, sonum, custname, soitem.description, soitem.unitprice]]> </queryString> <field name="GROUPBY" class="java.lang.String"/> <field name="SONUM" class="java.lang.String"/> <field name="CUSTNAME" class="java.lang.String"/> <field name="PRODUCTNUM" class="java.lang.String"/> <field name="DESCRIPTION" class="java.lang.String"/> <field name="QTY" class="java.lang.Double"/> <field name="POSTEDTOTALCOST" class="java.lang.Double"/> <field name="UNITPRICE" class="java.lang.Double"/> <field name="UOMCODE" class="java.lang.String"/> <variable name="ProductTotalCost" class="java.lang.Double" resetType="Group" resetGroup="PRODUCTNUM" calculation="Sum"> <variableExpression><![CDATA[$F{POSTEDTOTALCOST}]]></variableExpression> </variable> <variable name="ProductTotalPrice" class="java.lang.Double" resetType="Group" resetGroup="PRODUCTNUM" calculation="Sum"> <variableExpression><![CDATA[$F{QTY} * $F{UNITPRICE}]]></variableExpression> </variable> <variable name="GroupByTotalCost" class="java.lang.Double" resetType="Group" resetGroup="GroupBy" calculation="Sum"> <variableExpression><![CDATA[$F{POSTEDTOTALCOST}]]></variableExpression> </variable> <variable name="GroupByTotalPrice" class="java.lang.Double" resetType="Group" resetGroup="GroupBy" calculation="Sum"> <variableExpression><![CDATA[$F{QTY} * $F{UNITPRICE}]]></variableExpression> </variable> <variable name="ProductGrandTotalCost" class="java.lang.Double" calculation="Sum"> <variableExpression><![CDATA[$F{POSTEDTOTALCOST}]]></variableExpression> </variable> <variable name="ProductGrandTotalPrice" class="java.lang.Double" calculation="Sum"> <variableExpression><![CDATA[$F{QTY} * $F{UNITPRICE}]]></variableExpression> </variable> <group name="GroupBy"> <groupExpression><![CDATA[$F{GROUPBY}]]></groupExpression> </group> <group name="PRODUCTNUM"> <groupExpression><![CDATA[$F{PRODUCTNUM}]]></groupExpression> </group> <group name="UOMCODE"> <groupExpression><![CDATA[$F{UOMCODE}]]></groupExpression> </group> </subDataset> <parameter name="dateRange1" class="java.util.Date" isForPrompting="false"> <parameterDescription><![CDATA[This Month]]></parameterDescription> <defaultValueExpression><![CDATA[new Date()]]></defaultValueExpression> </parameter> <parameter name="dateRange2" class="java.util.Date" isForPrompting="false"> <defaultValueExpression><![CDATA[new Date()]]></defaultValueExpression> </parameter> <parameter name="productNum" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["%"]]></defaultValueExpression> </parameter> <parameter name="path" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["../SalesOrder/"]]></defaultValueExpression> </parameter> <parameter name="module" class="java.lang.Object" isForPrompting="false"> <defaultValueExpression><![CDATA[null]]></defaultValueExpression> </parameter> <parameter name="ckIncludeShipping" class="java.lang.String" isForPrompting="false"> <parameterDescription><![CDATA[60,900]]></parameterDescription> <defaultValueExpression><![CDATA["900"]]></defaultValueExpression> </parameter> <parameter name="ckIncludeReturns" class="java.lang.String" isForPrompting="false"> <parameterDescription><![CDATA[20,900]]></parameterDescription> <defaultValueExpression><![CDATA["20"]]></defaultValueExpression> </parameter> <parameter name="salesPerson" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["%"]]></defaultValueExpression> </parameter> <parameter name="customerID" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["%"]]></defaultValueExpression> </parameter> <parameter name="locationGroupID" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["%"]]></defaultValueExpression> </parameter> <parameter name="soNum" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["%"]]></defaultValueExpression> </parameter> <parameter name="cbGroupBy" class="java.lang.String"> <parameterDescription><![CDATA[soitem.productnum,Product,producttree.name,Product Tree,customer.name,Customer]]></parameterDescription> <defaultValueExpression><![CDATA["soitem.productnum"]]></defaultValueExpression> </parameter> <parameter name="productTree1" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["0"]]></defaultValueExpression> </parameter> <parameter name="productTree2" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["%"]]></defaultValueExpression> </parameter> <parameter name="colProduct" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <parameter name="colUnitCost" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <parameter name="colQty" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <parameter name="colUnitPrice" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <parameter name="colTotalPrice" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <parameter name="colCOGS" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <parameter name="colMargins" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <parameter name="colMargins%" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[true]]></defaultValueExpression> </parameter> <queryString> <![CDATA[sELECT company.name AS company FROM company WHERE id = 1]]> </queryString> <field name="COMPANY" class="java.lang.String"/> <variable name="DateFormat" class="java.lang.String" resetType="None"> <variableExpression><![CDATA[(System.getProperty("REPORT_DATE_FORMAT"))]]></variableExpression> </variable> <background> <band splitType="Stretch"/> </background> <pageHeader> <band splitType="Stretch"/> </pageHeader> <detail> <band height="86"> <printWhenExpression><![CDATA[$P{cbGroupBy}.equals("customer.name")]]></printWhenExpression> <componentElement> <reportElement uuid="397cf7c1-f875-499a-a12f-5af3222bc192" key="table" x="0" y="0" width="732" height="86"/> <jr:table xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd"> <datasetRun subDataset="ProductInfo" uuid="3d765615-02b8-4a6c-9cca-d4c57f366378"> <datasetParameter name="locationGroupID"> <datasetParameterExpression><![CDATA[$P{locationGroupID}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="salesPerson"> <datasetParameterExpression><![CDATA[$P{salesPerson}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="ckIncludeShipping"> <datasetParameterExpression><![CDATA[$P{ckIncludeShipping}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="customerID"> <datasetParameterExpression><![CDATA[$P{customerID}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="ckIncludeReturns"> <datasetParameterExpression><![CDATA[$P{ckIncludeReturns}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="productNum"> <datasetParameterExpression><![CDATA[$P{productNum}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="dateRange2"> <datasetParameterExpression><![CDATA[$P{dateRange2}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="dateRange1"> <datasetParameterExpression><![CDATA[$P{dateRange1}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="soNum"> <datasetParameterExpression><![CDATA[$P{soNum}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="cbGroupBy"> <datasetParameterExpression><![CDATA[$P{cbGroupBy}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="productTree1"> <datasetParameterExpression><![CDATA[$P{productTree1}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="productTree2"> <datasetParameterExpression><![CDATA[$P{productTree2}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="colProduct"> <datasetParameterExpression><![CDATA[$P{colProduct}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="colUnitCost"> <datasetParameterExpression><![CDATA[$P{colUnitCost}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="colQty"> <datasetParameterExpression><![CDATA[$P{colQty}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="colUnitPrice"> <datasetParameterExpression><![CDATA[$P{colUnitPrice}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="colCOGS"> <datasetParameterExpression><![CDATA[$P{colCOGS}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="colMargins"> <datasetParameterExpression><![CDATA[$P{colMargins}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="colMargins%"> <datasetParameterExpression><![CDATA[$P{colMargins%}]]></datasetParameterExpression> </datasetParameter> <datasetParameter name="colTotalPrice"> <datasetParameterExpression><![CDATA[$P{colTotalPrice}]]></datasetParameterExpression> </datasetParameter> <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression> </datasetRun> <jr:columnGroup width="1174" uuid="28b68d85-d5a6-4462-859d-6c1599746e35"> <jr:column width="90" uuid="f12cd1df-15e3-41df-ac91-facec8190a40"> <jr:tableHeader height="0" rowSpan="1"/> <jr:columnHeader height="16" rowSpan="1"> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement uuid="84a77d2e-0ad8-42a7-99a9-99abd9f4a254" x="0" y="0" width="90" height="16"/> <box> <bottomPen lineWidth="2.0"/> </box> <textElement markup="none"> </textElement> <textFieldExpression><![CDATA["Customer"]]></textFieldExpression> </textField> </jr:columnHeader> <jr:columnFooter height="0" rowSpan="1"/> <jr:detailCell height="14" rowSpan="1"> <textField> <reportElement uuid="2ff6f464-c81c-403d-a136-7eda88165004" x="0" y="0" width="90" height="14"/> <textElement/> <textFieldExpression><![CDATA[$F{CUSTNAME}]]></textFieldExpression> </textField> </jr:detailCell> </jr:column> <jr:column width="85" uuid="0d6bd9ba-b6f3-4ece-8466-bdc17341517b"> <jr:tableHeader height="0" rowSpan="1"/> <jr:columnHeader height="16" rowSpan="1"> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement uuid="7ae1056c-2216-471d-9073-2794bd8e9051" x="0" y="0" width="85" height="16"/> <box> <bottomPen lineWidth="2.0"/> </box> <textElement markup="none"> </textElement> <textFieldExpression><![CDATA["Product nr"]]></textFieldExpression> </textField> </jr:columnHeader> <jr:columnFooter height="0" rowSpan="1"/> <jr:detailCell height="14" rowSpan="1"> <textField> <reportElement uuid="60cd5162-43a2-4502-8345-09d83a10364a" x="0" y="0" width="85" height="14"/> <textElement/> <textFieldExpression><![CDATA[$F{PRODUCTNUM}]]></textFieldExpression> </textField> </jr:detailCell> </jr:column> <jr:column width="369" uuid="9da2a37f-789f-41d7-8e2d-ca0eba9e2e94"> <jr:tableHeader height="0" rowSpan="1"/> <jr:columnHeader height="16" rowSpan="1"> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement uuid="f24d5fc5-e60c-423d-9c6f-efcd92977b02" x="0" y="0" width="369" height="16"/> <box> <pen lineStyle="Solid"/> <topPen lineStyle="Solid"/> <leftPen lineStyle="Solid"/> <bottomPen lineWidth="2.0" lineStyle="Solid"/> <rightPen lineStyle="Solid"/> </box> <textElement markup="none"> </textElement> <textFieldExpression><![CDATA["Product description"]]></textFieldExpression> </textField> </jr:columnHeader> <jr:columnFooter height="0" rowSpan="1"/> <jr:detailCell height="14" rowSpan="1"> <textField> <reportElement uuid="2fa5f8bb-9780-45d7-978d-087693fa0912" x="0" y="0" width="369" height="14"/> <textElement/> <textFieldExpression><![CDATA[$F{DESCRIPTION}]]></textFieldExpression> </textField> </jr:detailCell> </jr:column> <jr:column width="90" uuid="22a8714a-e612-4f7d-9e9d-10dcbc3686ad"> <jr:tableHeader height="0" rowSpan="1"/> <jr:tableFooter height="0" rowSpan="1"/> <jr:columnHeader height="16" rowSpan="1"/> <jr:columnFooter height="0" rowSpan="1"/> <jr:detailCell height="14" rowSpan="1"/> </jr:column> <jr:column width="90" uuid="d4b8b5ed-420a-4a70-b8a4-93b0c01af98f"> <jr:tableHeader height="0" rowSpan="1"/> <jr:tableFooter height="0" rowSpan="1"/> <jr:columnHeader height="16" rowSpan="1"/> <jr:columnFooter height="0" rowSpan="1"/> <jr:detailCell height="14" rowSpan="1"/> </jr:column> <jr:column width="90" uuid="975b954f-be10-4b0b-b4ad-22a66212caa5"> <jr:tableHeader height="0" rowSpan="1"/> <jr:columnHeader height="16" rowSpan="1"> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement uuid="939f5abb-6a4f-44e1-b5c5-ec1ea7eb2a18" x="0" y="0" width="90" height="16"/> <box> <bottomPen lineWidth="2.0"/> </box> <textElement markup="none"> </textElement> <textFieldExpression><![CDATA["SO nr"]]></textFieldExpression> </textField> </jr:columnHeader> <jr:columnFooter height="0" rowSpan="1"/> <jr:detailCell height="14" rowSpan="1"> <textField> <reportElement uuid="0760ed2d-a0a7-487c-a9a5-1bfff6f22af7" x="0" y="0" width="90" height="14"/> <textElement/> <textFieldExpression><![CDATA[$F{SONUM}]]></textFieldExpression> </textField> </jr:detailCell> </jr:column> <jr:columnGroup width="360" uuid="7bfd772a-992b-4c05-b19e-0448de5d6278"> <jr:column width="90" uuid="4bd6be5f-3468-4ab8-972c-5f25ac8ea4cf"> <printWhenExpression><![CDATA[$P{colUnitCost}]]></printWhenExpression> <jr:columnHeader height="16" rowSpan="1"> <box> <bottomPen lineWidth="1.0"/> </box> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement uuid="248c1506-4a3b-4c89-827b-ac51c9e3c383" x="0" y="0" width="90" height="16"/> <box> <bottomPen lineWidth="2.0"/> </box> <textElement textAlignment="Right" markup="none"> </textElement> <textFieldExpression><![CDATA["Unit Cost"]]></textFieldExpression> </textField> </jr:columnHeader> <jr:detailCell height="14" rowSpan="1"> <textField isStretchWithOverflow="true" pattern="#,##0.00###" isBlankWhenNull="true"> <reportElement uuid="befe9f41-baf6-4fc6-95a1-37244e3cbbf2" x="0" y="0" width="90" height="14"/> <textElement textAlignment="Right"> </textElement> <textFieldExpression><![CDATA[$F{POSTEDTOTALCOST} / $F{QTY}]]></textFieldExpression> </textField> </jr:detailCell> </jr:column> <jr:column width="90" uuid="a579f110-9084-42ae-ba76-c3e4bd18eee5"> <printWhenExpression><![CDATA[$P{colQty}]]></printWhenExpression> <jr:columnHeader height="16" rowSpan="1"> <box> <bottomPen lineWidth="1.0"/> </box> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement uuid="0a7b1e73-703a-4cdd-b3ea-96c4b98bdd56" x="0" y="0" width="70" height="16"/> <box rightPadding="15"> <bottomPen lineWidth="2.0"/> </box> <textElement textAlignment="Right" markup="none"> </textElement> <textFieldExpression><![CDATA["Qty"]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement uuid="6fbc99a3-0ce1-47e0-8e83-9cb4f977340f" x="70" y="0" width="20" height="16"/> <box rightPadding="15"> <bottomPen lineWidth="2.0"/> </box> <textElement textAlignment="Right" markup="none"> </textElement> <textFieldExpression><![CDATA["U"]]></textFieldExpression> </textField> </jr:columnHeader> <jr:detailCell height="14" rowSpan="1"> <textField isStretchWithOverflow="true" pattern="#,##0.#####" isBlankWhenNull="true"> <reportElement uuid="2f6ecab2-5e5d-4881-a1f3-466781da090a" x="0" y="0" width="70" height="14"/> <box rightPadding="3"/> <textElement textAlignment="Right"> </textElement> <textFieldExpression><![CDATA[$F{QTY}]]></textFieldExpression> </textField> <textField isBlankWhenNull="true"> <reportElement uuid="de988f6f-74fa-4681-a180-a4df3871a31b" x="70" y="0" width="20" height="14"/> <textElement> </textElement> <textFieldExpression><![CDATA[$F{UOMCODE}]]></textFieldExpression> </textField> </jr:detailCell> </jr:column> <jr:column width="90" uuid="9cc1c8f2-aff5-4a24-bb01-317bfbb1c2d7"> <printWhenExpression><![CDATA[$P{colUnitPrice}]]></printWhenExpression> <jr:columnHeader height="16" rowSpan="1"> <box> <bottomPen lineWidth="1.0"/> </box> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement uuid="03ceea93-cd96-423f-b94e-2d63a450ba80" x="0" y="0" width="90" height="16"/> <box> <bottomPen lineWidth="2.0"/> </box> <textElement textAlignment="Right" markup="none"> </textElement> <textFieldExpression><![CDATA["Unit Price"]]></textFieldExpression> </textField> </jr:columnHeader> <jr:detailCell height="14" rowSpan="1"> <textField isStretchWithOverflow="true" pattern="#,##0.00###" isBlankWhenNull="true"> <reportElement uuid="938a1bdd-b02d-4ed0-a944-02e369dc30f7" x="0" y="0" width="90" height="14"/> <textElement textAlignment="Right"> </textElement> <textFieldExpression><![CDATA[$F{UNITPRICE}]]></textFieldExpression> </textField> </jr:detailCell> </jr:column> <jr:column width="90" uuid="9fe7e67e-a522-425f-8f7e-387b3fce08ad"> <printWhenExpression><![CDATA[$P{colTotalPrice}]]></printWhenExpression> <jr:columnHeader height="16" rowSpan="1"> <box> <bottomPen lineWidth="1.0"/> </box> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement uuid="89c15dae-9df7-4506-bba8-28c48ef6f92c" x="0" y="0" width="90" height="16"/> <box> <bottomPen lineWidth="2.0"/> </box> <textElement textAlignment="Right" markup="none"> </textElement> <textFieldExpression><![CDATA["Total Price"]]></textFieldExpression> </textField> </jr:columnHeader> <jr:detailCell height="14" rowSpan="1"> <textField isStretchWithOverflow="true" pattern="#,##0.00###" isBlankWhenNull="true"> <reportElement uuid="176fc28c-86de-4f4c-bb8a-748eadd54aaf" x="0" y="0" width="90" height="14"/> <textElement textAlignment="Right"> </textElement> <textFieldExpression><![CDATA[$F{QTY} * $F{UNITPRICE}]]></textFieldExpression> </textField> </jr:detailCell> </jr:column> </jr:columnGroup> </jr:columnGroup> <jr:column width="90" uuid="90dd48ef-35fe-4c04-bbb8-d26e3e941644"> <printWhenExpression><![CDATA[$P{colCOGS}]]></printWhenExpression> <jr:tableFooter height="0" rowSpan="1"/> <jr:groupHeader groupName="PRODUCTNUM"> <jr:cell height="0" rowSpan="1"> <box> <bottomPen lineWidth="1.0" lineColor="#CCCCCC"/> </box> </jr:cell> </jr:groupHeader> <jr:groupHeader groupName="GroupBy"> <jr:cell height="0" rowSpan="1"> <box> <bottomPen lineWidth="1.0"/> </box> </jr:cell> </jr:groupHeader> <jr:groupFooter groupName="PRODUCTNUM"> <jr:cell height="0" rowSpan="1"> <box> <topPen lineWidth="1.0" lineColor="#CCCCCC"/> <bottomPen lineWidth="0.0"/> </box> </jr:cell> </jr:groupFooter> <jr:groupFooter groupName="GroupBy"> <jr:cell height="0" rowSpan="1"> <box> <topPen lineWidth="1.0"/> </box> </jr:cell> </jr:groupFooter> <jr:columnHeader height="16" rowSpan="1"> <box> <bottomPen lineWidth="1.0"/> </box> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement uuid="b01ac94c-9c93-43e3-aae6-d174de4be9f0" x="0" y="0" width="90" height="16"/> <box> <bottomPen lineWidth="2.0"/> </box> <textElement textAlignment="Right" markup="none"> </textElement> <textFieldExpression><![CDATA["COGS"]]></textFieldExpression> </textField> </jr:columnHeader> <jr:detailCell height="14" rowSpan="1"> <textField isStretchWithOverflow="true" pattern="#,##0.00" isBlankWhenNull="true"> <reportElement uuid="ed76023d-59af-4fc0-a2cd-6d25261d319a" x="0" y="0" width="90" height="14"/> <textElement textAlignment="Right"> </textElement> <textFieldExpression><![CDATA[$F{POSTEDTOTALCOST}]]></textFieldExpression> </textField> </jr:detailCell> </jr:column> <jr:column width="90" uuid="20724816-aab5-44dd-a800-df9f4408384b"> <printWhenExpression><![CDATA[$P{colMargins}]]></printWhenExpression> <jr:tableFooter height="0" rowSpan="1"/> <jr:groupHeader groupName="PRODUCTNUM"> <jr:cell height="0" rowSpan="1"> <box> <bottomPen lineWidth="1.0" lineColor="#CCCCCC"/> </box> </jr:cell> </jr:groupHeader> <jr:groupHeader groupName="GroupBy"> <jr:cell height="0" rowSpan="1"> <box> <bottomPen lineWidth="1.0"/> </box> </jr:cell> </jr:groupHeader> <jr:groupFooter groupName="PRODUCTNUM"> <jr:cell height="0" rowSpan="1"> <box> <topPen lineWidth="1.0" lineColor="#CCCCCC"/> <bottomPen lineWidth="0.0"/> </box> </jr:cell> </jr:groupFooter> <jr:groupFooter groupName="GroupBy"> <jr:cell height="0" rowSpan="1"> <box> <topPen lineWidth="1.0"/> </box> </jr:cell> </jr:groupFooter> <jr:columnHeader height="16" rowSpan="1"> <box> <bottomPen lineWidth="1.0"/> </box> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement uuid="3a1052e7-2b6c-427d-9e60-71a040a16dbc" x="0" y="0" width="90" height="16"/> <box> <bottomPen lineWidth="2.0"/> </box> <textElement textAlignment="Right" markup="none"> </textElement> <textFieldExpression><![CDATA["Margins"]]></textFieldExpression> </textField> </jr:columnHeader> <jr:detailCell height="14" rowSpan="1"> <textField isStretchWithOverflow="true" pattern="#,##0.00" isBlankWhenNull="true"> <reportElement uuid="577b1151-819c-4c56-ab2e-2596ee03bbb1" x="0" y="0" width="90" height="14"/> <textElement textAlignment="Right"> </textElement> <textFieldExpression><![CDATA[($F{UNITPRICE} * $F{QTY}) - $F{POSTEDTOTALCOST}]]></textFieldExpression> </textField> </jr:detailCell> </jr:column> <jr:column width="92" uuid="1a41def9-dd1a-45cc-b66d-68ef1b27443c"> <printWhenExpression><![CDATA[$P{colMargins%}]]></printWhenExpression> <jr:tableFooter height="0" rowSpan="1"/> <jr:groupHeader groupName="PRODUCTNUM"> <jr:cell height="0" rowSpan="1"> <box> <bottomPen lineWidth="1.0" lineColor="#CCCCCC"/> </box> </jr:cell> </jr:groupHeader> <jr:groupHeader groupName="GroupBy"> <jr:cell height="0" rowSpan="1"> <box> <bottomPen lineWidth="1.0"/> </box> </jr:cell> </jr:groupHeader> <jr:groupFooter groupName="PRODUCTNUM"> <jr:cell height="0" rowSpan="1"> <box> <topPen lineWidth="1.0" lineColor="#CCCCCC"/> <bottomPen lineWidth="0.0"/> </box> </jr:cell> </jr:groupFooter> <jr:groupFooter groupName="GroupBy"> <jr:cell height="0" rowSpan="1"> <box> <topPen lineWidth="1.0"/> </box> </jr:cell> </jr:groupFooter> <jr:columnHeader height="16" rowSpan="1"> <box> <bottomPen lineWidth="1.0"/> </box> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement uuid="0fd8c5a1-2e2c-4d65-a7ad-49739ae9c221" x="0" y="0" width="92" height="16"/> <box> <bottomPen lineWidth="2.0"/> </box> <textElement textAlignment="Right" markup="none"> </textElement> <textFieldExpression><![CDATA["Margins %"]]></textFieldExpression> </textField> </jr:columnHeader> <jr:detailCell height="14" rowSpan="1"> <textField isStretchWithOverflow="true" pattern="#,##0.00" isBlankWhenNull="true"> <reportElement uuid="9ce0b14b-03e6-4ff7-8c25-5de411618f8a" x="0" y="0" width="92" height="14"/> <textElement textAlignment="Right"> </textElement> <textFieldExpression><![CDATA[($F{UNITPRICE} > 0) ? ((($F{UNITPRICE} * $F{QTY}) - $F{POSTEDTOTALCOST}) / ($F{QTY} * $F{UNITPRICE})) : 0 / 100]]></textFieldExpression> </textField> </jr:detailCell> </jr:column> </jr:table> </componentElement> </band> </detail> <pageFooter> <band splitType="Stretch"/> </pageFooter> </jasperReport>

Add one extra field - error

mathieu.ververken posted a topic in Products

Hi, I modified an existing report in iReport, and it works perfectly. Until I wanted to add one more field 'SalesPerson' to it. I don't get any 'report warnings', but when I want to open the report in Fishbowl I receive an error "Fishbowl serer error null". Anyone that could help me to find the mistake? The original code that works perfectly is code 1, the code with field 'SalesPerson' is code 2 (put the changes in bold). Thx a lot for your help, Mathieu

Most simple report - error

mathieu.ververken replied to mathieu.ververken's topic in Products

Thx a lot for your help mate, really appreciat it.

Most simple report - error

mathieu.ververken replied to mathieu.ververken's topic in Products

<?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="report9" language="groovy" pageWidth="842" pageHeight="595" orientation="Landscape" columnWidth="802" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="ecc5ee2c-2dda-485e-9814-44735bbf7872"> <property name="ireport.zoom" value="1.0"/> <property name="ireport.x" value="0"/> <property name="ireport.y" value="0"/> <queryString language="SQL"> <![CDATA[sELECT SO."NUM" AS SO_NUM FROM "SO" SO]]> </queryString> <field name="SO_NUM" class="java.lang.String"/> <group name="SO_NUM"> <groupExpression><![CDATA[$F{SO_NUM}]]></groupExpression> </group> <background> <band splitType="Stretch"/> </background> <title> <band height="79" splitType="Stretch"/> </title> <pageHeader> <band height="35" splitType="Stretch"/> </pageHeader> <columnHeader> <band height="61" splitType="Stretch"> <staticText> <reportElement uuid="ba6dc6df-885b-45a2-b622-27cd45a9620f" x="104" y="15" width="100" height="20"/> <textElement/> <text><![CDATA[sO_NUM]]></text> </staticText> </band> </columnHeader> <detail> <band height="125" splitType="Stretch"> <textField> <reportElement uuid="6176b8c5-efaf-4b92-9f6c-4e415e8da1d5" x="104" y="34" width="100" height="20"/> <textElement/> <textFieldExpression><![CDATA[$F{SO_NUM}]]></textFieldExpression> </textField> </band> </detail> <columnFooter> <band height="45" splitType="Stretch"/> </columnFooter> <pageFooter> <band height="54" splitType="Stretch"/> </pageFooter> <summary> <band height="42" splitType="Stretch"/> </summary> </jasperReport>

Most simple report - error

mathieu.ververken posted a topic in Products

Hello, I made a simple report in iReport, a list of all sales order. When I click on 'Preview' then it works fine. But when I open the report in Fishbowl, I get an error "Fishbowl Server Error: null". Does anyone know what is wrong with my code? Thx a lot for your help, Rgds, Mathieu <?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="report9" language="groovy" pageWidth="842" pageHeight="595" orientation="Landscape" columnWidth="802" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="ecc5ee2c-2dda-485e-9814-44735bbf7872"> <property name="ireport.zoom" value="1.0"/> <property name="ireport.x" value="0"/> <property name="ireport.y" value="0"/> <queryString language="SQL"> <![CDATA[sELECT SO."NUM" AS SO_NUM FROM "SO" SO]]> </queryString> <field name="SO_NUM" class="java.lang.String"/> <group name="SO_NUM"> <groupExpression><![CDATA[$F{SO_NUM}]]></groupExpression> </group> <background> <band splitType="Stretch"/> </background> <title> <band height="79" splitType="Stretch"/> </title> <pageHeader> <band height="35" splitType="Stretch"/> </pageHeader> <columnHeader> <band height="61" splitType="Stretch"> <staticText> <reportElement uuid="ba6dc6df-885b-45a2-b622-27cd45a9620f" x="104" y="15" width="100" height="20"/> <textElement/> <text><![CDATA[sO_NUM]]></text> </staticText> </band> </columnHeader> <detail> <band height="125" splitType="Stretch"> <textField> <reportElement uuid="6176b8c5-efaf-4b92-9f6c-4e415e8da1d5" x="104" y="34" width="100" height="20"/> <textElement/> <textFieldExpression><![CDATA[$F{SO_NUM}]]></textFieldExpression> </textField> </band> </detail> <columnFooter> <band height="45" splitType="Stretch"/> </columnFooter> <pageFooter> <band height="54" splitType="Stretch"/> </pageFooter> <summary> <band height="42" splitType="Stretch"/> </summary> </jasperReport>

Warning: Element reaches outside table detail contents & element must be placed at positive coordinates

mathieu.ververken posted a topic in Products

Good morning, I wanted to change an existing report in iReports and need to move 2 objects to another cell. By doing that I get 2 warnings with the following description: Element reaches outside table detail contents width: x = 281, width = 90, cell available widt = 100 (object: Text Field $F{SONUM})Element must be placed at positive coordinates (object: Text Field $F{DESCRIPTION})Is there anyone who can explain me what the second warning is about? Thx a lot for your help. Kind regards, Mathieu

Sign In

mathieu.ververken

Posts

Joined

Last visited

Content Type

Profiles

Forum

Events

Featured Visualizations

Knowledge Base

Documentation (PDF Downloads)

Blog

Documentation (Test Area)

Documentation

Dr. Jaspersoft Webinar Series

Downloads

Everything posted by mathieu.ververken

Add one extra field - error

Add one extra field - error

Add one extra field - error

Most simple report - error

Most simple report - error

Most simple report - error

Warning: Element reaches outside table detail contents & element must be placed at positive coordinates

Activity

Products

Explore