0

I have an SQL table that contains 4 columns

1) ID 2) EquipmentID 3) Date 4) Hours e.g. 10000

I have an SSRS report that displays the contents of this table

A from and to date parameter is used to select a date range of records

The table is grouped by equipment

In the equipment group I have added a row below with the following expression in the hours column

Last(Fields!Hours.Value) - First(Fields!Hours.Value) = "Total actual hours used" e.g. 10000 - 9500 = 500

This is does not return the correct "Total actual hours used"

The expression is incorrect

It should actaully be...

Last(Fields!Hours.Value) - PREVIOUS(First(Fields!Hours.Value)) = "Total actual hours used" e.g. 10000 - 9450 = 550

My issue is how can I get PREVIOUS(First(Fields!Hours.Value)?

By selecting the parameter date range I do not retrieve PREVIOUS(First(Fields!Hours.Value)

Thanks!

Adam
  • 23
  • 1
  • 4

2 Answers2

0

To get the expected result for your report expression i.e. Last(Fields!Hours.Value) - First(Fields!Hours.Value), an appropriate Order BY FieldName clause should be implemented in your T-Sql/SP.

Aftab Ansari
  • 926
  • 9
  • 17
0

Thanks for the tip Aftab

The SQL is already ordered by the date

Perhaps an example of the table would give you better understanding of why I need PREVIOUS(First(Fields!Hours.Value)

Date: 2012-09-01 Hours = 9500 (This is the total hours recorded at the end of the day and is used as the starting hours for the next day)

...

Date: 2012-09-30 Hours = 10000

Therefore if I use the expression...

Last(Fields!Hours.Value) - First(Fields!Hours.Value) = "Total actual hours used" e.g. 10000 - 9500 = 500

This returns the "Total actual hours used" of 500 for the from 2012-09-02 to 2012-09-30

I need the PREVIOUS(First(Fields!Hours.Value) to calculate the correct "Total actual hours used" from 2012-09-01 to 2012-09-30

Adam
  • 23
  • 1
  • 4