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,743 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

627 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