SSRS report-subtract row from one another

• var05

  Hall of Fame

  Points: 3011

  More actions

  February 7, 2013 at 7:06 am

  #394132

  Hi All,

  I have a below report in SSRS

  Time Time_stamp_Status State Desc Time_Stamp diff

  2013-01-24 08:30 2013-01-24 08:32 1 Available 2 mins

  2013-01-24 08:30 2013-01-24 08:34 2 Busy 4 mins

  2013-01-24 08:30 2013-01-24 08:38 3 Idle 2 mins

  2013-01-24 08:30 2013-01-24 08:40 4 Signed out

  The field Time, TIme_stamp_status,state and desc are from the table directly. Have to calculate Time_stamp_diff in ssrs reports.. Kindly any one let me know how to subtract two rows in ssrs i.e

  when I subtract 2nd row (8:34) - 1st row(8:32) , I get 2 mins in Available state.

  Any help how this can be achieved in SSRS /SQL

  Thanks

• higgim

  SSCarpal Tunnel

  Points: 4090

  More actions

  February 12, 2013 at 3:47 am

  #1586915

  You can do this sort of. The problem comes from the fact that you can (as far as I can tell) only look at the previous record within a dataset.

  Using the data below:

  SELECT 1 AS id ,

  '2013-01-24 08:30' AS Time ,

  '2013-01-24 08:32' AS Time_Stamp ,

  'Available' AS State

  UNION

  SELECT 2 ,

  '2013-01-24 08:30' ,

  '2013-01-24 08:34' ,

  'Busy' AS State

  UNION

  SELECT 3 ,

  '2013-01-24 08:30' ,

  '2013-01-24 08:38' ,

  'Idle' AS State

  UNION

  SELECT 4 ,

  '2013-01-24 08:30' ,

  '2013-01-24 08:40' ,

  'Signed out' AS State

  To resolve this, create a matrix on the report as normal and sort in descending order (Signed out time comes first). Create an additional column with the following expression:

  =iif(isdate(previous(max(Fields!Time_Stamp.Value))),DateDiff("n",max(Fields!Time_Stamp.Value),previous(max(Fields!Time_Stamp.Value))),0)

  Results should be

  idTime Time_StampStateDiffState

  424/01/2013 08:3024/01/2013 08:400Signed out

  324/01/2013 08:3024/01/2013 08:382Idle

  224/01/2013 08:3024/01/2013 08:344Busy

  124/01/2013 08:3024/01/2013 08:322Available

• var05

  Hall of Fame

  Points: 3011

  More actions

  February 13, 2013 at 7:42 am

  #1587465

  thanks for your reply ...Will try it out!

• var05

  Hall of Fame

  Points: 3011

  More actions

  February 13, 2013 at 8:09 am

  #1587481

  Hi Ya,

  I just would like to know how would the union dataset would work for many users.

  Say for eg there are around 100 users... and in similar fashion every user state has to be calculated. The data available for every user will be of the same format only.

  Any Help?

  Thanks

• higgim

  SSCarpal Tunnel

  Points: 4090

  More actions

  February 13, 2013 at 8:11 am

  #1587485

  You could try grouping by user and doing a compare of the previous user to the current user being looked at possibly

• var05

  Hall of Fame

  Points: 3011

  More actions

  February 13, 2013 at 8:47 am

  #1587519

  Thanks for your reply.

  Yes Im trying to group by user...But the problem is that expression which calc., should be grouped to a specific user ...but here the top row user B is being subtracted by bottow row of User A.

  Hope you getting what Im trying to say...

  Any Idea?

  thanks!

• var05

  Hall of Fame

  Points: 3011

  More actions

  February 13, 2013 at 8:56 am

  #1587526

  Hi

  I have data this way

  User Nam Time Time StampStaus

  Adam13/02/2013 15:3013/02/2013 15:45 3

  Adam13/02/2013 15:3013/02/2013 15:40 2

  Adam13/02/2013 13:3013/02/2013 13:37 3

  Adam13/02/2013 13:0013/02/2013 13:09 2

  Nancy13/02/2013 15:0013/02/2013 15:26 3

  Nancy13/02/2013 15:0013/02/2013 15:26 2

  Nancy13/02/2013 15:0013/02/2013 15:10 3

  Nancy13/02/2013 14:3013/02/2013 14:57 2

  Nancy13/02/2013 14:3013/02/2013 14:55 3

  Probably I have data like this...

  I wanted to show

  Adam 13/02/2013 15:3013/02/2013 15:45 3 (5mins)

  Adam 13/02/2013 15:3013/02/2013 15:40 2 10 mins

  Nancy 13/02/2013 15:0013/02/2013 15:26 3 0 mins

  Nancy 13/02/2013 15:0013/02/2013 15:26 2 26 mins

  Hope you get my question...Thanks very much in advance..