Mulitple True Parameter won't pass all records

[kiddygirl]

I have a report that needs to display how much a person receives of different types of payments per case. I am using Crystal Reports XI. The report is set out as follows:

First group by employee and then grouped by Case and then there are 3 detail sections, first section has the date and amount from the Recovery table, the second section has the date and amount from the Save table and the 3rd detail has the date and amount from the Avoidance table. Each detail section is set to suppress if null

The Recovery, Save, and Avoidance are all different tables which are all 3 linked to the main Case Table by Case ID. What I am trying to do is have the end user select one date range and the report will display all Recovery, Save, and Avoidance that fall within that date range.

I created a parameter called Date Received that allows custom values and range values.

I then created the following formula in the Record Selection Formula Editor


{Financial_Security_Saves.Date} in {?Date Received} OR
{Financial_Recovery.Date} in {?Date Received} OR
{Financial_Security_Avoidance.Date} in {?Date Received}


What I am running into is that since each part of the parameter will have a true value, that only the records that are in the first parameter will populate the report. The report will not read the other parameter statements because the first one had a true value. In the above formula only cases with Saves will show on the report. If I rework the formula as follows:

{Financial_Recovery.Date} in {?Date Received} OR
{Financial_Security_Avoidance.Date} in {?Date Received} OR
{Financial_Security_Saves.Date} in {?Date Received}

Then only cases with Recoveries will populate the report. This tells me that since the first part of the parameter has true values, then it stops looking at the 2nd and 3rd part of the formula and that is it. I can't change the OR to AND because that would mean a case would need to have all 3 types of payments in order to populate the report.

Does anyone know how to write a parameter that will return all the results in the date range selected for each of the 3 types of amount? Not all cases will have all 3 types of payments. Some will have one type and some will have multiple types, but I need the report to display all types that are received within the date range selected by the end user.

 

[kray4660]

As in the case of most programming languages that I have dealt with. Once a part of a statement returns TRUE (as in the case of OR's) the rest of the statement is ignored. Not sure how to solve your issue.

 

[lbass]

You should be using left outer joins from the main table to each of the other tables. This will likely result in duplicated values in your detail section though.

Another approach would be to create a command using union queries where each query sets the specific date field to the date parameter. The three date fields would populate the same field, and you would have to add a text identifier to distinguish each date as Recovery, Save, or Avoidance. This would give you a cleaner dataset.

-LB