Top N of a running total

[zflex]

I am writing a report to display a customers high credit amount in the previous 6 months. To do this I created a running total of all sales and payments made to thier account in the details section. Similar to what a bank statement looks like (adding sales and subtracting payments.) Looking down the running total column, the largest number in this list would be thier highest credit limit.

I would like to display this number in the group header. and hide the details sections.

Can this be done? That is, Can i get the topN of a running total? If so, any help would be appreciated. I've been working on this for days now and seem to find a solution.

 

[lbass]

You could do this in the group footer. Assuming you have a formula something like {@diff}:

{table.sales}-{table.payment}

...then your running total {#diffrt} would be the sum of {@diff}, evaluate for each record, reset on change of group (customer).

Then you would create a formula {@maxdiff}:

whileprintingrecords;
numbervar maxdiff;

if {#diffrt} > maxdiff then
maxdiff := {#diffrt} else
maxdiff := maxdiff;
maxdiff;

Place this in the group footer and suppress the details section. If you really must have the result in the group header, then I think you would have to save the report as a subreport linked on customer and place it in a group header section with all sections except the group footer suppressed.

-LB

 

[zflex]

Thanks for your help this far. I should mention that I am using 8.5. Is this supposed to work in this version? I ask because the running total is not reseting on change of customer and when I place it in the group footer it does not display the expected number. Do you have any more suggestions? All help is very much appreciated.

 

[zflex]

Actually, the running total is reseting on customer but the maxdiff formula is not reseting. Also if I move the maxdiff formula to the details section instead of the group footer the last detail line expected number (high credit amount) but when I move the formula to the footer the number is wrong.

 

[lbass]

Sorry, you need a reset formula in the customer group header:

whileprintingrecords;
numbervar maxdiff := 0;

-LB