CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2011
    Posts
    3

    Arrow 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?

  2. #2
    Join Date
    Jul 2005
    Posts
    1,083

    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 ...

  3. #3
    Join Date
    Sep 2011
    Posts
    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.

  4. #4
    Join Date
    Jul 2005
    Posts
    1,083

    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 ...

  5. #5
    Join Date
    Sep 2011
    Posts
    3

    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??

  6. #6
    Join Date
    Jul 2005
    Posts
    1,083

    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
  •  





Click Here to Expand Forum to Full Width

Featured