crystal-reports

Crystal reports if/and/else


I have 3 parameters created in my report and am struggling to get the correct syntax for the following.

I need to return the record if:

I have this so far but when I add values for the other parameters nothing is returned.

If  ucase({?Program ID}) <>'' then  {CD_CVR_SUMMARY.PROGRAM_ID} = ucase({?Program ID})
and ( If ucase({?Subcon Category}) <>'' then  {CD_CVR_SUMMARY.SUBCON_CATEGORY_1} = ucase({?Subcon Category})  )
and ( If ucase({?Project Category}) <>'' then  {CD_CVR_SUMMARY.PROJECT_CATEGORY_2} = ucase({?Project Category})  )

Any help greatly received.


Solution

  • Assuming these parameters are set as Optional, you can use the following expression:

    ((not HasValue({?Program ID})) OR {CD_CVR_SUMMARY.PROGRAM_ID} = ucase({?Program ID})) 
    and 
    ((not HasValue({?Subcon Category})) OR {CD_CVR_SUMMARY.SUBCON_CATEGORY_1} = ucase({?Subcon Category})) 
    and 
    ((not HasValue({?Project Category})) OR {CD_CVR_SUMMARY.PROJECT_CATEGORY_2} = ucase({?Project Category})