Search code examples
crystal-reportscrystal-reports-2008

Crystal Report Query change depending on the parameter

I have Crystal Report that is using the the standard query builder to return a financial report. The report consists of a header view and a detail view that returns the lines. See example below:

Report Default

The requirement we have now is to add an additional parameter, AMT that will filter the data based on the amounts. For example:

If @AMT = "Yes" then return all the data where the amount is greater than zero.

If @AMT = "No" then return all the data where the amount is less than zero.

How can you do this in Crystal Report? Can you add the parameter in the where clause?

Thanks in advance.

Solution

  • I reckon I'd do the following: 1) Create your parameter in the Parameter Fields section of the field explorer. The user will be able to specify "Negative" or "Positive" when prompted whether they want to display negative values or not. You can make this a dropdown and format it in whatever way is most meaningful to your needs.

    2) Use that value in a selection formula. Go to report-selection formulas-record and access the value the user selected with 

    {?ParameterName}

    Something like the following should get you what you need.

    if {?ParameterName} = 'Negative' then
{table_name.quantity_field_name} < 0
else
{table_name.quantity_field_name} > 0