Rolling Columns

[strom99]

Hello

Does anyone know of a way to have columns change every month. I need to keep track of the number of INVOICE_NBR for each Customer Service Rep. It needs to be a 12 month rollng count. So if I ran a report today it would show the past 12 full months

??

Thank You

 

[kenhamady]

See my FAQ on Conditional formulas and Totals. The trick is that your conditions are themselves based on formulas that calculate the months. For instance I could calculate 12 monthly column conditions (month and year) by using formulas like these:

@Month12
month(DateAdd ('m',-1 ,currentdate ))

and:
@year12
year(DateAdd ('m',-1 ,currentdate ))

Now you can write a formula for the 12th column like:

If Month ({table.date}) = {@Month12}
and Year ({table.date}) = {@Year12}
then {table.amount} else 0

Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)