How to sum all values in a column in Jaspersoft iReport Designer

Question

I have below similar report in Jaspersoft iReport Designer, but not able to figure out how to sum all values present in vertical column "Doctor Payment" to get a total of "1601"? Length of this columns is variable (i.e. no of rows vary according to size of database & its update frequency ).

Is there any variable like $V{COLUMN_COUNT} (actually gives no of rows, here 5), which gives sum of all values in a column? If no, how to do sum?

Doctor ID   Doctor Payment
 A1             123
 B1             223
 C2             234
 D3             678
 D1             343
Total          1601

User · Answer

iReports Custom Fields for columns   sum  average  etc    Right-Click on Variables and click Create Variable Click on the new variable  a    Notice the properties on the right Rename the variable accordingly Change the Value Class Name to the correct Data Type  a  You can search by clicking the 3 dots Select the correct type of calculation Change the Expression  a     Click the little icon  b     Select the column you are looking to do the calculation for  c     Click finish Set Initial Value Expression to 0 Set the increment type to none Leave Incrementer Factory Class Name blank Set the Reset Type  usually report  Drag a new Text Field to stage  Usually in Last Page Footer  or Column Footer  Double Click the new Text Field Clear the expression    Text Field    Select the new variable Click finish Put the new text in a desirable position

User · Answer

It is quite easy to solve your task  You should create and use a new variable for summing values of the  Doctor Payment  column    In your case the variable can be declared like this    lt variable name  total  class  java lang Integer  calculation  Sum  gt       lt variableExpression gt  lt   CDATA  F payment    gt  lt  variableExpression gt   lt  variable gt     the Calculation type is Sum  the Reset type is Report  the Variable expression is  F payment   where  F payment  is the name of a field contains sum  Doctor Payment     The working example   CSV datasource    doctor id payment A1 123 B1 223 C2 234 D3 678 D1 343   The template    lt  xml version  1 0  encoding  UTF-8   gt   lt jasperReport     gt       lt queryString gt           lt   CDATA    gt       lt  queryString gt       lt field name  doctor id  class  java lang String   gt       lt field name  payment  class  java lang Integer   gt       lt variable name  total  class  java lang Integer  calculation  Sum  gt           lt variableExpression gt  lt   CDATA  F payment    gt  lt  variableExpression gt       lt  variable gt       lt columnHeader gt           lt band height  20  splitType  Stretch  gt               lt staticText gt                   lt reportElement x  0  y  0  width  100  height  20   gt                   lt box leftPadding  10   gt                   lt textElement textAlignment  Center  verticalAlignment  Middle  gt                       lt font size  10  isBold  true  isItalic  true   gt                   lt  textElement gt                   lt text gt  lt   CDATA Doctor ID   gt  lt  text gt               lt  staticText gt               lt staticText gt                   lt reportElement x  100  y  0  width  100  height  20   gt                   lt box leftPadding  10   gt                   lt textElement textAlignment  Center  verticalAlignment  Middle  gt                       lt font size  10  isBold  true  isItalic  true   gt                   lt  textElement gt                   lt text gt  lt   CDATA Doctor Payment   gt  lt  text gt               lt  staticText gt           lt  band gt       lt  columnHeader gt       lt detail gt           lt band height  20  splitType  Stretch  gt               lt textField gt                   lt reportElement x  0  y  0  width  100  height  20   gt                   lt box leftPadding  10   gt                   lt textElement  gt                   lt textFieldExpression gt  lt   CDATA  F doctor id    gt  lt  textFieldExpression gt               lt  textField gt               lt textField gt                   lt reportElement x  100  y  0  width  100  height  20   gt                   lt box leftPadding  10   gt                   lt textElement  gt                   lt textFieldExpression gt  lt   CDATA  F payment    gt  lt  textFieldExpression gt               lt  textField gt           lt  band gt       lt  detail gt       lt summary gt           lt band height  20  gt               lt staticText gt                   lt reportElement x  0  y  0  width  100  height  20   gt                   lt box leftPadding  10   gt                   lt textElement gt                       lt font isBold  true   gt                   lt  textElement gt                   lt text gt  lt   CDATA Total   gt  lt  text gt               lt  staticText gt               lt textField gt                   lt reportElement x  100  y  0  width  100  height  20   gt                   lt box leftPadding  10   gt                   lt textElement gt                       lt font isBold  true  isItalic  true   gt                   lt  textElement gt                   lt textFieldExpression gt  lt   CDATA  V total    gt  lt  textFieldExpression gt               lt  textField gt           lt  band gt       lt  summary gt   lt  jasperReport gt    The result will be       You can find a lot of info in the JasperReports Ultimate Guide

[jasper-reports] How to sum all values in a column in Jaspersoft iReport Designer?

The answer is

Examples related to jasper-reports

Examples related to ireport

Tags