SSRS 2008 R2 My recordset contains a "Type" field and a "Date" field. When a record has "12/12/3000" in the date field and "C" in the type field I want to replace the date with the the date of the previous record that has "P" in the type field. For example: Original Data: Type Date P 3/4/2012 C 12/12/3000 A 12/12/3000 P 1/1/2011 C 12/12/3000 C 12/12/3000 C 1/17/2012 C 12/12/3000 Desired Output: Type Date P 3/4/2012 C 3/4/2012 A 12/12/3000 P 1/1/2011 C 1/1/2011 C 1/1/2011 C 1/17/2012 C 1/1/2011 I have attempted to do this in the SQL query but have not been able to so far. That is why I am looking for another solution. Thank you, Scott

Have you tried the Previous function in SSRS? Previous(Fields!FieldName.Value)

I considered it. However the value I want is not allways the previous but may be up to 13 records previously.

Hi Scott, Based on your description, I tried to use the Previous and Loopup functions but it is hard to get the correct result. So, I would suggest you still try to do this in SQL query. You can also post the question about T-SQL at http://social.technet.microsoft.com/Forums/en-US/transactsql/threads. We appriciate your understanding. If you have any question, please feel free to ask. Thanks, Tony ChainTony Chain [MSFT CSG] | Microsoft Community Support 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.

I agree with Tony. Very cumbersome to handle this in SSRS. I would tweak this in the SQL query (stored procedure) and then use the generated result-set in the report. You can use Select Case statement in the query and if date = 12/12/3000, call select top 1 date from table t1 where t1.primarykey < t2.primarykey and type = 'P' order by t1.primarykey desc where t2 is the alias for the outer table). If you have more doubts please post this in the T-SQL forum for better solution. Thanks

This is very simple to do in the T-SQL: select Type, Date from Results where Type <> 'C' and Date <> '30001212' UNION ALL SELECT R.Type, Prev.Date from Results R OUTER APPLY (select top (1) [Date] from Results R1 where R1.Type = 'P' and R1.ID < R.ID ORDER BY R1.ID DESC) Prev WHERE R.Type = 'C' and R.Date = '30001212' You need to have another field in your table that controls sorting order.For every expert, there is an equal and opposite expert. - Becker's Law My blog