none
SSRS 2008 Comparing current record with previous record calculated field?

    General discussion

  • Hi,

    I'm fairly new to SSRS and I'm trying to figure out a way to only display repeat records with a certain condition.

    I'm currently grouping on repeat of ID so I am seeing only my repeated IDs.  Now I need to display the IDs that have less than 73 hours between each other (including the first record).  So I created the expression below in the Hours column:

    =iif((previous(Fields!ID.Value)=Fields!ID.Value),iif(Datediff(DateInterval.Hour, (previous(Fields!Left.Value)), Fields!Arrival.Value)>72, "New ID", Datediff(DateInterval.Hour, (previous(Fields!Left.Value)), Fields!Arrival.Value)), "New ID")

    I only want to display those that are less than 73 hours but also the record that has the "New ID" as this is the first instance of the series.  Does this make sense?  The current output is as below:

    Name

    ID

    Acct

    Arrival

    Left

    Hours

    Test

    7222

    1

    8/5/2013 11:22

    8/5/2013 14:45

    New ID

    Test

    7222

    2

    8/9/2013 12:15

    8/9/2013 15:10

    New ID

    Test

    7222

    3

    8/10/2013 09:12

    8/10/2013 10:12

    18

    Test1

    9793

    4

    9/26/2013 09:25

    9/26/2013 10:16

    New ID

    Test1

    9793

    5

    9/29/2013 11:54

    9/29/2013 10:17

    New ID

    Test2

    9797

    6

    6/14/2013 09:26

    6/14/2013 10:16

    New ID

    Test2

    9797

    7

    6/15/2013 10:35

    6/15/2013 10:41

    24

    Test3

    9983

    8

    5/16/2013 09:21

    5/16/2013 09:38

    New ID

    Test3

    9983

    9

    5/16/2013 10:51

    5/16/2013 10:55

    1

    Test4

    10244

    10

    6/14/2013 12:33

    6/14/2013 15:10

    New ID

    Test4

    10244

    11

    6/16/2013 13:54

    6/16/2013 22:17

    47

    Test5

    11111

    12

    5/2/2013 13:53

    5/2/2013 15:53

    NEW ID

    Test5

    11111

    13

    5/12/2013 06:41

    5/12/2013 09:41

    NEW ID

    Test5

    11111

    14

    5/23/2013 20:46

    5/25/2013 12:16

    NEW ID

    Test5

    11111

    15

    5/26/2013 00:38

    5/26/2013 03:38

    12

    Test5

    11111

    16

    5/27/2013 03:38

    5/27/2013 04:38

    24

    The desired output should be:

    Name

    ID

    Acct

    Arrival

    Left

    Hours

    Test

    7222

    2

    8/9/2013 12:15

    8/9/2013 15:10

    New ID

    Test

    7222

    3

    8/10/2013 09:12

    8/10/2013 10:12

    18

    Test2

    9797

    6

    6/14/2013 09:26

    6/14/2013 10:16

    New ID

    Test2

    9797

    7

    6/15/2013 10:35

    6/15/2013 10:41

    24

    Test3

    9983

    8

    5/16/2013 09:21

    5/16/2013 09:38

    New ID

    Test3

    9983

    9

    5/16/2013 10:51

    5/16/2013 10:55

    1

    Test4

    10244

    10

    6/14/2013 12:33

    6/14/2013 15:10

    New ID

    Test4

    10244

    11

    6/16/2013 13:54

    6/16/2013 22:17

    47

    Test5

    11111

    14

    5/23/2013 20:46

    5/25/2013 12:16

    NEW ID

    Test5

    11111

    15

    5/26/2013 00:38

    5/26/2013 03:38

    12

    Test5

    11111

    16

    5/27/2013 03:38

    5/27/2013 04:38

    24

    Any help is greatly appreciated!

    Michelle

    PS. The data has been altered for the purposes of writing this question.  I apologize if I mistyped any number(s).

    Wednesday, September 04, 2013 7:32 PM