Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3839
  • Last Modified:

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

Hi There

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.


Date         Reading
                                                         \
  ---           256230       \   Inner List      |  Outer List
  ---           256250        |  Detail Group  |  Trip Group
  ---           256430        |                      |
  ---           256600        /                      /

Total               370

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

Paul
0
Sequest
Asked:
Sequest
  • 4
  • 3
1 Solution
 
simon_kirkCommented:
I think that error that your getting is related to the fact that the function fields referred to need the dataset name included.

E.g        (Fields!Reading.Value,"YourDataSetName")

0
 
SequestAuthor Commented:
Simon

I had lots of fun trying to get the Previous function to work.  The help refers to scope parameters as you mention but if I try to specify them I get compilation errors stating the wrong number of parameters for the Previous function.  This happens even if the expression is used in a textbox instead of a field.  

The only way I can get the Previous function to compile is with a single parameter refering to a field. Admittedly I have only tried this with a list.

Thanks for the comment anyway
0
 
simon_kirkCommented:
Does the compliation error regarding the number of parameters still occur if you put the field within seperate brackets?

Eg.

=iif(Previous((Fields!Reading.Value,"YourDataSetName"))=0, 0, (Fields!Reading.Value,"YourDataSetName")- Previous((Fields!Reading.Value,"YourDataSetName")
))
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
SequestAuthor Commented:
Well your suggestion resolves the problem of the wrong number of aguments but I still get the two original compilation errors.

I tried using your expression in a text box rather than a field and I get three errors stating that an ")" is expected but I triple-checked the parenthesises and it is syntactically correct.  Past the expression back into a field and I only get the original two errors!  I even simplified by removing the inline if and still the same results.

Very confusing !!  the danger of using a version 1.0 product I think.   The annoying thing is I can write it easily by adding a sub-query to the query for the dataset but that doesn't help me learn Reporting Services
0
 
simon_kirkCommented:
Can you not just use the fields without the function 'Previous' ?

i.e.
 
=iif((Fields!Reading.Value,"YourDataSetName"))=0, 0, (Fields!Reading.Value,"YourDataSetName")- (Fields!Reading.Value,"YourDataSetName")
)
0
 
SequestAuthor Commented:
The whole purpose is to get the difference between the value in the current row and the previous row and then get a total of all the differences.

To be honest I have given up on trying to find a solution to this in reporting services, especially as I wrote a SQL Query to do the job for me in about 10 minutes and it worked first time.

The 'Previous' function appears to be the answer but I can't get it to work.  Maybe the next release will fix it.
0
 
simon_kirkCommented:
There is SP1 out for Reporting Services, though I guess that you've got that already!

Glad you managed to get the result you wanted, regardless of how you got there ;o)

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now