Sum amount at report footer is wrong

[Chlaris]

Dear all,

I have a report with the condition like below :

Detail Section :

- EmployeeName
- Salary
- Overtime

Report Footer:

= Sum([Salary])
= Sum([Overtime])

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.Overtime= 0 Then
Cancel = True
End If
End Sub

Sample data :

AAA 1,000 100
BBB 1,000 100
CCC 1,000 0

Output :

AAA 1,000 100
BBB 1,000 100
-------------
Total 3,000 200

The amount of 3,000 is wrong.
Is there any solution for this problem without change the sql statement for
record source ?
Thanks.

Chlaris

 

[Allen Browne]

That's right: suppressing the display of a value does not remove it from the
report total.

The best solution would be to correct the source query so it does not
contain the spurious/duplicated data.

There is a workaound involving a group footer with running sum. Essentially
you create a Group Footer on the field that defines the duplicate
(EmployeeName in your example.) In this new section, add a text box with
properties like this:
Control Source Salary
Running Sum Over Group
Format Currency (or something numeric anyway)
Name txtEmployeeSalaryRS
You can set the Visible property of the text box (or of the group footer) to
No if you don't wish to see it.

Now in the Report Footer section, add a text box with Control Source of:
=[txtEmployeeSalaryRS]

 

[Chlaris]

Actually I want to suppress the record with the overtime amount is zero.
I create a text box with Running Sum is Over All.
But the text box also sum the suppressed record.
Thanks.

That's right: suppressing the display of a value does not remove it from
the report total.

The best solution would be to correct the source query so it does not
contain the spurious/duplicated data.

There is a workaound involving a group footer with running sum.
Essentially you create a Group Footer on the field that defines the
duplicate (EmployeeName in your example.) In this new section, add a text
box with properties like this:
Control Source Salary
Running Sum Over Group
Format Currency (or something numeric anyway)
Name txtEmployeeSalaryRS
You can set the Visible property of the text box (or of the group footer)
to No if you don't wish to see it.

Now in the Report Footer section, add a text box with Control Source of:
=[txtEmployeeSalaryRS]

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Dear all,

I have a report with the condition like below :

Detail Section :

- EmployeeName
- Salary
- Overtime

Report Footer:

= Sum([Salary])
= Sum([Overtime])

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.Overtime= 0 Then
Cancel = True
End If
End Sub

Sample data :

AAA 1,000 100
BBB 1,000 100
CCC 1,000 0

Output :

AAA 1,000 100
BBB 1,000 100
-------------
Total 3,000 200

The amount of 3,000 is wrong.
Is there any solution for this problem without change the sql statement
for record source ?
Thanks.

 

[Allen Browne]

In that case, you did not place the text box in a suitable group footer.

Again, applying criteria in the query will be better.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Actually I want to suppress the record with the overtime amount is zero.
I create a text box with Running Sum is Over All.
But the text box also sum the suppressed record.
Thanks.

That's right: suppressing the display of a value does not remove it from
the report total.

The best solution would be to correct the source query so it does not
contain the spurious/duplicated data.

There is a workaound involving a group footer with running sum.
Essentially you create a Group Footer on the field that defines the
duplicate (EmployeeName in your example.) In this new section, add a text
box with properties like this:
Control Source Salary
Running Sum Over Group
Format Currency (or something numeric anyway)
Name txtEmployeeSalaryRS
You can set the Visible property of the text box (or of the group footer)
to No if you don't wish to see it.

Now in the Report Footer section, add a text box with Control Source of:
=[txtEmployeeSalaryRS]

Dear all,

I have a report with the condition like below :

Detail Section :

- EmployeeName
- Salary
- Overtime

Report Footer:

= Sum([Salary])
= Sum([Overtime])

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.Overtime= 0 Then
Cancel = True
End If
End Sub

Sample data :

AAA 1,000 100
BBB 1,000 100
CCC 1,000 0

Output :

AAA 1,000 100
BBB 1,000 100
-------------
Total 3,000 200

The amount of 3,000 is wrong.
Is there any solution for this problem without change the sql statement
for record source ?