Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
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
Medium Priority
?
3,771 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 2000 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

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.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

721 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