I need to set up a report that will print columns for each year. I am able to do this without problem for calendar year but the client has indicated they wish to run it on fiscal year or calendar year and based on any year-end date. So for the simple example of a fiscal year ending Sep 30 of each year:

They wish to run the report based on a parameter selection and if they select calendar year, no problem. However, if they select the fiscal year option then each column's data should be based on dates for each year starting Oct 1 of one year and ending Sep 30 of the next year eg. Oct 1, 2006 ending Sep 30, 2007 for one column, Oct 1, 2007 ending Sep 30, 2008 for the next and Oct 1, 2008 ending Sep 30, 2009 for the last if there is three year's data in the database.

I have not asked the question because I am afraid of the answer but I am sure they will ask for each of the last 12 months ending "enter a date here" which could be May 31. So then the report would have to pull Jun1 of one year to May 30 of the next year and put everything into columns.

I think a crosstab is best here but I may have to group by category and manually calculate totals based on standard set of years and put the totals in the group footer. However, I don't think I should have to do this if a crosstab can do the work.

I am just not sure how to approach this one so any ideas are appreciated. TIA rasinc