Summarizing a formula using Next and Previous
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22868
Printed Date: 29 Apr 2024 at 3:43am
Topic: Summarizing a formula using Next and Previous
Posted By: jsh8286
Subject: Summarizing a formula using Next and Previous
Date 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. |
Replies:
Posted By: jsh8286
Date 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. |
Posted By: kevlray
Date 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)> |
Posted By: jsh8286
Date 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. |
Posted By: kevlray
Date 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. |
Posted By: jsh8286
Date 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. 
|