Sum last line is excluded (when using a subreport variable) [resolved]

[maartenstachanov.com]

I have a subreport that returns a value (to a variable) and a group sum on that variable in a different variable:

 

When the last detail band report returns null it sums correctly

subreport v1 = 10

subreport v1 = null

subreport v1 = 20

subreport v1 = null

sum(subreport v1) = 30

 

but when I have the last row not null it will not add it to the sum.

subreport v1 = 10

subreport v1 = null

subreport v1 = null

subreport v1 = 20

I will get:

sum(subreport v1) = 10

 

How can I fix this?

---

How to solve:

1. Create a subtotal variable $V{subtotal} in the main report
   1. Set it to calculation type: System
   2. set the resettype to the correct value (report, group)
2. Add another return value to the report (select the subreport, in the properties add a new Return Value)
3. Assign the return value $V{v1} from the subreport to $V{subtotal}
4. Calculation type: "Sum"

 

 

 

Post Edited by maartenalbers at 02/17/2012 16:28

[maartenstachanov.com]

Ok after some debugging I've narrowed down the problem to the following:

 

after line 1 v1 is set, so if I print the variable at the same time we get

line 1 subreport: 10  v1:  0.0

line 2 subreport: null v1:10

line 3 subreport: 20 v1: null

line 4 subreport: 30 v1: 20

group sum: 30 (while it should be 60)

 

Post Edited by maartenalbers at 02/15/2012 09:56

[cbarlow3]

According to the iReport Ultimate Guide (section on returning parameters back from a Subreport):

"The value coming from the subreport is available only when the whole band containing the subreport is printed. If you need to
print this value using a textfield placed in the same band as your subreport, set the evaluation time of the textfield to Band."

Hope that helps.

 

Carl

Post Edited by cbarlow3 at 02/15/2012 15:19

Post Edited by cbarlow3 at 02/15/2012 15:19

[maartenstachanov.com]

Thank you for your reply, I did that and it shows up correctly in the band.

However if I calculate a subtotal over v1 doesn't (eventhough I set the subtotal calculation time to now/auto/band/report.)

Post Edited by maartenalbers at 02/15/2012 15:46

[maartenstachanov.com]

- create a subreport that returns a value.
- assign the value in the main report to variable A
- add a sum variable in the main report over A


Tried to solve the problem using different evaluation times (report, now, auto, band) neither made a difference.
(page, group, and column didn't make sense to me.)

Code:

database table `test` (id,v1):(1,10),(2,20),(3, NULL),(4,40),(4,50) 

[cbarlow3]

A long time ago I got some good information on this forum regarding returning values from a subreport, including some tips on how to in the main report do a SUM on those returned values.  I couldn't remember how to do it at all, and going back and reading my own notes is still a little confusing, but I think the last paragraph of my notes does have the answer you need, if you can just wade through it and make it make sense!  I've provided the entire set of my notes on returning values, but I think only the last paragraph applies to your specific need:

If you need a value from a subreport in your main report, do the following steps:

·         In the subreport, put the value you need to return into a variable.  In my example NegSharesPerPerson.jrxml, I have a variable called $V{SubTotNegBal} that is returned every time the subreport is run in the detail band of NegSharesByBranch.jrxml.

·         In the main report, create a variable with the same data type (and often the same name, just for simplicity) as the variable in the subreport.  In the Properties pane, make the Calculation be “System”.

·         Select the subreport, either in the Report Inspector or in the Design pane.  Scroll to the bottom of the Properties pane, and select “Return Values” … A dialog box comes up where you can “Add” a return parameter.  In the “Subreport Variable” you put the name of the variable in the subreport (unless it’s a system-supplied variable, it won’t appear in the dropdown—you’ll have to enter the name yourself, and you don’t need the $V{} around the name…it’s assumed to be a variable name.  If you’ve already created your variable in the main report that you want the result to go in, you should be able to find it in the dropdown for “Local Destination Variable”.  If you want the destination to just receive the actual value, choose “Nothing” from the “Calculation Type” dropdown.

·         Now you can drag that variable in the main report into an appropriate band on the report.  If it is the same band as the subreport, choose an “Evaluation Time” of “Band”.  Otherwise, “Now” or “Auto” will probably work fine.

·         If you want a variable that is keeping a sum of this returned variable per group or per report, do not set these variables up with the usual Calculation of “Sum” and variable expression of $V{SubTotNegBal}, because the variable that it’s totaling won’t be evaluated until all the subreports have run.  Instead, set the Calculation to “System”, leave the Variable Expression blank, still set Reset type to “Group” (and set Reset group) or “Report”, etc. depending on what kind of subtotal you’re keeping.  Then create additional returned values for the subreport, the same way you created the original returned value, but this time using “Sum” as the “Calculation Type” instead of “Nothing”.  My main report NegSharesByBranch.jrxml defines three variables that are all returned from the subreport:  SubTotNegBal, SubTotByBranch, and GrandTotal.  They all have a Calculation of “System”, and in the subreport mappings, it shows that they all come from the same subreport variable, but the first one has a Calculation Type(in the subreport return values dialog) of “Nothing” and  the other two have Calculation Type  of “Sum”.  Additionally, in the main properties for these variables, the reset type of these three variables is “None”, “Group”, and “Report” respectively.

[maartenstachanov.com]

Thanks: that looks promising. (Although it seems a bit peculiar behavior) I'll try it this afternoon.