Accessing Previous record from Dataset
I am building a report that currently has one dataset ordered by ID that gives these results
[code]ID Start Time End Time
1 1/17/2011 9:00 AM 1/17/2011 9:52 AM
2 1/17/2011 10:00 AM 1/17/2011 10:50 AM
[/code]
This is all fine, but I am wanting to calculate the time between the End of the previous ID and the start of the Current ID
So it needs to look like this:
[code]
ID Start Time End Time Time Between ID
10 1/17/2011 9:00 AM 1/17/2011 9:52 AM =ID10 StartTime - ID9 EndTime
11 1/17/2011 10:00 AM 1/17/2011 10:50 AM =ID11 StartTime - ID10 EndTime
[/code]
What is the best way to go about this? A second Dataset? Custom Code? I am farily new to SSRS. Any help is appreciated!
January 25th, 2011 9:27am
Hi,
Generally, we can access the previous record by using the
Previous function in Reporting Services.
For example, in order to calculate the time between the
EndTime of the previous record and the StartTime of the current record, please refer to the following steps:
1.
Add the 3 data fields (ID, StartTime, EndTime) to the cells in detail row in the table.
2.
Insert a new column.
3.
In the detail cell of the new column, type in the expression like
=IIf(IsNothing(Previous(Fields!EndTime.Value)),Nothing,DateDiff("n",Previous(Fields!EndTime.Value),
Fields!StartTime.Value))
Please correct the data field names and the interval argument based on your report.
For more information about the
Previous Function, please refer to:
http://msdn.microsoft.com/en-us/library/ms156372.aspx
About the DateDiff function, please refer to:
http://msdn.microsoft.com/en-us/library/b5xbyt6f(v=VS.90).aspx
If you have any more questions, please feel free to ask.
Thanks,
Tony Chain Tony Chain [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Free Windows Admin Tool Kit Click here and download it now
January 26th, 2011 12:46am