Formula for date range from fields in two tables (Crystal Reports)

0
votes

I am trying to create a formula which would allow to pull the records from fields in two different tables and print the date in the report.

1.) I have a report with beginning and ending date parameters.

2.) I also have two formulas {@MinDate} and {@MaxDate} which print the minimum and maximum dates:

minimum{?Event date}

maximum{?Event date}

I think I’m good at this point.

3.) I am trying to create a formula which would allow to pull the records from fields in two different tables and print the date in the report.

The closest I have come is:

({EVENT.DATE}>= {@MinDate} and{EVENT.DATE}<= {@MaxDate})OR ({CALENDAR.DATE}>= {@MinDate} and{CALENDAR.DATE}<= {@MaxDate})

This formula is giving me a Boolean response, and I think I understand why it is giving me a Boolean response, but I don’t know what to do instead. I am looking for the report to show dates within the original parameters.

I’m am a beginner at Crystal Reports; sorry if this is a novice question.

2
crystal-reportsformula

2 Answers

0
votes

What you have looks mostly fine, you just need to apply the comparison logic in the record selection formula instead of in a regular formula. I might also suggest that you create two separate date parameters instead of just using one that has multi-select or ranges.

To get to the record selection formula, Click "Report" in the toolbar → "Selection Formulas" → "Record". From there enter your logic:

({EVENT.DATE} >= {@MinDate} and {EVENT.DATE} <= {@MaxDate})
OR ({CALENDAR.DATE} >= {@MinDate} and {CALENDAR.DATE} <= {@MaxDate})

Alternatively, if you go with two separate parameters, you can do away with the Min/Max formulas:

{EVENT.DATE} in {?Start Date} to {?End Date}
OR {CALENDAR.DATE} in {?Start Date} to {?End Date}
0
votes

I feel your are complicating the report with your approach...

To pick data from two tables... is crystal reports preparing two queries so that one filtering criteria will be applied on one table and other on 2nd table? if yes then you have scope of returning the correct results.. else I am afraid you may end up getting wrong results.

Though your question is not about best approach... I feel you are going in wrong direction hence suggesting a better approach that I feel will work.

  1. Link both the tables in report and elimate use of date field from two tables in record selection formula.
  2. Secondly instead of one date parameter create two parameters Start Date and End Date and eliminate use of Min and Max functions.

  3. Since you have already linked tables now use date fields from only one table and write the formula in Record Selection Formula.

    {EVENT.DATE} >= {?Start Date} and {EVENT.DATE} <={?End Date}