Financial Year Date Formula Required 1

[beebass]

Hi there

I'm pretty new to Crystal reports (currently using version 9). I have been asked to provide a report thats produces sales figures from various contracts over the company's financial year (November 1st to October 31st). So far I have been using a between filter in the selction expert on the sales invoice date. However, does anybody happen to know if I can create a formula field on the sales invoice date that will pick up the correct data in a financial year and automatically pick up the correct data when the next financial year begins so it may be possible to schedule the report without having to change the filters at the beginning of every new financial year.

Thanks

BB

 

[lbass]

You could use:

(
{%month} >= 11 and
{table.date} in date(year(currentdate), 11,1) to date(year(currentdate)+1,10,31)
) or
(
{%month} < 11 and
{table.date} in date(year(currentdate)-1,11,1) to date(year(currentdate),10,31)
)

...where {%month} is a SQL expression like:

{fn MONTH({fn NOW()})}

...however, you can create a month(currentdate) SQL expression with your particular datasource/connectivity. This will allow the formula to pass to the SQL statement. Otherwise you could just use:

month(currentdate)

...instead of {%month}.

-LB