I think, I'm following and understanding you better now. From your comments, I guess you can use PREVIOUS() to look back at not just the prior row (what I assumed) but group or other logical set. With that understanding in mind here is my answer. After, all the tables are joined together and the filter is applied for any given Division lets say Division 45 and cycle 17 you are looking at the privious row of that result set. Since, this filter with the previous() function is used in one script at a time when placed in the where clause in would simple be:
Where
Report level filter items
ws.StartDateTime IS NOT NULL and wa.RetiredFlag = 0 and wa.LoadStatusIndicator NOT IN (0, 4) and msi.P4UtilityTypeReadIndicator != 'F' and
Expression level filter
if ( {MeterSessionInput.P4UtilityTypeReadIndicator} = "L" and PREVIOUS ({MeterSessionInput.P4UtilityTypeReadIndicator}) = "E" and {Meter.MeterNumber} = PREVIOUS ({Meter.MeterNumber}) ) then ( if ({MeterSessionOutput.ReadCode} <> 0 and {MeterSessionOutput.ReadCode} <> 2 and {MeterSessionOutput.ReadCode} <> 99 and {MeterSessionOutput.BestResultIndicator} = 2 and (isNull({CustomDataField_MeterLink.CustomData}) = true or IsDemo({CustomDataField_MeterLink.CustomData}) = false) ) or (PREVIOUS({MeterSessionOutput.ReadCode}) <> 0 and PREVIOUS({MeterSessionOutput.ReadCode}) <> 2 and PREVIOUS({MeterSessionOutput.ReadCode}) <> 99 and PREVIOUS({MeterSessionOutput.BestResultIndicator}) = 2 and IsDemo(PREVIOUS({CustomDataField_MeterLink.CustomData})) = false) then true ) ;
So, the short & skinny answer is it simple looks at the previous row of the result set. I hope that, I'm clear.
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Posted: 30 Jan 2014 at 9:49am
No, "Previous()" in Crystal goes to the previous row in the data based on how the report is grouped and sorted. However, there is no "Previous" function in SQL. So, you have to somehow create a sub-query that will provide a single row that contains the data from that previous row. However, in order to do that, you have know how the data is ordered (grouping may or may not give you the order in SQL) to determine what to put in the where clause to get that information.
Joined: 13 Jun 2011
Online Status: Offline
Posts: 475
Posted: 31 Jan 2014 at 3:55am
to get previous you could add
ROW_NUMBER ( )
OVER ( order by somefield ) as link
function to the query then create same query but with
ROW_NUMBER ( )
OVER ( order by somefield )-1 as link
link them together using row_number and it should give you the previous.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum