Exclude suppressed values from running total

[Tredoux]

We've got a report in CR 9 using Interbase db.

We want a top 100 debtors list but the balance is calculated by adding 4 columns together.

We do this by creating a formula field called @balance which adds the columns together and then sort them decending according to @balance.

But now we only want the top 100, so we suppress in details's section expert where 'recordnumber > 100'.

Our problem is that the running total does not ignore the suppressed records and adds all of them.

Any help would be great, thanks.

 

[lupins46]

In the running total definition you can use the same condition for adding to the running total instead of 'for every record'.

 

[Tredoux]

No, if you use 'recordnumber <= 100' in the running total you get the following error:

'A running total cannot refer to a print time formula'

 

[lbass]

Use a running total {#cntall} created in the running total expert to count only the displayed records by selecting a recurring field, count, evaluate based on a formula that represents the opposite of your suppression criteria, e.g., if you are suppressing all records that include {table.name} that start with "B" then:

not({table.name} startswith "B")

Reset never.

Then go to the section expert->details->suppess->x+2 and enter:

{#cntall} > 100

-LB

 

[Tredoux]

Thank you for the response lbass, but the problem is that my suppression criteria is based on the function 'recordnumber' (I only want to display and calculate for the top 100 debtors) and when you try to use that(or any formula or running total that refers to that) you get the error about print time formulas.

 

[lupins46]

So create your own running total by declaring a numbervar and adding 1 it in the detail section (using a formula field). Then you can suppress the row based on this value.

 

[lbass]

Are you saying that your calculation is including records over the first 100? I misunderstood. Create two formulas:

//{@accum} to be placed in the section where you have @balance}:
whileprintingrecords;
numbervar sumtopN;
numbervar counter := counter + 1;

if counter <= 100 then
sumtopN := sumtopN + {@balance});

//{@display} to be placed in the report footer:
whileprintingrecords;
numbervar sumtopN;

-LB