Im back! haist, i thought i could easily translate the report into qrp. I was actually planning to create the report where we could filter the dates but i stumbled on how to do this with filtered dates! Ouch!
I think this would be possible in crystal reports but, oh well, i made it as stored procedure.
Copy and paste the sql query below:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: archelle.pagapulan@outlook.com
-- Create date: Feb 2, 2015
-- Description: Populate Department sales per month based on filter dates.
-- For Microsoft dynamics reference only.
-- =============================================
CREATE PROCEDURE MonthlyDepartmentSalesReport
@fromdate AS DATETIME ,
@todate AS DATETIME
AS
BEGIN
SET NOCOUNT ON;
SELECT Department.Name AS Department ,
MAX(CASE WHEN iMOnth = 1 THEN Total
ELSE ''
END) AS January ,
MAX(CASE WHEN iMOnth = 2 THEN Total
ELSE ''
END) AS February ,
MAX(CASE WHEN iMOnth = 3 THEN Total
ELSE ''
END) March ,
MAX(CASE WHEN iMOnth = 4 THEN Total
ELSE ''
END) AS April ,
MAX(CASE WHEN iMOnth = 5 THEN Total
ELSE ''
END) AS May ,
MAX(CASE WHEN iMOnth = 6 THEN Total
ELSE ''
END) AS June ,
MAX(CASE WHEN iMOnth = 7 THEN Total
ELSE ''
END) AS July ,
MAX(CASE WHEN iMOnth = 8 THEN Total
ELSE ''
END) AS August ,
MAX(CASE WHEN iMOnth = 9 THEN Total
ELSE ''
END) AS September ,
MAX(CASE WHEN iMOnth = 10 THEN Total
ELSE ''
END) AS October ,
MAX(CASE WHEN iMOnth = 11 THEN Total
ELSE ''
END) AS November ,
MAX(CASE WHEN iMOnth = 12 THEN Total
ELSE ''
END) AS December
FROM ( SELECT i.DepartmentID ,
DATEPART(MONTH, t.[time]) AS iMOnth ,
SUM(te.Quantity * te.Price) AS Total
FROM dbo.[Transaction] t
LEFT JOIN dbo.TransactionEntry te ON te.TransactionNumber = t.TransactionNumber
INNER JOIN dbo.Batch b ON b.BatchNumber = T.BatchNumber
LEFT JOIN Item i ON i.ID = te.ItemID
WHERE i.DepartmentID != 0
AND t.[Time] >= @fromdate
AND t.[Time] <= @todate
GROUP BY i.DepartmentID ,
DATEPART(MONTH, t.[time])
) AS tr
LEFT JOIN dbo.Department ON Department.ID = tr.DepartmentID
GROUP BY Department.Name
END
GO
Run the query using this format:
EXEC MonthlyDepartmentSalesReport @fromdate = '2014-01-01', @todate = '2015-01-01'
Thats it!
If you think this post solves your problem, pls do reply and mark this thread as closed.
