Display sum in a group header

[vinays84]

I'd like to print the sum of values before they are displayed. For example suppose I had the query:

Code:

SELECT branch,day,sales_amt
FROM sales
WHERE branch='Toys' OR branch='Auto'

which returns :

Code:

[code] BRANCH DAY SALES_AMT Toys 1 200 Toys 2 300 Toys 3 400 Auto 1 100 Auto 2 200 Auto 3 300

I'd like to display

Code:

[code] Toys 900 1 200 2 300 3 400 Auto 600 1 100 2 200 3 300

 

If I group by branch, I can create a variable which sums the sales_amt and resets for the group. The problem is the variable will only total after the last day is reached, making it possible to put the total in the group footer, but not the group header. Is there any way to achieve this?

 

Note: I cannot change the query to include the sum. I have to do it in Jasper.

 

Thanks!

[svenn]

You could create a seporate subreport that runs your query and calculates the total for your group. You could then place the subreport before the details in your group.

 

I have not found a way to evaluate a total of any sort before the details are created in the same report.

[monodude]

Create a Varible with calculation type "Sum"

Make its reset type "Group" and set its reset group appropriately

 

Put the varible in the "Group Header" band and then set its Eval time to "Group" and its Eval Group to the appropriate group

[vinays84]

monodude - I can't do that for the reasons I listed in my initial post.

 

svenn - I'd like to avoid using subreports if possible, because I'd like one jrxml file to host the report in its entirety.

 

Thanks for the responses nonetheless.

[mdahlman]

vinays84,

 

monodude's answer is correct. The parts he put in bold are the important ones.

 

The problem in your logic was here: "The problem is the variable will only total after the last day is reached, making it possible to put the total in the group footer, but not the group header."

 

This statement can be corrected like this, "The variable will only total after the last day is reached, making it possible to put the total in the the group header only if the Evaluation Time of the field is set to Group (and the Evaluation Group is set appropriately)."

 

Leaving the Evaluation Time at its default value of "Now" will indeed cause the problems that you are running into.

 

-Matt

JasperSoft

[vinays84]

Yes, I see now. Thanks to you both.

[striperfishingmail.com]

I have a similar problem.  I can DISPLAY the sum in the Header if I follow the instructions given here.  In my case though, I would like to use the sum variable in an expression to determine whether or not to print a particular heading.  For instance, if the sum > 0, display a static message.  Is there a way to either use a Text Field in a "printWhenExpression" or force a variable to evaluate at the GROUP level just as the Text Field does?

[miltenb]

 I'd think that you allready answered your answer....

I created a second variable $test

$V{timereg_type_sum_qty} == 1 ? "one" : "not one"

 

It has the same settings as the field I am testing except for the calculation. that is set to 'Nothing'

[marius.strapenskas]

Ty, monodude, really helps

[TheMatthew]

thanks! this did the trick

[Jona Tai]

I'm using the method you give,but the result show the sum of last value.How to solve it?

Thanks First.