MonthToDate YearToDate

[Abernut]

Hello.
Right now we are trying to decide between SSRS and Crystal Report Server. I've been writing simple reports with Crystal for a little while and I love the ease of making the report look nice and pretty for the end user (It seems that's all they care about). So far with SSRS I can only get tabbed reports but I love the ease of deploying the reports to view via a browser.

But on to my question.
I have a table which contains all sales transactions {sales_history}. With-in the table is invoice_date and sales_amount and customer_name

I need to to create a report to display LastYear Sales, MonthToDate Sales, YearToDate Sales, and Total Sales.

I am a bit lost on this.

Thank you for you help.

 

[xlbo]

This is a SQL query really rather than anything to do with SSRS - you are genreeally better off doing any heavy calc crunching in the SQL rather than in SSRS itself

Having said that, if you need to do this in teh report, what you need to look at are conditional sums e.g.

Last Year Sales:

=SUM(iif(Year(Fields!Invoice_Date.value) = Year(Today())-1,cdbl(Fields!Sales_Amount.value),cdbl(0)))

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Abernut]

This is what I have came up with so far.



select oeh_MQ.bill_to_name,
(select sum(oeh_TS.tot_sls_amt)
from dbo.oehdrhst_sql oeh_TS
where Year(oeh_TS.inv_dt)=Year(getdate())-1
and oeh_TS.bill_to_name = oeh_MQ.bill_to_name) as "Total Sales Last Year",
(select sum(oeh_MTD.tot_sls_amt)
from dbo.oehdrhst_sql oeh_MTD
where (Month(oeh_MTD.inv_dt)=Month(getdate())
and Year(oeh_MTD.inv_dt)=Year(getdate()))
and oeh_MTD.bill_to_name = oeh_MQ.bill_to_name) as "Month to Date",
(select sum(oeh_YTD.tot_sls_amt)
from dbo.oehdrhst_sql oeh_YTD
where Year(oeh_YTD.inv_dt)=Year(getdate())
and oeh_YTD.bill_to_name = oeh_MQ.bill_to_name) as "Year to Date"
from dbo.oehdrhst_sql oeh_MQ
group by oeh_MQ.bill_to_name
order by 1