Sale batch report with the total quantity sold per item

Posted by Ed on March 13, 2009, 10:26 am

Please log in for more thread options

How i can setup a sale batch report with the total quantity sold per item in
the given batch # range for each item
Example:

Batch :45

Item :464343 Qty 12
Item :678445 Qty 67
etc.

Posted by Matt Hurst on March 14, 2009, 9:15 pm

Please log in for more thread options

here is a query that will give that result for the current day:

select t.storeid, batchnumber, itemlookupcode, sum(e.quantity) qty
from [transaction] t
join transactionentry e on t.storeid=e.storeid
and e.transactionnumber=t.transactionnumber
join item on itemid=item.id where time>convert(char,getdate(),101)
group by t.storeid, batchnumber, itemlookupcode
order by t.storeid, batchnumber, itemlookupcode

Here is the same in an HQ report:

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

Begin ReportSummary
ReportType = reporttypeItems
ReportTitle = "Batch ILC Qty"
PageOrientation = pageorientationPortrait
WordWrap = False
ShowDateTimePicker = False
OutLineMode = True
Groups = 0
GroupDescription = "Total"
DisplayLogo = True
LogoFileName = "MyLogo.bmp"
ProcedureCall = ""
PreQuery1 = ""
PreQuery2 = ""
TablesQueried = <BEGIN>

from [transaction] t
join transactionentry e on t.storeid=e.storeid
and e.transactionnumber=t.transactionnumber
join item on itemid=item.id

<END>
SelCriteria = ""
GroupBy = "t.storeid, batchnumber, itemlookupcode"
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 = "As Of: <Report Date>"
Font = "Arial"
FontBold = True
FontSize = 10
Color = "Black"
End TitleRow

//--- Filters ---//

Begin Filter
FieldName = "t.time"
FilterOp = reportfilteropBetween
FilterLoLim = <Today>
FilterHilim = <Today>
FilterNegated = False
FilterConnector = reportfilterbooleanconAND
End Filter

//--- Columns ---//

Begin Column
FieldName = "t.StoreID"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "StoreID"
VBDataType = vbInteger
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1395
GroupMethod = groupmethodNone
ColFormat = ""
End Column

Begin Column
FieldName = "t.batchnumber"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Batch"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1605
GroupMethod = groupmethodNone
ColFormat = ""
End Column

Begin Column
FieldName = "Item.ItemLookUpCode"
DrillDownFieldName = "Item.ItemLookUpCode"
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "LookUp Code"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1245
GroupMethod = groupmethodCount
ColFormat = ""
End Column

Begin Column
FieldName = "qty"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Quantity"
VBDataType = vbInteger
Formula = "sum(e.quantity)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 870
GroupMethod = groupmethodSum
ColFormat = ""
End Column

Begin Column
FieldName = "t.time"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Date"
VBDataType = vbDate
Formula = ""
ColHidden = True
ColNotDisplayable = True
FilterDisabled = False
ColWidth = 1250
GroupMethod = groupmethodnone
ColFormat = ""
End Column

"Ed" wrote:

> How i can setup a sale batch report with the total quantity sold per item in

> the given batch # range for each item

> Example:

> Batch :45

> Item :464343 Qty 12

> Item :678445 Qty 67

> etc.