Solved

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
8
3,622 Views
Last Modified: 2008-01-09
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
Comment
Question by:Sequest
  • 4
  • 3
8 Comments
 
LVL 14

Expert Comment

by:simon_kirk
ID: 12135348
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
 

Author Comment

by:Sequest
ID: 12136894
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
 
LVL 14

Expert Comment

by:simon_kirk
ID: 12141120
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:Sequest
ID: 12150937
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
 
LVL 14

Expert Comment

by:simon_kirk
ID: 12257780
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
 

Author Comment

by:Sequest
ID: 12274927
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
 
LVL 14

Accepted Solution

by:
simon_kirk earned 500 total points
ID: 12275978
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now