Effective date

[ecsrs]

I am using CR 8.5. I have a table which has the following fields: rates, serviceID, fundingID, and effective date. I need to know how to pull the rate for the latest effective date. For example: $5.00 as of 1/1/2004 and $6.00 as of 1/1/2005. How would I tell my report to pull the $6.00 rate? There isn't an end date for the effective date.

 

[lbass]

You could either go to report->edit selection formula->GROUP and enter:

{table.effectivedate} = maximum({table.effectivedate},{table.group}) //assuming you want the most recent per group

Or, you may be able to create a SQL expression {%maxdate}:

(select max(AKA.`effectivedate`) from Table AKA where
AKA.`groupfield` = Table.`groupfield`)

You would substitute your table name for "Table" and your exact field names for "effectivedate" and "groupfield". Leave "AKA" as is, since it is an alias field name. Then go to edit selection formula ->RECORD and enter:

{table.effectivedate} = {%maxdate}

With the latter method you will be able to use inserted summaries for calculations. The first solution might require you to use running totals, depending upon the calculation.

-LB

 

[ecsrs]

Thanks, the grouping worked perfectly.