Group by Fiscal Year or calendar year...

[omacron]

Need to write a report that well group off of a fiscal year or the calendar year prompted by the user. Also prompted by the user it must be able to show all fiscal or calendar years ever (yes I know this is could be a really long report to run).

Doing the Calendar is np, but the fiscal is the problem. Found a way to calc the fiscal year but it uses the the current date so all previous fiscal years get ignored. The fiscal year is Sept 1 to Aug 31

thanks

 

[synapsevampire]

There are several approaches to this, including this whitepaper emulating data warehouse approaches:

faq767-4532

In either case create a string parameter with the choices Calendar and Fiscal, and another numeric parameter and prepopulate it with a zero, and in the description place the text All Years and select to only display the description, then use something akin to the following in the Report->Edit Selection Formula->Record

If {?MyYearTypeParm} = "Calendar" then
(
{?MyNumericParm} = 0
or
year({table.date}) = {?MyNumericParm}
)
else
(
{?MyNumericParm} = 0
or
{Table.date}>=cdate({?MyNumericParm}-1,9,1)
and
{Table.date}<=cdate({?MyNumericParm},8,31)
)

-k

 

[omacron]

hmmm, the problem with doing that I need to group by fiscal year. Putting that forumal in Report->Edit Selection Formula->Record would give me all the data within the fiscal year but not group it by fiscal year. This is what I am trying to do:

Year: 2004
Cross tab: Sales
Month $$$
Total $$$$$

Year: 2003
Cross tab: Sales
Month $$$
Total $$$$$

So this is what I figured I would do:

@FiscalStartDate
date(year(currentdate)-1,9,1)

@FiscalEndDate
date(year(currentdate),8,31)

@Groupforumal
{PSSALEH.TDATE} >= {@FiscalStartDate}
and
{PSSALEH.TDATE} <= {@FiscalEndDate}


The problem with doing this is that it groups into a true and false. So it would give the fiscal year for this year into true, then group all other years into false.

 

[lbass]

Here's a formula for grouping by year (calendar or fiscal) which involves creating two parameters--a string parameter {?yeartype} and a number parameter {?year} which allows multiple entries.

numbervar i := ubound({?year});
numbervar counter;
stringvar yr;

for counter := 1 to i do(
if {?yeartype} = "Fiscal" then
(if {Orders.Order Date} in date(({?year}[counter])-1,09,01) to date({?year}[counter],08,31) then
yr := "Fiscal Year "+totext({?year}[counter],0,"") else "") else
if {?yeartype} = "Calendar" then
(if {Orders.Order Date} in date({?year}[counter],01,01) to date({?year}[counter],12,31) then
yr := "Calendar Year "+totext({?year}[counter],0,"")) else "");
yr

For your record selection formula, try:

if {?yeartype} = "Fiscal" then
{Orders.Order Date} >= date(minimum({?year})-1,09,01) and
{Orders.Order Date} <= date(maximum({?year}),08,31) else

if {?yeartype} = "Calendar" then
{Orders.Order Date} >= date(minimum({?year}),01,01) and
{Orders.Order Date} <= date(maximum({?year}),12,31)

This will return all records between two earliest and latest years selected. If you wanted to be able to select non-consecutive years, you would need a more complicated formula.

-LB