Same Date period one year in the past.

[deeya]

CR v7
Oracle 9 DB

Hi All,

I have a date range, {@Start Date} and {@End Date}, I need to create a formula that will calculate the same Start and end period for the previous year (factoring in leap years) for my subreport. Its so the user can see current and previous years data for the same period.

Many Thanks

 

[lbass]

What is the content of your formulas? Are they conversions of two discrete date parameters? Is the dateadd function available to you in v.7?

-LB

 

[deeya]

{@Start Date} = Minimum(ReportPeriod) and
{@End Date} = Maximum(ReportPeriod) where ReportPeriod is a parameter.

No DateAdd is not available.

Thanks

 

[lbass]

Use your formulas to link to the subreport, and then change the resulting subreport report selection formula to:

datevar start := if {?pm-@Start Date} = date(year({?pm-@Start Date}), 2, 29) then
start := date(year({?pm-@Start Date}),3,1) else
start := {?pm-@Start Date};
datevar end : if {?pm-@End Date} = date(year({?pm-@End Date}), 2, 28) or
{?pm-@End Date} = date(year({?pm-@End Date}), 2, 29) then
end := date(year({?pm-@End Date}),3,1)-1 else
end := {?pm-@End Date};

{table.date} >= date(year(start)-1, month(start), day(start)) and
{table.date} <= date(year(end)-1, month(end), day(end))

This assumes variables are available and it's kludgy, but should work. Otherwise, is dateserial available in 7.0?

-LB

 

[deeya]

Brilliant LB works a treat!!

Thanks very much.