INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Mulitple True Parameter won't pass all records

Mulitple True Parameter won't pass all records

(OP)
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.

RE: Mulitple True Parameter won't pass all records

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.

RE: Mulitple True Parameter won't pass all records

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close