Display sum at Group Header.

[imranIBM]

In my report each row displays the maximum { Max(field) } value of a set of feilds using Max function. These rows are in each Group.
I want to display the sum of these row in the Group Header at the top of each group. The Sum() function doesnot allow me to do the sum of Max fields, so I am adding each row's value to a variable and displaying that variable in the Group Header.
Variable x;
x = x + Maxfield;
But it displays only the value of the first row.
Correct result:
Group 1 Total - 5 (Sum of 3 + 2)
Row#1....3
Row#2....2

My incorrect result:
Group 1 Total - 3 (First row value)
Row#1....3
Row#2....2
can anyone please help.

 

[DavidT_macktool]

You need to utilize evaluation time statements in your formulas:
there are several, including
BeforePrintingRecords
WhileReadingRecords
WhilePrintingRecords

Read here:
http://publib.boulder.ibm.com/infocenter/radhelp/v6r0m1/index.jsp?topic=/com.businessobjects.integration.eclipse.doc.crdesigner/reportdesigner/crconreportprocessingmodel.htm

My guess is that you will be using WhileReadingRecords in the detail band formula, which is the default when a formula uses database fields.
And use WhilePrintingRecords in the group header.

I also have the feeling you will have trouble resetting the counter to zero, should be done in the group footer.

I'll help if I can, do a little reading and see what you come up with...


I had a second thought: Keep a Sub total in the group footer - you can even suppress it. Set a variable to be used in the header to the group footer's value WhilePrintingRecords. It's all done with global variables.

 

[imranIBM]

Hello,

I tried the 2nd option to put the sum value in the footer and then assigning it to the header. May be I am missing out something can you please explain with codes in detail. Thank you very much.

Using BeforePrintingRecords and WhileReadingRecords gives error saying "This function cannot be used because it must be evaluated later".

Please help. Thanks.

Also if you can help me in this:
I am calculating the sum of the value for each group (x := x + field), and displaying at the bottom. But when the next group is displayed I want to change the variable (x := 0) value to zero. I cannot do this too. Please help me in this too.. Thank you very much for your support.

 

[DavidT_macktool]

You are not missing anything, there is not a simple solution. You are trying to go against the BASIC REPORT STRUCTURE of a summary report, specifically, you want the sum before the list of the records used to obtain the sum. For whatever reason this is required, it goes against the standards established for a summary report. You are going to have to be creative. I don't have the answer and to be honest, I don't have the time to figure out how to 'trick' a powerful report writing tool into doing something it is not intended to do.
You should look at the Cost/Benefit of the time you will spend trying to get the report to do this, when you could be done with the report by adding a subtotal field in the group footer.

Sorry there is no magic code on this one...

 

[atilak]

Here is a solution

hi imranIBM,

here is your solution...
and a very simple one...

i assume you have created grouping in the report... now add a summery field that u want to total at the end of group section ... u'll get a field...
now, at the group header section, insert one unbound number field. right click on it and choose formula editor..
remove the lines that come by default:

WhileReadingRecords; 
0.0

and drag the required summery field from the field tree (which will appear under report fields)
save and close the formula editor...
here u r go run ur project.. ur problem solved!

-atilak

 

[DavidT_macktool]

I'm not sure the above solution works, but I did test and find a solution also.
I used the report expert and created a grouped report with subtotals (summed fields) in the group footer. I suppressed the subtotals in the footers. Then I created a formula field, added the 'while printing records', and double clicked on the group footer subtotal field in the field explorer of the formula editor.

My formula looks like this:

WhileprintingRecords; 
Sum ({Partials.Material}, {Partials.Shop Number})

The sum code includes the "grouped on" field after the field being summed. That handles the zeroing out when a new group is started.
{Partials} = name of dataset
{Partials.Material} = the field I want subtotaled.
{Partials.Shop Number} = the field the group is based on.

Now drag your new formula field into the group header and format it the way you like.


Hope that helps...