| Author |
Message |
pjs65
Newbie
Joined: 28 Jan 2014
Location: United States
Online Status: Offline
Posts: 3
|
  
Topic: comparing lines of data
Posted: 28 Jan 2014 at 6:42am |
|
Hi, I am using crystal reports 9 and I'm pretty new to it. I am ultimately trying to create an output file(comma delimited) to charge an inactive customer fee.
Here would be an example of the data
#Cust# #Acct# #LstActv#
123 1 1/5/2005
123 2 10/13/2013
456 1 8/6/2012
I have grouped Cust#. I want to eliminate a customer from being feed if they have multiple accounts with at least one of them being active within a year of the current date. I am getting stuck when analyzing the multiple acct customer. Thank you in advance for any help.
|
 IP Logged |
|
|
|
NewCRuser
Newbie
Joined: 28 Jan 2014
Online Status: Offline
Posts: 7
|
Posted: 28 Jan 2014 at 5:00pm |
|
you may try to create two formula.
1. maximum(acct#)
2. currentdate - LstActv
insert maximum(acct#) in the detail section
in details section>select expert>suppress condition formula> if maximum(acct#)>0 and currentdate - LstActv >365 then true else false
hope this helps.
|
| IP Logged |
|
pjs65
Newbie
Joined: 28 Jan 2014
Location: United States
Online Status: Offline
Posts: 3
|
Posted: 29 Jan 2014 at 4:52am |
|
Hi NewCRuser,
Not quite, if we look at the example above, my objective is to eliminate cust# 123 because they have at least one account that is active. When I plug in those formulas Cust# 123's account 1 would get feed.
Thanks for the suggestion.
|
| IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 29 Jan 2014 at 4:58am |
|
having just been through this, suppressing the rows on the report and then exporting to csv, will probably result in all rows being displayed....v9 might be different than XI, but it's something to consider.
I always think that a stored proc will solve issues, and I realize that not everyone knows/is allowed to create stored procs. In this type of scenario, the stored proc would be useful in that you can delete from the data the active customers before Crystal ever 'sees' it, and then you don't need the suppression formula.
looking at the suppression formulas, I would think that you would want to add the grouping condition into them...and I don't understand the max(acct#) needing to be > 0. Just having max({table.LstActv}, {table.Cust}) being < 365 days ago should be sufficient...at least that is what I would have thought
HTH
|
| IP Logged |
|
pjs65
Newbie
Joined: 28 Jan 2014
Location: United States
Online Status: Offline
Posts: 3
|
Posted: 30 Jan 2014 at 2:38am |
|
Thanks Lockwelle, that did the trick. I was able to create the file via Access, but that was a little more cumbersome. I compared my output from CR9 to the access output and they were dead on.
Thanks everyone for the help.
|
| IP Logged |
|
|
|
