Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSRS report-subtract row from one another Expand / Collapse
Author
Message
Posted Thursday, February 7, 2013 7:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 1, 2014 8:41 AM
Points: 101, Visits: 437
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
Post #1417053
Posted Tuesday, February 12, 2013 3:47 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, August 28, 2014 2:32 AM
Points: 405, Visits: 1,310
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

id Time Time_Stamp StateDiff State
4 24/01/2013 08:30 24/01/2013 08:40 0 Signed out
3 24/01/2013 08:30 24/01/2013 08:38 2 Idle
2 24/01/2013 08:30 24/01/2013 08:34 4 Busy
1 24/01/2013 08:30 24/01/2013 08:32 2 Available
Post #1418851
Posted Wednesday, February 13, 2013 7:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 1, 2014 8:41 AM
Points: 101, Visits: 437
thanks for your reply ...Will try it out!
Post #1419523
Posted Wednesday, February 13, 2013 8:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 1, 2014 8:41 AM
Points: 101, Visits: 437
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
Post #1419542
Posted Wednesday, February 13, 2013 8:11 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, August 28, 2014 2:32 AM
Points: 405, Visits: 1,310
You could try grouping by user and doing a compare of the previous user to the current user being looked at possibly
Post #1419546
Posted Wednesday, February 13, 2013 8:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 1, 2014 8:41 AM
Points: 101, Visits: 437
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!
Post #1419583
Posted Wednesday, February 13, 2013 8:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 1, 2014 8:41 AM
Points: 101, Visits: 437
Hi
I have data this way
User Nam Time Time Stamp Staus
Adam 13/02/2013 15:30 13/02/2013 15:45 3
Adam 13/02/2013 15:30 13/02/2013 15:40 2
Adam 13/02/2013 13:30 13/02/2013 13:37 3
Adam 13/02/2013 13:00 13/02/2013 13:09 2

Nancy 13/02/2013 15:00 13/02/2013 15:26 3
Nancy 13/02/2013 15:00 13/02/2013 15:26 2
Nancy 13/02/2013 15:00 13/02/2013 15:10 3
Nancy 13/02/2013 14:30 13/02/2013 14:57 2
Nancy 13/02/2013 14:30 13/02/2013 14:55 3


Probably I have data like this...

I wanted to show

Adam 13/02/2013 15:30 13/02/2013 15:45 3 (5mins)
Adam 13/02/2013 15:30 13/02/2013 15:40 2 10 mins
Nancy 13/02/2013 15:00 13/02/2013 15:26 3 0 mins
Nancy 13/02/2013 15:00 13/02/2013 15:26 2 26 mins

Hope you get my question...Thanks very much in advance..
Post #1419590
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse