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
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 290,900 Super User 2024 Season 2
Martin Dráb 229,297 Most Valuable Professional
nmaenpaa 101,156