Fiscal Year in a CrossTab

[ClanLabCooker]

Version: 8.5
DataSource: View with read-only access

I have no problem creating a cross tab displaying the correct records with column headings of:

2003 2004 2005
Jan Feb Mar... Jan Feb Mar... Jan Feb Mar...

by putting the date field in the columns area twice, first grouped by year, the second grouped by month.

What I need are two FISCAL YEAR sorts (July through June)with the following formats:

REPORT 1
FY2003 FY2004 FY2005
Jul Aug Sept... Jul Aug Sept ... Jul Aug Sept...

REPORT 2
JULY AUGUST SEPTEMBER
FY03 FY04 FY05 FY03 FY04 FY05 FY03 FY04 FY05


Any assistance on either or both reports would be appreciated.

 

[lbass]

Create a formula {@fiscalyear}:

if {table.date} in date(year(currentdate+184)-1,07,01) to
date(year(currentdate+184),06,30) then "Fiscal Year "+
totext(year(currentdate+184),0,"") else
if {table.date} in date(year(currentdate+184)-2,07,01) to
date(year(currentdate+184)-1,06,30) then "Fiscal Year "+
totext(year(currentdate+184)-1,0,"") else
if {table.date} in date(year(currentdate+184)-3,07,01) to
date(year(currentdate+184)-2,06,30) then "Fiscal Year "+
totext(year(currentdate+184)-2,0,"")

For Report 1, use this formula as the first column field, and then enter {table.date} as the second column field->group options->print on change of month. For Report 2, you would just reverse the order of entry of the two column fields.

-LB

 

[ClanLabCooker]

50% Success!!!!

Report 1 worked perfectly, THANK YOU!

However, Report 2 still isn't delivering what I need. Following your suggestions this is the output I get:

7/02 8/02 9/02
FY02 Tot FY02 Tot FY02 Tot

What I need:

Jul Aug Sept
FY02 FY03 FY04 FY02 FY03 FY04 FY02 FY03 FY04

I think I need a formula for the first column that will look ONLY at the month, not the entire date. Is that correct? If so, what would it look like?

 

[ClanLabCooker]

Never Mind!!

I answered my own question. Still working on format, but have the information I need. Too early in the morning, I guess.

Excellent response and I thank you!

 

[lbass]

Oops, yes, you are right. Create a formula:

month({table.date})

...and use this as your column field for Report 2. Then highlight the formula in the crosstab expert column area->group options->customize group name->use a formula to customize name and enter:

monthname(month({table.date}))

Be sure to refresh the report after adding the customized name so that you can see the correct result.

-LB