-
September 27th, 2011, 05:32 AM
#1
Filtering data by Formula
Hi,
I have a CRM system called Goldmine with an SQL Database and I am trying to create a report using Crystal Reports XI which will filter off and show only the most recent activity for each opportunity created.
I have created a formula which uses the nthlargest function which add a yes to the record with the most recent date.
If {Conthist.ondate} = nthlargest(1,{Conthist.ondate}) then "Yes"
This has been created as a formula field called RecentActivity.
How do I apply this to the report?
-
September 27th, 2011, 10:53 AM
#2
Re: Filtering data by Formula
Welcome to Code Guru forums !
You could use the 'Record Selection Formula'
Code:
{@RecentActivity} = "Yes"
JG
... If your problem is fixed don't forget to mark your threads as resolved using the Thread Tools menu ...
-
September 28th, 2011, 05:08 AM
#3
Re: Filtering data by Formula
I have tried that same code in the 'Record Selection Formula' area but receive an error message
Error:
This formula cannot be used because it must be evaluated later.
-
September 28th, 2011, 11:43 AM
#4
Re: Filtering data by Formula
Try editing your 'RecentActivity' formula field, and write as the first line the next code
Code:
WhileReadingRecords;
...and if the problem persist, then try the next code instead
Code:
BeforeReadingRecords;
JG
... If your problem is fixed don't forget to mark your threads as resolved using the Thread Tools menu ...
-
October 5th, 2011, 11:01 AM
#5
Re: Filtering data by Formula
I tried both WhileReadingRecords, BeforeReadingRecords in front of the code in the "RecentActivity" formula field like this
Code:
WhileReadingRecords;
If {Conthist.ondate} = nthlargest(1,{Conthist.ondate}) then Yes
else no
The same error again appeared
Error:
This formula cannot be used because it must be evaluated later.
I also tried adding them to the beginning of the record selection code like this
Code:
WhileReadingRecords;
{CONTHIST.SRECTYPE} <> "O" and
{CONTHIST.LOPRECID} = {?Pm-OPMGR.OPID} and
{@RecentActivity} = yes
Again the same error appears. I also tried the WhilePrintingRecords and
EvaluateAfter({@RecentActivity}) but again the same error appears. Any other suggestions??
-
October 5th, 2011, 11:12 AM
#6
Re: Filtering data by Formula
Could You post the rpt ? (save it with data)
Or... say in which report's section is placed the 'RecentActivity' formula
Last edited by jggtz; October 5th, 2011 at 11:51 PM.
JG
... If your problem is fixed don't forget to mark your threads as resolved using the Thread Tools menu ...
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|