Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Stored Procedure Post Reply Post New Topic
<< Prev Page  of 3
Author Message
Tupacmoche
Groupie
Groupie
Avatar

Joined: 04 Apr 2007
Online Status: Offline
Posts: 52
Quote Tupacmoche Replybullet Posted: 29 Jan 2014 at 9:22am
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.
  
Rob
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet 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.

-Dell

-Dell
IP IP Logged
kostya1122
Senior Member
Senior Member
Avatar

Joined: 13 Jun 2011
Online Status: Offline
Posts: 475
Quote kostya1122 Replybullet 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.
IP IP Logged
<< Prev Page  of 3
Post Reply Post New Topic
Printable version Printable version

Forum Jump
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



This page was generated in 0.031 seconds.