Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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,813 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
7 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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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 to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

580 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