Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Summarizing a formula using Next and Previous Post Reply Post New Topic
Author Message
jsh8286
Groupie
Groupie
Avatar

Joined: 09 Mar 2012
Online Status: Offline
Posts: 46
Quote jsh8286 Replybullet Topic: Summarizing a formula using Next and Previous
    Posted: 29 Apr 2020 at 11:40am
Have a report designed using the following formula to see if the descriptions for item records is the same:

if trim({'PRICE_SHEET_'.NEWDESC}) = trim(next({'PRICE_SHEET_'.NEWDESC}))

or trim({'PRICE_SHEET_'.NEWDESC}) = trim(previous({'PRICE_SHEET_'.NEWDESC})) then "YES - REPEAT!!" else "NO - OK"

LONG story short how do I summarize (group) all the 'YES's' and all the 'NO's' to group together so I can get a total of each?

Thank you in advance for any suggestions.
IP IP Logged
jsh8286
Groupie
Groupie
Avatar

Joined: 09 Mar 2012
Online Status: Offline
Posts: 46
Quote jsh8286 Replybullet Posted: 29 Apr 2020 at 3:08pm
I can not group the report as previously posted since the field I want to group on to do a count contains the "Next" and "Previous" functions.

If I do a formula to return a different record based on those fields it won't let me count or group it.

For example I used the following on the above formula posted earlier with an InStr formula:

if instr({@description repeats},"NO - OK")>0 then 1 else
if instr({@description repeats},"YES - REPEAT!!")>0 then -1 else 0

This returned exactly what I wanted on the report. Problem again is when I go to insert a sum(count) or try to group on this formula I can't since it references the original Next and Previous functions in the original formula.

Assume a workaround but at this point I am lost.

Thanks in advance for any postings.
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet Posted: 30 Apr 2020 at 4:30am
I am not sure there is.  Next and Previous are evaluated after the grouping.  So unless you can do something in a sub-report and pass back a result.  I do not know if there is another solution.  The newer versions of MS-SQL do have previous/next type functions.  So it may be possible to do it in a command if your data source is MS-SQL and it is new enough (unfortunately I do not recall what version has the functions)>

IP IP Logged
jsh8286
Groupie
Groupie
Avatar

Joined: 09 Mar 2012
Online Status: Offline
Posts: 46
Quote jsh8286 Replybullet Posted: 30 Apr 2020 at 5:28am
Issue "Resolved" - As frustation built I should have done this in the first place. I just exported the resulting printout to an Excel file and sorted it that way.

Thanks keviray for your input. Would still like to know if possible since I sure as you know what figure it out.

IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet Posted: 04 May 2020 at 4:30am
Unfortunately.  I do not have much time to devote to working on a solution (I am mostly here just to provide a quick solution if there is one).  But if you are using a newer version of MS-SQL (2012 or newer).  The functions are called Lead and Lag.
IP IP Logged
jsh8286
Groupie
Groupie
Avatar

Joined: 09 Mar 2012
Online Status: Offline
Posts: 46
Quote jsh8286 Replybullet Posted: 05 May 2020 at 3:51pm
Thanks again kevlray. I appreciate your input. Unfortunately no on the MS-SQL but make note of your functions anyway.

Thanks for your input.

Still searching for an answer.
IP IP Logged
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.016 seconds.