You’re offline. This is a read only version of the page.
Hi folks
Looking for a report giving me total monthly sales for 12 months by department. I would also like to be able to adjust the report dates so I could do this year, last year or any other specified year. (RMS single store setup).
Sample output:
Department Jan Feb Mar ......... Total
Hardware $10,999 $1,200 $100 $12,299
Thanks
Dan
*This post is locked for comments
Hi Eliud Mugo
This is good, thanks. Can you do this also so I can run a similar report that shows Transactions per month and a similar report that sums cost of goods by month? I had a bit of a play but am a total newbie and couldnt get it to work.
Much appreciated
Susan
Hi
Are you looking for something like this?
//--- Report Summary --- //
Begin ReportSummary
ReportType = reporttypeSales
ReportTitle = "12 Month Item Sales"
PageOrientation = pageorientationLandscape
WordWrap = False
ShowDateTimePicker = False
OutLineMode = True
Groups = 1
GroupDescription = ""
DisplayLogo = True
LogoFileName = "MyLogo.bmp"
ProcedureCall = ""
PreQuery1 = "IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'SalesMonthlyView') DROP VIEW SalesMonthlyView"
PreQuery2 = <BEGIN>
CREATE VIEW SalesMonthlyView AS
Select Item.ItemLookupCode,
Item.Description,
Transactionentry.Price as sales,
Transactionentry.Cost as cost,
Transactionentry.Quantity as Quantity,
[Transaction].Time as Date,
Department.Name as Department,
Category.Name as Category,
Supplier.SupplierName,
CASE WHEN cast(DatePart(Mm,[Transaction].Time) as nvarchar) BETWEEN '1' AND '1' THEN SUM(TRANSACTIONENTRY.PRICE * transactionentry.quantity) ELSE 0 END as Jan,
CASE WHEN cast(DatePart(Mm,[Transaction].Time) as nvarchar) BETWEEN '2' AND '2' THEN SUM(TRANSACTIONENTRY.PRICE * transactionentry.quantity) ELSE 0 END as Feb,
CASE WHEN cast(DatePart(Mm,[Transaction].Time) as nvarchar) BETWEEN '3' AND '3' THEN SUM(TRANSACTIONENTRY.PRICE * transactionentry.quantity) ELSE 0 END as Mar,
CASE WHEN cast(DatePart(Mm,[Transaction].Time) as nvarchar) BETWEEN '4' AND '4' THEN SUM(TRANSACTIONENTRY.PRICE * transactionentry.quantity) ELSE 0 END as Apr,
CASE WHEN cast(DatePart(Mm,[Transaction].Time) as nvarchar) BETWEEN '5' AND '5' THEN SUM(TRANSACTIONENTRY.PRICE * transactionentry.quantity) ELSE 0 END as May,
CASE WHEN cast(DatePart(Mm,[Transaction].Time) as nvarchar) BETWEEN '6' AND '6' THEN SUM(TRANSACTIONENTRY.PRICE * transactionentry.quantity) ELSE 0 END as Jun,
CASE WHEN cast(DatePart(Mm,[Transaction].Time) as nvarchar) BETWEEN '7' AND '7' THEN SUM(TRANSACTIONENTRY.PRICE * transactionentry.quantity) ELSE 0 END as Jul,
CASE WHEN cast(DatePart(Mm,[Transaction].Time) as nvarchar) BETWEEN '8' AND '8' THEN SUM(TRANSACTIONENTRY.PRICE * transactionentry.quantity) ELSE 0 END as Aug,
CASE WHEN cast(DatePart(Mm,[Transaction].Time) as nvarchar) BETWEEN '9' AND '9' THEN SUM(TRANSACTIONENTRY.PRICE * transactionentry.quantity) ELSE 0 END as Sep,
CASE WHEN cast(DatePart(Mm,[Transaction].Time) as nvarchar) BETWEEN '10' AND '10' THEN SUM(TRANSACTIONENTRY.PRICE * transactionentry.quantity) ELSE 0 END as Oct,
CASE WHEN cast(DatePart(Mm,[Transaction].Time) as nvarchar) BETWEEN '11' AND '11' THEN SUM(TRANSACTIONENTRY.PRICE * transactionentry.quantity) ELSE 0 END as Nov,
CASE WHEN cast(DatePart(Mm,[Transaction].Time) as nvarchar) BETWEEN '12' AND '12' THEN SUM(TRANSACTIONENTRY.PRICE * transactionentry.quantity) ELSE 0 END as Dec,
SUM(TRANSACTIONENTRY.PRICE * transactionentry.quantity) as Total
FROM
[transaction]
LEFT JOIN TRANSACTIONENTRY ON [TRANSACTION].TRANSACTIONNUMBER = TRANSACTIONENTRY.TRANSACTIONNUMBER and TransactionEntry.ItemType <> 9
LEFT JOIN ITEM ON TRANSACTIONENTRY.ITEMID = ITEM.ID
LEFT JOIN BATCH ON [TRANSACTION].BATCHNUMBER = BATCH.BATCHNUMBER
LEFT JOIN DEPARTMENT ON ITEM.DEPARTMENTID = DEPARTMENT.ID
LEFT JOIN CATEGORY ON ITEM.CATEGORYID = CATEGORY.ID
LEFT JOIN SUPPLIER ON ITEM.SUPPLIERID = SUPPLIER.ID
GROUP BY
Item.ItemLookupCode,
Item.Description,
Transactionentry.Price,
Transactionentry.Cost,
Transactionentry.Quantity,
[Transaction].Time,
Department.Name,
Category.Name,
Supplier.SupplierName
<END>
TablesQueried = "FROM SalesMonthlyView"
SelCriteria = ""
GroupBy = "SalesMonthlyView.ItemLookupCode, SalesMonthlyView.Description, SalesMonthlyView.Date, SalesMonthlyView.Department, SalesMonthlyView.Category, SalesMonthlyView.SupplierName, SalesMonthlyView.Quantity, SalesMonthlyView.Cost, SalesMonthlyView.Sales"
SortOrder = ""
End ReportSummary
//--- Title Rows ---//
Begin TitleRow
Text = "<Store Name>"
Font = "Arial"
FontBold = True
FontSize = 16
Color = "Blue"
End TitleRow
Begin TitleRow
Text = "<Report Title>"
Font = "Arial"
FontBold = True
FontSize = 12
Color = "Black"
End TitleRow
Begin TitleRow
Text = "Generated On <Report Date>"
Font = "Arial"
FontBold = True
FontSize = 10
Color = "Black"
End TitleRow
//--- Filters ---//
Begin Filter
FieldName = "SalesMonthlyView.Date"
FilterOp = reportfilteropBetween
FilterLoLim = "1/1/2011"
FilterHilim = "12/31/2011"
FilterNegated = False
FilterConnector = reportfilterbooleanconAND
End Filter
//--- Columns ---//
Begin Column
FieldName = "SalesMonthlyView.ItemLookupCode"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Item"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 5085
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "Sales"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Jan"
VBDataType = vbCurrency
Formula = "SUM(SalesMonthlyView.Jan)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = True
ColWidth = 495
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "Sales"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Feb"
VBDataType = vbCurrency
Formula = "SUM(SalesMonthlyView.Feb)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = True
ColWidth = 495
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "Sales"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Mar"
VBDataType = vbCurrency
Formula = "SUM(SalesMonthlyView.Mar)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = True
ColWidth = 495
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "Sales"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Apr"
VBDataType = vbCurrency
Formula = "SUM(SalesMonthlyView.Apr)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = True
ColWidth = 495
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "Sales"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "May"
VBDataType = vbCurrency
Formula = "SUM(SalesMonthlyView.May)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = True
ColWidth = 495
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "Sales"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Jun"
VBDataType = vbCurrency
Formula = "SUM(SalesMonthlyView.Jun)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = True
ColWidth = 495
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "Sales"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Jul"
VBDataType = vbCurrency
Formula = "SUM(SalesMonthlyView.Jul)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = True
ColWidth = 495
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "Sales"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Aug"
VBDataType = vbCurrency
Formula = "SUM(SalesMonthlyView.Aug)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = True
ColWidth = 495
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "Sales"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Sep"
VBDataType = vbCurrency
Formula = "SUM(SalesMonthlyView.Sep)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = True
ColWidth = 495
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "Sales"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Oct"
VBDataType = vbCurrency
Formula = "SUM(SalesMonthlyView.Oct)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = True
ColWidth = 495
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "Sales"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Nov"
VBDataType = vbCurrency
Formula = "SUM(SalesMonthlyView.Nov)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = True
ColWidth = 495
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "Sales"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Dec"
VBDataType = vbCurrency
Formula = "SUM(SalesMonthlyView.Dec)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = True
ColWidth = 495
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "SalesMonthlyView.Total"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Total"
VBDataType = vbCurrency
Formula = "SUM(SalesMonthlyView.Total)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 600
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "SalesMonthlyView.Description"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Item Description"
VBDataType = vbString
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 2220
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "SalesMonthlyView.Department"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Department"
VBDataType = vbString
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1395
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "SalesMonthlyView.Category"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Category"
VBDataType = vbString
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1395
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "SalesMonthlyView.SupplierName"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Supplier"
VBDataType = vbString
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1395
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "SalesMonthlyView.Date"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Date"
VBDataType = vbDate
Formula = ""
ColHidden = True
ColNotDisplayable = True
FilterDisabled = False
ColWidth = 1395
GroupMethod = groupmethodNone
ColFormat = ""
End Column
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.
Currently working on it. I'll get back to you later! :D
Execute below statement if you are running 2008
EXEC sp_dbcmptlevel 'DatabaseName', 100
for 2005
EXEC sp_dbcmptlevel 'DatabaseName', 90
Hi Ramy
Installed SQL 2005 Express Toolkit but still can't run above query?
Thanks
Danny
Hi Ramy
This doesn't work on the Microsoft SQL 2005 express edition - will pivot work on any Microsoft SQL express edition?
Danny
You are recommended to using SQL SERVER PIVOT Table to generate your report
Sample Example:
SELECT *
FROM (
SELECT
YEAR(OrderDate) [Year],
CASE MONTH(OrderDate)
WHEN 1 THEN 'January'
WHEN 2 THEN 'February'
WHEN 3 THEN 'March'
WHEN 4 THEN 'April'
WHEN 5 THEN 'May'
WHEN 6 THEN 'June'
WHEN 7 THEN 'July'
WHEN 8 THEN 'August'
WHEN 9 THEN 'September'
WHEN 10 THEN 'October'
WHEN 11 THEN 'November'
WHEN 12 THEN 'December'
END as [Month],
SubTotal
FROM Sales.SalesOrderHeader
) TableDate
PIVOT (
SUM(SubTotal)
FOR [Month] IN (
[January],[February],[March],[April],
[May],[June],[July],[August],
[September],[October],[November],[December]
)
) PivotTable
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,240
Super User 2024 Season 2
#2
Martin Dráb
230,149
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (