Crystal Reports – Prompt for value, if empty use database

The following steps describe how to create a Crystal Report which will prompt for a value and if nothing is entered select the alternative value from the database record.

This could be helpful if you have a report which selects values from the database but occasionally requires a manual override to enter an alternative value.

In this example we are using a finance payment form where the ‘cost code’ value from the database needs to be replaced with an alternative value.

 

Instructions

  1. Open the report using Crystal Reports (in this example we are using version 12 / 2008)
  2. Locate the value which will be replaced, e.g. “cost_code”.
  3. Hover over it and take note of the full database name, e.g. “vwGrantPayment.Cost_Code” Take note of this name for later on.
  4. Crystal-Override1
  5. Remove the field from the report by selecting it and pressing the ‘Delete’ button on the keyboard
  6. Create the prompt by right-clicking on ‘Parameter Fields’ in the ‘Field Explorer’ and selecting ‘New’
  7. Under ‘Name’ enter an appropriate name, e.g. “cost_code_override”. Take note of this name for later on.
  8. Under ‘Prompt Text’ enter an appropriate instruction for the end user (they will see this each time the report is ran), e.g. “Enter manual cost code:”
  9. Crystal-Override2
  10. Now we need to tell the report to use the manual value but if it’s empty use the database value, this is done using a formula field.
  11. Right-click on ‘Formula Fields’ under ‘Field Explorer’ and select ‘New’
  12. Enter an appropriate name, e.g. “select_cost_code”
  13. Crystal-Override3
  14. Enter the following formula based on the names noted earlier
  15. IF {?cost_code_override} = ""
    THEN {vwGrantPayment.Cost_Code}
    ELSE {?cost_code_override}
  16. Save and close the formula editor
  17. Insert the formula field into the report
  18. Refresh or run the report, you should be prompted for the override value.
  19. Crystal-Override4