Dear All
How do I get the following to work and give me a total in the detail band/s or for each data row?
I have the following tables.
CREATE TABLE TranBuy(id integer,type integer,description varchar(50),amount double);CREATE TABLE TranSell(id integer,type integer,description varchar(50),amount double); INSERT INTO TranBuy VALUES (1,31,"Food",25.55),(2,32,"Clothes",26.55),(3,32,"Clothes",27.55),(4,33,"Drink",28.55),(5,34,"Pens",29.55); INSERT INTO TranSell VALUES (1,31,"Food",35.55),(2,32,"Clothes",36.55),(3,32,"Clothes",37.55),(4,33,"Drink",38.55),(5,34,"Pens",39.55);
and the following reports:
main add report
<?xml version="1.0" encoding="UTF-8"?><!-- Created with Jaspersoft Studio version last--><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="Add_A4" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="9ae22f39-7fe5-4959-9f13-7e32855a4d86"> <property name="com.jaspersoft.studio.data.defaultdataadapter" value="SmallData"/> <queryString language="SQL"> <![CDATA[SELECT DISTINCT TYPE FROM `transell`]]> </queryString> <field name="TYPE" class="java.lang.Integer"> <fieldDescription><![CDATA[]]></fieldDescription> </field> <variable name="buy_total" class="java.lang.Double" calculation="System"/> <variable name="sell_total" class="java.lang.Double" calculation="System"/> <variable name="buyselltotal" class="java.lang.Double" calculation="Sum"> <variableExpression><![CDATA[$V{buy_total} + $V{sell_total}]]></variableExpression> </variable> <background> <band splitType="Stretch"/> </background> <title> <band height="35" splitType="Stretch"> <property name="local_mesure_unitheight" value="pixel"/> <property name="com.jaspersoft.studio.unit.height" value="px"/> <staticText> <reportElement x="0" y="0" width="215" height="30" uuid="4959e8ea-fbeb-40af-aae6-577e7ccf85df"> <property name="local_mesure_unitheight" value="pixel"/> <property name="com.jaspersoft.studio.unit.height" value="px"/> </reportElement> <textElement> <font size="20"/> </textElement> <text><![CDATA["Test add in band"]]></text> </staticText> </band> </title> <pageHeader> <band height="20" splitType="Stretch"/> </pageHeader> <columnHeader> <band height="20" splitType="Stretch"/> </columnHeader> <detail> <band height="40" splitType="Stretch"> <property name="local_mesure_unitheight" value="pixel"/> <property name="com.jaspersoft.studio.unit.height" value="px"/> <subreport> <reportElement x="0" y="0" width="280" height="40" uuid="10ae46f4-dff7-4cfb-8e7b-7a43daae1f21"/> <subreportParameter name="type_id"> <subreportParameterExpression><![CDATA[$F{TYPE}]]></subreportParameterExpression> </subreportParameter> <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression> <returnValue subreportVariable="Buy_Total" toVariable="buy_total"/> <subreportExpression><![CDATA["Buy.jasper"]]></subreportExpression> </subreport> <subreport> <reportElement x="280" y="0" width="280" height="40" uuid="c7ded612-ffe6-4419-ae1e-71721ebe6b95"> <property name="local_mesure_unitheight" value="pixel"/> <property name="com.jaspersoft.studio.unit.height" value="px"/> <property name="local_mesure_unitwidth" value="pixel"/> <property name="com.jaspersoft.studio.unit.width" value="px"/> </reportElement> <subreportParameter name="type_id"> <subreportParameterExpression><![CDATA[$F{TYPE}]]></subreportParameterExpression> </subreportParameter> <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression> <returnValue subreportVariable="Sub_Sell_Total" toVariable="sell_total"/> <subreportExpression><![CDATA["Sell.jasper"]]></subreportExpression> </subreport> </band> <band height="30"> <property name="local_mesure_unitheight" value="pixel"/> <property name="com.jaspersoft.studio.unit.height" value="px"/> <staticText> <reportElement x="270" y="5" width="130" height="20" uuid="faf04390-ef1e-4c5a-bd8b-da694399392d"> <property name="local_mesure_unity" value="pixel"/> <property name="com.jaspersoft.studio.unit.y" value="px"/> </reportElement> <text><![CDATA[Buy and Sell Total]]></text> </staticText> <textField evaluationTime="Band" pattern="#,##0.00¤;#,##0.00"> <reportElement x="400" y="5" width="154" height="20" uuid="44438255-48b5-4a75-98a8-48a344e28528"> <property name="local_mesure_unitheight" value="pixel"/> <property name="com.jaspersoft.studio.unit.height" value="px"/> </reportElement> <textElement textAlignment="Right"/> <textFieldExpression><![CDATA[$V{buyselltotal}]]></textFieldExpression> </textField> </band> </detail> <columnFooter> <band height="20" splitType="Stretch"/> </columnFooter> <pageFooter> <band height="20" splitType="Stretch"/> </pageFooter> <summary> <band height="42" splitType="Stretch"/> </summary></jasperReport>
Buy subreport .
<?xml version="1.0" encoding="UTF-8"?><!-- Created with Jaspersoft Studio version last--><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="Buy" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="bfb7bbeb-6212-4e33-9ddf-9662bb22a2ab"> <property name="com.jaspersoft.studio.data.defaultdataadapter" value="SmallData"/> <parameter name="type_id" class="java.lang.Integer"/> <queryString language="SQL"> <![CDATA[SELECT type,description, amount FROM tranbuyWHERE type = $P{type_id}]]> </queryString> <field name="type" class="java.lang.Integer"> <fieldDescription><![CDATA[]]></fieldDescription> </field> <field name="description" class="java.lang.String"> <fieldDescription><![CDATA[]]></fieldDescription> </field> <field name="amount" class="java.lang.Double"> <fieldDescription><![CDATA[]]></fieldDescription> </field> <variable name="Buy_Total" class="java.lang.Double" calculation="Sum"> <variableExpression><![CDATA[$F{amount}]]></variableExpression> </variable> <background> <band splitType="Stretch"/> </background> <pageHeader> <band height="1" splitType="Stretch"> <property name="local_mesure_unitheight" value="pixel"/> <property name="com.jaspersoft.studio.unit.height" value="px"/> </band> </pageHeader> <detail> <band height="25" splitType="Stretch"> <property name="local_mesure_unitheight" value="pixel"/> <property name="com.jaspersoft.studio.unit.height" value="px"/> <textField> <reportElement x="0" y="0" width="70" height="20" uuid="70eb0478-fc79-49d9-8427-1e104a4c22f1"> <property name="local_mesure_unitheight" value="pixel"/> <property name="com.jaspersoft.studio.unit.height" value="px"/> </reportElement> <textFieldExpression><![CDATA[$F{type}]]></textFieldExpression> </textField> <textField> <reportElement x="70" y="0" width="70" height="20" uuid="18d91ad9-b897-4591-a804-15f109f1502e"> <property name="local_mesure_unity" value="pixel"/> <property name="com.jaspersoft.studio.unit.y" value="px"/> <property name="local_mesure_unitx" value="pixel"/> <property name="com.jaspersoft.studio.unit.x" value="px"/> <property name="local_mesure_unitheight" value="pixel"/> <property name="com.jaspersoft.studio.unit.height" value="px"/> </reportElement> <textFieldExpression><![CDATA[$F{description}]]></textFieldExpression> </textField> <textField pattern="#,##0.##¤;#,##0.##"> <reportElement x="140" y="0" width="70" height="20" uuid="af9d1821-ef42-47bf-b6e4-ceaedba1d74d"> <property name="local_mesure_unity" value="pixel"/> <property name="com.jaspersoft.studio.unit.y" value="px"/> <property name="local_mesure_unitx" value="pixel"/> <property name="com.jaspersoft.studio.unit.x" value="px"/> <property name="local_mesure_unitheight" value="pixel"/> <property name="com.jaspersoft.studio.unit.height" value="px"/> </reportElement> <textElement textAlignment="Right"/> <textFieldExpression><![CDATA[$F{amount}]]></textFieldExpression> </textField> </band> </detail> <pageFooter> <band height="25" splitType="Stretch"> <property name="local_mesure_unitheight" value="pixel"/> <property name="com.jaspersoft.studio.unit.height" value="px"/> <textField pattern="#,##0.##¤;#,##0.##"> <reportElement x="140" y="0" width="70" height="20" uuid="55c4a33a-25d3-45ff-8e6c-88e65cdd1284"> <property name="local_mesure_unitx" value="pixel"/> <property name="com.jaspersoft.studio.unit.x" value="px"/> <property name="local_mesure_unitheight" value="pixel"/> <property name="com.jaspersoft.studio.unit.height" value="px"/> </reportElement> <textElement textAlignment="Right"/> <textFieldExpression><![CDATA[$V{Buy_Total}]]></textFieldExpression> </textField> </band> </pageFooter></jasperReport>
Sell subreport
<?xml version="1.0" encoding="UTF-8"?><!-- Created with Jaspersoft Studio version last--><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="Sell" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="e23f79b9-ab42-4493-a012-bf97e699f232"> <property name="com.jaspersoft.studio.data.defaultdataadapter" value="SmallData"/> <parameter name="type_id" class="java.lang.Integer"/> <queryString language="SQL"> <![CDATA[SELECT type,description, amount FROM transellWHERE type = $P{type_id}]]> </queryString> <field name="type" class="java.lang.Integer"> <fieldDescription><![CDATA[]]></fieldDescription> </field> <field name="description" class="java.lang.String"> <fieldDescription><![CDATA[]]></fieldDescription> </field> <field name="amount" class="java.lang.Double"> <fieldDescription><![CDATA[]]></fieldDescription> </field> <variable name="Sub_Sell_Total" class="java.lang.Double" calculation="Sum"> <variableExpression><![CDATA[$F{amount}]]></variableExpression> </variable> <background> <band splitType="Stretch"/> </background> <pageHeader> <band height="1" splitType="Stretch"> <property name="local_mesure_unitheight" value="pixel"/> <property name="com.jaspersoft.studio.unit.height" value="px"/> </band> </pageHeader> <detail> <band height="25" splitType="Stretch"> <property name="local_mesure_unitheight" value="pixel"/> <property name="com.jaspersoft.studio.unit.height" value="px"/> <textField> <reportElement x="0" y="0" width="70" height="20" uuid="a5d659d7-878c-4abc-a563-62d4ac8cdb3f"> <property name="local_mesure_unitheight" value="pixel"/> <property name="com.jaspersoft.studio.unit.height" value="px"/> <property name="local_mesure_unitx" value="pixel"/> <property name="com.jaspersoft.studio.unit.x" value="px"/> </reportElement> <textFieldExpression><![CDATA[$F{type}]]></textFieldExpression> </textField> <textField> <reportElement x="70" y="0" width="70" height="20" uuid="9f6715c5-c153-4577-9c04-61057c6f4020"> <property name="local_mesure_unity" value="pixel"/> <property name="com.jaspersoft.studio.unit.y" value="px"/> <property name="local_mesure_unitx" value="pixel"/> <property name="com.jaspersoft.studio.unit.x" value="px"/> <property name="local_mesure_unitheight" value="pixel"/> <property name="com.jaspersoft.studio.unit.height" value="px"/> </reportElement> <textFieldExpression><![CDATA[$F{description}]]></textFieldExpression> </textField> <textField pattern="#,##0.##¤;#,##0.##"> <reportElement x="140" y="0" width="70" height="20" uuid="251f37cf-b539-4e7c-b869-3ebd7d8f6775"> <property name="local_mesure_unity" value="pixel"/> <property name="com.jaspersoft.studio.unit.y" value="px"/> <property name="local_mesure_unitx" value="pixel"/> <property name="com.jaspersoft.studio.unit.x" value="px"/> <property name="local_mesure_unitheight" value="pixel"/> <property name="com.jaspersoft.studio.unit.height" value="px"/> </reportElement> <textElement textAlignment="Right"/> <textFieldExpression><![CDATA[$F{amount}]]></textFieldExpression> </textField> </band> </detail> <pageFooter> <band height="27" splitType="Stretch"> <property name="local_mesure_unitheight" value="pixel"/> <property name="com.jaspersoft.studio.unit.height" value="px"/> <textField> <reportElement x="140" y="0" width="90" height="20" uuid="f1d7e6a9-13e1-4165-86e9-6cad0d4ca6a7"> <property name="local_mesure_unity" value="pixel"/> <property name="com.jaspersoft.studio.unit.y" value="px"/> <property name="local_mesure_unitheight" value="pixel"/> <property name="com.jaspersoft.studio.unit.height" value="px"/> <property name="local_mesure_unitx" value="pixel"/> <property name="com.jaspersoft.studio.unit.x" value="px"/> </reportElement> <textElement textAlignment="Right"/> <textFieldExpression><![CDATA[$V{Sub_Sell_Total}]]></textFieldExpression> </textField> </band> </pageFooter></jasperReport>
Get a total in a row from two sub-reports?
4 Answers:
Posted on April 29, 2015 at 8:45am
User cannot control report variable evaluation after the sub report call since variables are calculated when a data record is fetched, before record fields being laid out in the report band. To capture the return value from a sub report call through a variable, you will have to wait for the next record fetch.
The easiest way to solve this is to put the calculation directly in a report field to display the result. As long as the field is placed after the sub report, you should be able to capture the returned value in your calculation.
Posted on April 30, 2015 at 7:04am
Hi Tchen
Thanks for the reply.
I am still trying things to see if I can find a work around. There are actually quite a few issues
I am trying to address. Obviously the examples I give are simplified versions of the reports I am
trying to produce.
But the sum (or any calculation)using variables returned by a sub report should be possible in some
way with in a "final" detail band.
In my case I have two sub reports that each return a value and I want to add them together.
Calculating the result in a display field on the report also does not solve all the problems. As
this also causes issues when, you for instance, want use the result in a graph in the summary of
the main report. Sometimes we want a calculation without having to display the result immediately.
I have also tried making the sub reports into sub-sub reports and getting a total in the sub report
I have tried using the summary band and the detail band to calculate the total before sending it
back to the main report.
All of which does not work.
There really should be some way to do a calculation on a variable in a detail band without putting
the calculation directly in a report field to display the result?
Posted on April 30, 2015 at 12:36pm
The returned value from a sub report call is not available by the time your sum variable is evaluated. Besides calling a Java method (Scriptlet, etc) strategically placed in a report band location to do the summation work, what you can do is to set the evaluation apart from the record fetch and do it in a different detail band rendering cycles. This is what you can do using JR report structure:
1) create a sub report to get those sub report return values through parameters, Sum them up, and display it in the sub report;
2) create new detail band and place the new sub report in it.
Give a try and it should work.
Posted on September 5, 2016 at 1:09pm
Tchen, in this topic http://community.jaspersoft.com/questions/854116/using-return-value-subr... you said:
"I would suggest either do the calculation in the display field where evelaution time can be controled"
In this one you said:
"put the calculation directly in a report field to display the result. As long as the field is placed after the sub report, you should be able to capture the returned value in your calculation"
I believe you're talking about the same solution. But I still don't get it.
I tryied this: create a variable "mySum". After the subreport element but in the same detail band I put a text field with this expression:
$V{mySum}.add( $V{mySubreportReturnValue} )
The text field only shows null, even when I use Evaluation time "band".
