Rolling Months

[raccess]

Hi,

I have report in which I need data for previous 3 months of data as per user selection, If user selects Mar then display data for Jan, Feb & Mar. If user selects Feb then display just Jan & Feb data as I have to display only single year of data from anywhere Jan to Dec.

In table I have data with Month number like,

MonthNo Year TranAmt
2 2004 12.00
3 2003 30.00
12 2004 23.00


Something like this.

How I can span rolling 3 month in report. How I can achieve this?

Thanks in Advance,

R

 

[Mountaine]

Use a parameter that asks the user the question "which ending month do you want?" Let's say you call the parameter "LastMonth". Then use the result of that parameter in record selection, where the record selection tests the data to ensure that the month of any record is one of the ones you want. If your data field for month is MONTH then the record selection formula would be

MONTH=LastMonth or MONTH=Lastmonth-1 or MONTH=Lastmonth-2.

I think this may solve your problem, since if the user select february, the selection would be months 2 or 1 or 0, and month 0 won't find any data...

 

[lbass]

You need to request the year also, I think, so you might add both an endmonth parm {?endmo} and a year parm {?parm}. Then try something like:

{table.date} in dateserial({?year},{?endmo}-2,01) to dateserial({?year},{?endmo}+1,01)-1 and
year({table.date}) = {?year}

The first part would give you the rolling three months and the second clause would limit the data to the calendar year, if the month was January or February.

-LB