Sum of SaleAmt for 3 separate months from the date report runs

[fredong]

I need to create an invoice report with the sum of SalesAmt for 3 separate months from the Date when user run the report. So the months will be varies depend when the user run the report. I have to group by 3 different Categories. The group byVendor Categories is my puzzle for the total salesamt for 3 separate month

Below is my example

1st Group by SalesPerson
John
Peter
James
2nd Group by customer
Publix
Walmart
Bi-LO

3rd Group by Vendors Jan Feb March
SONY SalesAmt SalesAmt SalesAmt
RCA SalesAmt SalesAmt SalesAmt
HITACHI SalesAmt SalesAmt SalesAmt


Subtotal $50,000 $150,000 $100,000

--so how can get the sum of SalesAmt from the last 3 month month separately by different vendors form the month that user run the report.This is what I am trying to do in the Formula editor by creating a formula but there is an error
@totalSaleMonth1

sum{tek_sp_rpt_SLIPandGAIN;1.totalsale}DateAdd("m",-3,{?@MonthStart})

--?@MonthStart is the parameter that user enter the date

Thanks if someone can help.

 

[Progmatic]

Hi, I can think of one thing. Take in the current date as a parmaeter and strip out the month. Then calculate the past three months from that. Then in your select statement you can use a decode statement to get the amounts for the three months as three different columns. decode statment will be something like this-
select decode(to_char(Month_Field_in_table,'mm'),1,salesamount,0)amount_month1,decode(to_char(Month_Field_in_table,'mm'),2,salesamoutn,0)amount_month2,decode(to_char(Month_Field_in_table,'mm'),3,salesamount,0) amount_month3,......
I hope this helps...
cheers

 

[jpicks]

try creating a formula that returns the month that each sales transaction occurred in.

ie.
@month
month({tablename.transaction_date})

You could use this formula as your main group.

this would give you:

Month 1
customer
vendor
Total Month 1 SONY SalesAmt
Total Month 1 RCA SalesAmt
Total Month 1 HITACHI SalesAmt
Month 2
customer
vendor
Total Month 2 SONY SalesAmt
Total Month 2 RCA SalesAmt
Total Month 2 HITACHI SalesAmt
Month 3
customer
vendor
Total Month 3 SONY SalesAmt
Total Month 3 RCA SalesAmt
Total Month 3 HITACHI SalesAmt
Report Footer
Total all months SONY SalesAmt
Total all months RCA SalesAmt
Total all months HITACHI SalesAmt

Hope this helps,

-Jim