November 29, 2010 at 5:27 pm
Good day experts,
I have a problem which I have been searching all over the internet for. I am creating ssrs reports (OLTP) and i want to know the best way to compare previous year time period in the report.
For example if i run the report for sales for the month of January 2010, I also want to see sales for the same time period a year ago side by side i.e sales for the month of january 2009. Is this possible in SSRS? If so any articles and/or explanations will be appreciated. Thanks
December 7, 2010 at 8:04 am
It's certainly possible with Reporting Services. For year-over-year data, I usually pass the year and/or month in a parameter, then return a column for that month and a second column for the same month of the previous year. Try something like this:
SUM(CASE WHEN SalesMonth = @Month AND SalesYear = @Year THEN Amount ELSE 0 END)
as ThisMonth,
SUM(CASE WHEN SalesMonth = @Month AND SalesYear = @Year - 1 THEN Amount ELSE 0 END)
as ThisMonthLastYear
That way you're bringing in the two columns without having to do inspection or calculation on the report side.
January 11, 2011 at 8:46 am
Hello Doug,
I am using your seggested query logic for prior year comparison. However, it appears that the @Year -1 is not working.
DECLARE @Month int
DECLARE @Year int
SET @Month = 1
SET @Year = 2010
SELECT --isnull(j.RSMEmpNum,0) AS RSMEmpNum,
ISNULL((e.FirstName + ' ' + e.LastName),'Unknown') as RSMName
,SUM(CASE WHEN DATEPART(Month, j.NTPDate)= @Month AND DATEPART(Year, NTPDate) = @Year
THEN (ISNULL(j.EngSellPrice,0)+ ISNULL(j.MatSellPrice,0) + ISNULL(j.InstallSellPrice,0)) ELSE 0 END)
as ThisMonthThisYear
,SUM(CASE WHEN DATEPART(Month, j.NTPDate)= @Month AND DATEPART(Year, NTPDate) = @Year-1
THEN (ISNULL(j.EngSellPrice,0)+ ISNULL(j.MatSellPrice,0) + ISNULL(j.InstallSellPrice,0)) ELSE 0 END)
as ThisMonthThisYear
FROM tblJOBS j
LEFT OUTER JOIN tblCustomers c ON j.Customer = c.Customer
Left Outer Join tblEmployees e ON j.RSMEmpNum = e.EmpNum
LEFT OUTER JOIN tblEmployees e2 ON j.SalesRep = e2.EmpNum
WHERE NTPDate IS NOT NULL
AND DATEPART(Year,NTPDAte) = (@Year)
--AND InvoiceNumber IS NOT NULL
--AND j.Active = 1
--AND Status = 'SHIPPED'
GROUP BY ISNULL((e.FirstName + ' ' + e.LastName),'Unknown')--,DATEPART(Year, NTPDate),DATEPART(Month, NTPDate)
--ORDER BY DATEPART(Year, NTPDate), DATEPART(Month, NTPDate)
January 11, 2011 at 10:46 am
Hi Steven,
It looks like you have two columns with the same name "ThisMonthThisYear". Does that have anything to do with why it isn't working? As far as I can tell, the rest of the code looks okay.
-Doug
January 11, 2011 at 11:39 am
I figured it out. The resaon I wasn't getting the @year-1 logic to work was because the where clause state that NTPDate is = @year. So it was filtering for only the year passed by the parameter. Thus the query couldn't return data for the previous year.
I figured it out right after I posted my question. Thanks for your reply though.
Steven
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply