Skip to Content

Create a formula field emulating SQL statement

I'm new to Crystal Reports, but understand how you can create basic Formula Fields to add to your report, and am also familiar with SQL. What I'd like to do on an existing report is add a new column called Passported, that populates Y/N dependant on a more complicated condition.

The current report lists each Claim Number and, based on the claim number, I could describe how we know whether the new column should be populated with Y or N, but I'm not sure how to do this in Crystal:

Each claim (from CLAIMS table) has one or more people on it (from CLAIM_ROLES), and these people each have various Finance Items (FQV_FINANCE_ITEMS) listed against them. If any one of these Finance Items is one from a particular list, then that claim is Passported = Y. Otherwise, Passported = N.

The SQL to return a list of all passported Claim Numbers would be:

SELECT

CLAIMS.CLA_REFNO

FROM

FQV_FINANCE_ITEMS,

CLAIM_ROLES,

CLAIMS

WHERE

( CLAIMS.CLA_REFNO=CLAIM_ROLES.CRO_CLA_REFNO )

AND ( SYSDATE BETWEEN CLAIM_ROLES.CRO_START_DATE AND NVL(CLAIM_ROLES.CRO_END_DATE,SYSDATE+1) )

AND ( CLAIM_ROLES.CRO_PAR_REFNO=FQV_FINANCE_ITEMS.PAR_REFNO )

AND

(

(

FQV_FINANCE_ITEMS.TYPE_CODE In ( 'ESA (IR)','IS','JSA(IB)','PCGC' )

AND

(

trunc(FQV_FINANCE_ITEMS.END_DATE) >= trunc(sysdate)

OR

trunc(FQV_FINANCE_ITEMS.END_DATE) Is Null

)

)

)

So I'm currently displaying CLAIMS.CLA_REFNO on the report, but want to add this new column (Passported), which will populate Y or N for each CLAIMS.CLA_REFNO, based on the criteria mentioned above. i.e. that the claim in question has a person attached to it who has one of the listed Finance Items currently active (i.e. is one of 'ESA (IR)','IS','JSA(IB)','PCGC').

Is there a way I can translate this into a Formula Field on my report? I have all the tables linked up correctly, I'm just not sure how to write what it is I want to do here.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Best Answer
    Posted on Dec 15, 2014 at 07:20 PM

    Hi Sami,

    Assuming you have a group on the 'Ref_No' field, try this please:

    1) Create a formula @List with this code:

    If {FQV_FINANCE_ITEMS.TYPE_CODE} In [ 'ESA (IR)','IS','JSA(IB)','PCGC' ] then 1 else 0

    2) Create another formula with this code and place this on the Group Header for the Ref_No:

    If Maximum({@List}, {CLAIMS.CLA_REFNO}) = 1 then 'Y'

    else 'N'

    -Abhilash

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.