how to get MTD, YTD columns

[Kennymc]

I want to develope a report that looks like this
Sales as of "aug 15, 2004" -
cust# daily sales MTD sales YTD sales lastyear sales
a1 1.00 2.00 4.00 10.00
a2 4.00 5.00 15.00
a3 1.00
a4 20.00

user input is a valid date. I did this report using Oracle Report writer, but now with a new accounting system I need to use crystal CR9.

In my example I can't use monthtodate function as I'll also get sales past Aug 15

the table has data columns cust#, invoice date, amount

Suggestions - current thought is using stored procedures

 

[lbass]

Assuming you are using a date parameter {?date}, then for MTD use something like:

if {table.date} in date(year({?date}),month({?date}),01) to {?date} then {table.sales}

For YTD, try:
if {table.date} in date(year({?date}), 01, 01) to {?date} then {table.sales}

-LB