Skip to main content

Notifications

Microsoft Dynamics RMS (Archived)

RMS 2.0 Sales per Item, Month by Month for Calenday Year Custom Report

Posted on by 110

Does anyone have a report that we could use for ordering pruducts at shows...

When we order we can select when we want it to ship and knowing the break down of when we sold things is vital...

Here is the code for the report we have that works, but could be improved great.

Thank you for any and all help!

Bobby

320-253-4868

 

//--- Report Summary --- //

Begin ReportSummary
   ReportType = reporttypeSales
   ReportTitle = "12 Month Item Quantities"
   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 = 'MonthlyView') DROP VIEW MonthlyView"
   PreQuery2 = <BEGIN>

  CREATE VIEW MonthlyView AS
  Select Item.ItemLookupCode,
 Item.Description,
 Item.SubDescription1 AS SUB1,
        Item.Price,
        Item.Cost,
        Item.Quantity,
 [Transaction].Time as Date,
 Register.Number as Register,
 Cashier.Name as CashierName,
 Cashier.Number as CashierNum,
 Department.Name as Department,
 Category.Name as Category,
 Supplier.SupplierName,
 SalesRep.Number as SalesRepNum,
 CASE WHEN cast(DatePart(Mm,[Transaction].Time) as nvarchar) BETWEEN '1' AND '1' THEN SUM(TRANSACTIONENTRY.QUANTITY) ELSE 0 END as Jan,
 CASE WHEN cast(DatePart(Mm,[Transaction].Time) as nvarchar) BETWEEN '2' AND '2' THEN SUM(TRANSACTIONENTRY.QUANTITY) ELSE 0 END as Feb,
 CASE WHEN cast(DatePart(Mm,[Transaction].Time) as nvarchar) BETWEEN '3' AND '3' THEN SUM(TRANSACTIONENTRY.QUANTITY) ELSE 0 END as Mar,
 CASE WHEN cast(DatePart(Mm,[Transaction].Time) as nvarchar) BETWEEN '4' AND '4' THEN SUM(TRANSACTIONENTRY.QUANTITY) ELSE 0 END as Apr,
 CASE WHEN cast(DatePart(Mm,[Transaction].Time) as nvarchar) BETWEEN '5' AND '5' THEN SUM(TRANSACTIONENTRY.QUANTITY) ELSE 0 END as May,
 CASE WHEN cast(DatePart(Mm,[Transaction].Time) as nvarchar) BETWEEN '6' AND '6' THEN SUM(TRANSACTIONENTRY.QUANTITY) ELSE 0 END as Jun,
 CASE WHEN cast(DatePart(Mm,[Transaction].Time) as nvarchar) BETWEEN '7' AND '7' THEN SUM(TRANSACTIONENTRY.QUANTITY) ELSE 0 END as Jul,
 CASE WHEN cast(DatePart(Mm,[Transaction].Time) as nvarchar) BETWEEN '8' AND '8' THEN SUM(TRANSACTIONENTRY.QUANTITY) ELSE 0 END as Aug,
 CASE WHEN cast(DatePart(Mm,[Transaction].Time) as nvarchar) BETWEEN '9' AND '9' THEN SUM(TRANSACTIONENTRY.QUANTITY) ELSE 0 END as Sep,
 CASE WHEN cast(DatePart(Mm,[Transaction].Time) as nvarchar) BETWEEN '10' AND '10' THEN SUM(TRANSACTIONENTRY.QUANTITY) ELSE 0 END as Oct,
 CASE WHEN cast(DatePart(Mm,[Transaction].Time) as nvarchar) BETWEEN '11' AND '11' THEN SUM(TRANSACTIONENTRY.QUANTITY) ELSE 0 END as Nov,
 CASE WHEN cast(DatePart(Mm,[Transaction].Time) as nvarchar) BETWEEN '12' AND '12' THEN SUM(TRANSACTIONENTRY.QUANTITY) ELSE 0 END as Dec,
 SUM(TRANSACTIONENTRY.QUANTITY) as Total

  FROM
 [transaction]
 LEFT JOIN TRANSACTIONENTRY ON [TRANSACTION].TRANSACTIONNUMBER = TRANSACTIONENTRY.TRANSACTIONNUMBER
 LEFT JOIN ITEM ON TRANSACTIONENTRY.ITEMID = ITEM.ID
 LEFT JOIN BATCH ON [TRANSACTION].BATCHNUMBER = BATCH.BATCHNUMBER
 LEFT JOIN REGISTER ON BATCH.REGISTERID = REGISTER.ID
 LEFT JOIN CASHIER ON [TRANSACTION].CASHIERID = CASHIER.ID
 LEFT JOIN DEPARTMENT ON ITEM.DEPARTMENTID = DEPARTMENT.ID
 LEFT JOIN CATEGORY ON ITEM.CATEGORYID = CATEGORY.ID
 LEFT JOIN SUPPLIER ON ITEM.SUPPLIERID = SUPPLIER.ID
 LEFT JOIN SALESREP ON TRANSACTIONENTRY.SALESREPID = SALESREP.ID  

  GROUP BY
 Item.ItemLookupCode,
 Item.Description,
 Item.SubDescription1,
 [Transaction].Time,
 Register.Number,
 Cashier.Name,
 Cashier.Number,
 Department.Name,
 Category.Name,
 Supplier.SupplierName,
 SalesRep.Number,
        Item.Cost,
        Item.Price,
        Item.Quantity
 
 
<END>
   TablesQueried = "FROM MonthlyView"
   SelCriteria = ""
   GroupBy = "MonthlyView.ItemLookupCode, MonthlyView.Description, MonthlyView.SUB1, MonthlyView.Date, MonthlyView.Register, MonthlyView.CashierName, MonthlyView.CashierNum, MonthlyView.Department, MonthlyView.Category, MonthlyView.SupplierName, MonthlyView.SalesRepNum, MonthlyView.Quantity, Monthlyview.Cost, Monthlyview.Price"
   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 = "MonthlyView.Date"
   FilterOp = reportfilteropBetween
   FilterLoLim = "1/1/2011"
   FilterHilim = "12/31/2011"
   FilterNegated = False
   FilterConnector = reportfilterbooleanconAND
End Filter


//--- Columns ---//

Begin Column
   FieldName = "MonthlyView.ItemLookupCode"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   StoreIDFieldName = ""
   Title = "Item"
   VBDataType = vbString
   Formula = "MonthlyView.ItemLookupCode + ', ' + MonthlyView.Department + ', ' + MonthlyView.Description + ', ' + MonthlyView.SUB1"
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 5085
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "Monthlyview.price"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   StoreIDFieldName = ""
   Title = "Price"
   VBDataType = vbCurrency
   Formula = ""
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 885
   GroupMethod = groupmethodMax
   ColFormat = ""
End Column

Begin Column
   FieldName = "Monthlyview.Cost"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   StoreIDFieldName = ""
   Title = "Cost"
   VBDataType = vbCurrency
   Formula = ""
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 870
   GroupMethod = groupmethodMax
   ColFormat = ""
End Column

Begin Column
   FieldName = "Monthlyview.Quantity"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   StoreIDFieldName = ""
   Title = "On-Hand"
   VBDataType = vbDouble
   Formula = ""
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 795
   GroupMethod = groupmethodMax
   ColFormat = ""
End Column

Begin Column
   FieldName = "qty"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   StoreIDFieldName = ""
   Title = "Jan"
   VBDataType = vbDouble
   Formula = "SUM(MonthlyView.Jan)"
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = True
   ColWidth = 495
   GroupMethod = groupmethodSum
   ColFormat = "###"
End Column

Begin Column
   FieldName = "qty"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   StoreIDFieldName = ""
   Title = "Feb"
   VBDataType = vbDouble
   Formula = "SUM(MonthlyView.Feb)"
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = True
   ColWidth = 495
   GroupMethod = groupmethodSum
   ColFormat = "###"
End Column

Begin Column
   FieldName = "qty"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   StoreIDFieldName = ""
   Title = "Mar"
   VBDataType = vbDouble
   Formula = "SUM(MonthlyView.Mar)"
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = True
   ColWidth = 495
   GroupMethod = groupmethodSum
   ColFormat = "###"
End Column

Begin Column
   FieldName = "qty"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   StoreIDFieldName = ""
   Title = "Apr"
   VBDataType = vbDouble
   Formula = "SUM(MonthlyView.Apr)"
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = True
   ColWidth = 495
   GroupMethod = groupmethodSum
   ColFormat = "###"
End Column

Begin Column
   FieldName = "qty"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   StoreIDFieldName = ""
   Title = "May"
   VBDataType = vbDouble
   Formula = "SUM(MonthlyView.May)"
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = True
   ColWidth = 495
   GroupMethod = groupmethodSum
   ColFormat = "###"
End Column

Begin Column
   FieldName = "qty"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   StoreIDFieldName = ""
   Title = "Jun"
   VBDataType = vbDouble
   Formula = "SUM(MonthlyView.Jun)"
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = True
   ColWidth = 495
   GroupMethod = groupmethodSum
   ColFormat = "###"
End Column

Begin Column
   FieldName = "qty"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   StoreIDFieldName = ""
   Title = "Jul"
   VBDataType = vbDouble
   Formula = "SUM(MonthlyView.Jul)"
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = True
   ColWidth = 495
   GroupMethod = groupmethodSum
   ColFormat = "###"
End Column

Begin Column
   FieldName = "qty"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   StoreIDFieldName = ""
   Title = "Aug"
   VBDataType = vbDouble
   Formula = "SUM(MonthlyView.Aug)"
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = True
   ColWidth = 495
   GroupMethod = groupmethodSum
   ColFormat = "###"
End Column

Begin Column
   FieldName = "qty"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   StoreIDFieldName = ""
   Title = "Sep"
   VBDataType = vbDouble
   Formula = "SUM(MonthlyView.Sep)"
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = True
   ColWidth = 495
   GroupMethod = groupmethodSum
   ColFormat = "###"
End Column

Begin Column
   FieldName = "qty"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   StoreIDFieldName = ""
   Title = "Oct"
   VBDataType = vbDouble
   Formula = "SUM(MonthlyView.Oct)"
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = True
   ColWidth = 495
   GroupMethod = groupmethodSum
   ColFormat = "###"
End Column

Begin Column
   FieldName = "qty"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   StoreIDFieldName = ""
   Title = "Nov"
   VBDataType = vbDouble
   Formula = "SUM(MonthlyView.Nov)"
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = True
   ColWidth = 495
   GroupMethod = groupmethodSum
   ColFormat = "###"
End Column

Begin Column
   FieldName = "qty"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   StoreIDFieldName = ""
   Title = "Dec"
   VBDataType = vbDouble
   Formula = "SUM(MonthlyView.Dec)"
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = True
   ColWidth = 495
   GroupMethod = groupmethodSum
   ColFormat = "###"
End Column

Begin Column
   FieldName = "MonthlyView.Total"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   StoreIDFieldName = ""
   Title = "Total"
   VBDataType = vbDouble
   Formula = "SUM(MonthlyView.Total)"
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 600
   GroupMethod = groupmethodSum
   ColFormat = "######"
End Column

Begin Column
   FieldName = "MonthlyView.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 = "MonthlyView.Register"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   StoreIDFieldName = ""
   Title = "Reg #"
   VBDataType = vbString
   Formula = ""
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1395
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "MonthlyView.CashierName"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   StoreIDFieldName = ""
   Title = "Cashier"
   VBDataType = vbString
   Formula = ""
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1395
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "MonthlyView.CashierNum"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   StoreIDFieldName = ""
   Title = "Cashier #"
   VBDataType = vbString
   Formula = ""
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1395
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "MonthlyView.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 = "MonthlyView.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 = "MonthlyView.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 = "MonthlyView.SalesRepNum"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   StoreIDFieldName = ""
   Title = "Sales Rep"
   VBDataType = vbString
   Formula = ""
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1395
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "MonthlyView.Date"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   StoreIDFieldName = ""
   Title = "Date"
   VBDataType = vbDate
   Formula = ""
   ColHidden = True
   ColNotDisplayable = True
   FilterDisabled = False
   ColWidth = 1395
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

 

*This post is locked for comments

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Complete!

🔔 Be sure to subscribe to the new forums you are interested in to stay up to date! 🔔

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,900 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,297 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans