Running Sum on Calculated Fields (1 Viewer)

[Alistair]

I have a report with calculated fields in the detail section of the report.
eg. Source: =([a]*[b%]) called [c]

I want to total these values in my report footer.

[Total]: =Sum([c])

I get a Jet Engine doesn't recognise query blah blah. If i delete the field the report runs OK.

Am I doing something obviously stupid here.

 

[Pat Hartman]

You need to repeat the calculation:
Source: =Sum([a]*[b%])

 

[Martin C]

I have a similar problem, in the details section I have:
Source: =[Rent]/7*[Days]
and this works, but when in the footer I put:
Source: =Sum([Rent]/7*[Days])
and then run the report I get an Enter Parameter Value request for Rent and Days. I guess it's because Rent and Days are calculated fields in the Detail section not fields in the base query. Any ideas on how to overcome?

 

[Rich@ITTC]

Hi Martin

You will need to define Rent and Days in terms of the original fields - so if Rent is made up from, say, NoOfRooms x RoomRate and Days is calculated from LeaveDate - ArrivalDate you would need:

=Sum((([NoOfRooms]*[RoomRate])/7)*([LeaveDate]-ArrivalDate]))

Obviously the field names will depend on the names you have given them.

HTH

Rich

 

[Alistair]

I managed to solve my problem by doing the following.

In the details section:

Calculated field [c]=[a]*[b%(variable)] is a number based on Iif statement that looks at info from another report field.

I created a new field [d] that performs a running sum of [c] for each value in the details section and then hid it so that it's invisible in the printed report.

In the report footer I created field [e]= [d]
and tada it references the last value in the running sum.