sum() Function in relation to other influences like a date.

[adiboing]

Hello people,

I started with iReport 3.7.6 since a few days now. I have never used iReport before and now I would like to try something with sum().

But honestly I don't really know how to use the sum()-function.
I have my FIELD called "merit" and a FIELD called "logdate". So how is it possible to sum all entrys of "merit" from the DB between the "logdate" 01. Jan and the 30. Jan?

And when I sum all my "merit" and click on PREVIEW it just sums all merits of the single page but not form all pages together.

Thanks for your attention.
Adi

 

Post Edited by adiboing at 12/01/2010 14:26

[cbarlow3]

Hard to tell without seeing the jrxml what might be wrong, but I'll make a few guesses.  I've also pasted below the jrxml of a simple report that successfully selects records based on a date field being within a range specified by two parameters and maintains a sum variable that is good for the whole report and appears in the summary.  I've attached sample output when I use Mar 1, 2010 and May 31, 2010 as the date range.  Naturally, my SQL command has an artificial section in it where the data is hardcoded instead of coming from an actual data source, but that should allow you to open my jrxml in iReport and look at some of the techniques used so you can apply them to your situation.

Here are my some things I would check in yours:

1. First, I'm not sure what kind of problems the date is causing you.  I'm assuming that you are using the logDate field to determine whether or not to include a record on your report or not, but that you expect the SUM that appears to include every merit amount that appears on the report.  If that's true, then my example shows how to define a startDate and endDate parameter and how to use the WHERE clause in the SQL query to limit the result set to only those records where the logDate falls in that date range.  Nothing in iReport will know about the other records that are not selected, so they would obviously not be included in any sum.
2. As for the sum, I've defined a variable named meritAmountSum.  In the Properties pane, I set the "Calculation" to "Sum" and I've set the "Reset Type" to "Report".  I've set the "Variable Expression" to $F{MERITAMOUNT} (the name of my field I'm summing).  This means that the variable will add this field and will not reset to zero on a periodic basis (like every page, every group, etc.).  I then drag this variable from my Report Inspector into the Summary band (not Column Footer or Page Footer band).

Good luck!

Carl

Code:

<?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="SumTest" language="groovy" pageWidth="612" pageHeight="792" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20"><property name="ireport.zoom" value="1.0"/><property name="ireport.x" value="0"/><property name="ireport.y" value="0"/><style name="BandedTable" isDefault="false"><conditionalStyle><conditionExpression><![CDATA[$V{REPORT_COUNT}%2==1]]></conditionExpression><style isDefault="false" style="BandedTable" backcolor="#99FF99"/></conditionalStyle></style><parameter name="startDate" class="java.util.Date"/><parameter name="endDate" class="java.util.Date"/><queryString><![CDATA[WITH fakeData (logDate, meritAmount) AS(values        (date('2010-01-15'), 123.45),        (date('2010-01-31'), 513.20),        (date('2010-02-15'), 614.27),        (date('2010-02-28'), 314.15),        (date('2010-03-15'), 218.28),        (date('2010-03-31'), 543.21),        (date('2010-04-15'), 888.44),        (date('2010-04-30'), 105.16),        (date('2010-05-15'), 112.23),        (date('2010-05-31'), 305.05),        (date('2010-06-01'), 416.16),        (date('2010-06-30'), 135.79),        (date('2010-07-01'), 246.80),        (date('2010-07-31'), 202.05),        (date('2010-08-01'), 807.13),        (date('2010-08-31'), 443.58))SELECT  logDate, meritAmountFROM  fakeDataWHERE  logDate>=$P{startDate} AND logDate<=$P{endDate}ORDER BY  logDate]]></queryString><field name="LOGDATE" class="java.sql.Date"><fieldDescription><![CDATA[]]></fieldDescription></field><field name="MERITAMOUNT" class="java.math.BigDecimal"><fieldDescription><![CDATA[]]></fieldDescription></field><variable name="meritAmountSum" class="java.math.BigDecimal" calculation="Sum"><variableExpression><![CDATA[$F{MERITAMOUNT}]]></variableExpression></variable><background><band splitType="Stretch"/></background><title><band height="20" splitType="Stretch"><staticText><reportElement x="0" y="0" width="255" height="20"/><textElement><font size="14" isBold="true"/></textElement><text><![CDATA[Example of Sum and Date Selection]]></text></staticText><textField pattern="MM/dd/yyyy"><reportElement x="412" y="0" width="84" height="20"/><textElement textAlignment="Right"/><textFieldExpression class="java.util.Date"><![CDATA[$P{startDate}]]></textFieldExpression></textField><staticText><reportElement x="496" y="0" width="15" height="20"/><textElement/><text><![CDATA[ to]]></text></staticText><textField pattern="MM/dd/yyyy"><reportElement x="511" y="0" width="61" height="20"/><textElement/><textFieldExpression class="java.util.Date"><![CDATA[$P{endDate}]]></textFieldExpression></textField></band></title><columnHeader><band height="20" splitType="Stretch"><staticText><reportElement x="0" y="0" width="106" height="20"/><textElement><font size="12" isBold="true"/></textElement><text><![CDATA[Log Date]]></text></staticText><staticText><reportElement x="106" y="0" width="100" height="20"/><textElement textAlignment="Right"><font size="12" isBold="true"/></textElement><text><![CDATA[Merit Amount]]></text></staticText></band></columnHeader><detail><band height="20" splitType="Stretch"><textField pattern="¤ #,##0.00"><reportElement style="BandedTable" mode="Opaque" x="106" y="0" width="100" height="20"/><textElement textAlignment="Right"/><textFieldExpression class="java.math.BigDecimal"><![CDATA[$F{MERITAMOUNT}]]></textFieldExpression></textField><textField pattern="MMMMM dd, yyyy"><reportElement style="BandedTable" mode="Opaque" x="0" y="0" width="106" height="20"/><textElement textAlignment="Right"/><textFieldExpression class="java.util.Date"><![CDATA[$F{LOGDATE}]]></textFieldExpression></textField></band></detail><summary><band height="20" splitType="Stretch"><textField pattern="¤ #,##0.00"><reportElement x="106" y="0" width="100" height="20"/><textElement textAlignment="Right"/><textFieldExpression class="java.math.BigDecimal"><![CDATA[$V{meritAmountSum}]]></textFieldExpression></textField></band></summary></jasperReport>

[adiboing]

Hey thanks for your big reply :D

I tried to copy your code and insert it in a new report but it didn't preview the report. I was able to change the startDate and endDate and then it breaks.
 

About the sum part. I have changed my fieldnames and added some but they're not necessary to know. Now I have no merit anymore I just have a field called NID but still the LogDate just called NLOGDATE.
 

Here is a quick look on my report: (Sry for not embedding the first pic in my reply)

You told me, " I've set the "Reset Type" to "Report". "
When you look at my first picture you can see on the right the blue marked text "Evaluation Time", I think that's it . I took Report like you told me. But it is still not working like you'll see in my second picture.

1.
http://www.bilder-hochladen.net/files/9b2l-6f-png.html

 

and here my Preview, but as you can see I'm at page 3. And my total of the field NID is just around 11.000. So it counts all NIDs of page 3 but not of all pages.

2.
http://www.bilder-hochladen.net/files/9b2l-6g.png

 

Why is this still not working? Is the "Evaluation Time" with "Report" selected the wrong property? I can't find your "Reset Type".

greetz Adi

[cbarlow3]

I think you are confusing the display of the text field based on your variable with the definition of your variable itself.  From your screenshot, the display of your text field looks fine.  The "Reset Type" I refer to is visible as a property on the actual variable definition.  In the Report Inspector, expand the "Variables" section, click on $V{NID_SUM} and look at the properties pane.  I've attached a screenshot of what that looks like in the report I created yesterday (slightly different variable names, but the same idea).  I think the default for "Reset type" is "Report", but if you somehow accidentally changed it to "Page", that variable would get reset to 0 every time your report goes to a new page, which is what it sounds like is happening.

Carl

[adiboing]

Hey Carl,

thank you for your help. Yesterday I found it by myself. But still thanks for your screen ;)

I have a new question:
Is it possible to include the reports filename in the report?

Like:

Static Text	Variable or something
Report:	report_nr_1.jrxml

Just something to show the current filename in the report.

 

And is it possible when I include a IMAGE via the palette to make it a bit transparency? If yes, where is this property?

Thanks a lot to you ;D

peace Adi

 

 

Post Edited by adiboing at 12/03/2010 08:34

[cbarlow3]

I don't see a system-generated parameter or variable that includes the name of the .jrxml (or .jasper) file.  If you were running the report from within a Java program, the program would of course know the name of the file it's about to execute, and it could pass that as a parameter to the report, but I don't know how to make it happen just within iReport.

Carl

[evalentine]

I thought the Parameter 'JASPER_REPORT' contains the file name of the Report.

[cbarlow3]

Well, I never experimented with that parameter before, but I just now tried it with a report called VariableStyleTest.jrxml, and the $P{JASPER_REPORT} parameter displays as net.sf.jasperreports.engine.JasperReport@441b45, so it doesn't look like it's the NAME of the individual report, but rather just the version of JasperReports you're running.

Carl