Reporting Services - How to create a calculated field which is the difference between a value in the current row and a previous row and totol the differences
Posted on 2004-09-22
I have a report I am developing for a customer and part of it contains a list of ODO Meter readings from a truck and the customer wants a total of the mileage covered by the truck in each round trip it does, i.e. a total of the differences in the ODO meter readings. So for each trip you might have a selection of reading like the example below.
--- 256230 \ Inner List | Outer List
--- 256250 | Detail Group | Trip Group
--- 256430 | |
--- 256600 / /
Using the Previous function I can create a hidden textbox in the inner group which holds the difference between each row.
=iif(Previous(Fields!Reading.Value)=0, 0, Fields!Reading.Value - Previous(Fields!Reading.Value))
This works fine but you cannot total on a report item so I tried to create a calculated field with the same expression but it won't compile. I get the following errors:
sort expression for the field 'blah' uses the aggregate function previous
The field expression for the dataset 'blah' has a scope parameter that is not valid for an aggregate function
Obviously I can write my query to return a column with the value already calculated but I would rather know if it can be done in Reporting Services
Thanks in advance