Selecting Relevant Data from a Date Range 1

[khan82]

Hello..i really need your help...i am pulling my hair out here.

I am trying to build a crystal report that prints a rent schedule at an effective date. The idea is to get the charges to print which were effective as of the effective date parameter.

I created a parameter for an effective date and passed that through the select expert as:

{PML_LEASE__LEASE_RECURRING_CHG_LOG.Effective_Date} <= {?EffectiveDate}

Problem is that charge effective dates are multiple dates i.e.
01/01/12 $500.00
04/01/12 $600.00
04/01/13 $800.00

i need a formula to select the date that is less than or equal to the effective date(parameter) and the charge has not expired as well but is either current or future.

what would be the method or formula to show effective charge as of 06/01/12?

Thank you so much!!!

 

[lbass]

After selecting dates <= the parameter date, go to report->selection formula->GROUP and enter:

{table.chargeeffectivedate} = maximum({table.chargeeffectivedate},{table.groupfield})

This will return the most recent date <= the parameter date.

If you need to do calculations across groups, use running totals as non-group displayed records are still IN the report, and would contribute to the more usual inserted summaries.

-LB

 

[khan82]

error:
This field cannot be summarized.

maximum(GroupName ({PML_LEASE__LEASE_RECURRING_CHG_LOG.Effective_Date}, "weekly")) <= {?EffectiveDate}

 

[khan82]

Please see attached screen shot.

 

[lbass]

The record selection formula (report->selection formula->record) should be:

{PML_LEASE__LEASE_RECURRING_CHG_LOG.Effective_Date}<={?EffectiveDate}

The group selection formula (report->selection formula->GROUP) should be:

{PML_LEASE__LEASE_RECURRING_CHG_LOG.Effective_Date}=maximum({PML_LEASE__LEASE_RECURRING_CHG_LOG.Effective_Date},{table.account})

...where {table.account} is some field you are grouping (not the date) and within which you are trying to find the most recent effective date.

-LB

 

[khan82]

its beautiful when a report comes together....thank you so much!! you are amazing!!

 

[khan82]

Hi LB

Is it possible to place a select statements to only show records in a sub-report that has the following criteria:

I want the sub-report to only display leases that are over a year.

- First to calculate the difference between two records i.e. Lease start date and lease end date.

- Show record if the difference > 365 days.

Thanks
AK

 

[lbass]

How does this relate to the current thread? If it doesn't, please start a new thread. You will also need to explain where the subreport would be located in the main report, how the main report is grouped, the fields you are working with, etc.

-LB