Share
Reporthow do I run a report that shows my total store inventory and also shows the total monetary value of the inventory
*This post is locked for comments
You’re offline. This is a read only version of the page.
90
how do I run a report that shows my total store inventory and also shows the total monetary value of the inventory
*This post is locked for comments
See if this works for you - the ExtendedCost and ExtendedPrice columns would multiply on hand by cost and by price.
//--- Report Summary --- //
Begin ReportSummary
ReportType = reporttypeItems
ReportTitle = "Item Value List with Profit"
PageOrientation = pageorientationLandscape
OutLineMode = True
Groups = 1
GroupDescription = ""
DisplayLogo = True
LogoFileName = "MyLogo.bmp"
ProcedureCall = ""
TablesQueried = "FROM Item WITH(NOLOCK) LEFT JOIN Department WITH(NOLOCK) ON Item.DepartmentID = Department.ID LEFT JOIN Category WITH(NOLOCK) ON Item.CategoryID = Category.ID LEFT JOIN Supplier WITH(NOLOCK) ON Item.SupplierID = Supplier.ID"
SelCriteria = ""
GroupBy = ""
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 = "Item.Inactive"
FilterOp = reportfilteropEqual
FilterLoLim = "0"
FilterHilim = "0"
End Filter
//--- Columns ---//
Begin Column
FieldName = "Department.Name"
DrillDownFieldName = "Department.Name"
DrillDownReportName = ""
Title = "Department"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 2205
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column
Begin Column
FieldName = "Category.Name"
DrillDownFieldName = "Category.Name"
DrillDownReportName = ""
Title = "Category"
VBDataType = vbString
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1230
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column
Begin Column
FieldName = "Supplier.SupplierName"
DrillDownFieldName = "Supplier.SupplierName"
DrillDownReportName = ""
Title = "Supplier"
VBDataType = vbString
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1725
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column
Begin Column
FieldName = "Item.ItemLookupCode"
DrillDownFieldName = "Item.ItemlookupCode"
DrillDownReportName = ""
Title = "Item"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1320
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column
Begin Column
FieldName = "Item.Description"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Description"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 2085
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column
Begin Column
FieldName = "Item.Quantity"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Qty On Hand"
VBDataType = vbDouble
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1035
GroupMethod = groupmethodSum
ColFormat = "#.##"
End Column
Begin Column
FieldName = "Item.QuantityCommitted"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Qty Comm."
VBDataType = vbDouble
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1050
GroupMethod = groupmethodSum
ColFormat = "#.##"
End Column
Begin Column
FieldName = "Item.Price"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Price"
VBDataType = vbCurrency
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 855
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "Item.Cost"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Cost"
VBDataType = vbCurrency
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 855
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "ItemProfit"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Profit"
VBDataType = vbCurrency
Formula = "Item.Price - Item.Cost"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 855
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "ItemMargin"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Margin"
VBDataType = vbDouble
Formula = "CASE WHEN Item.Cost > 0 and (Item.Price > Item.Cost) THEN ((Item.Price - Item.Cost)/Item.Price) ELSE 0 END"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 855
GroupMethod = groupmethodNone
ColFormat = "#.##%"
End Column
Begin Column
FieldName = "Item.PriceA"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Price A"
VBDataType = vbCurrency
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 720
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "Item.PriceB"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Price B"
VBDataType = vbCurrency
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 660
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "Item.PriceC"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Price C"
VBDataType = vbCurrency
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 720
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "Item.MSRP"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "MSRP"
VBDataType = vbCurrency
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 945
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "Item.PriceLowerBound"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Lo Bound"
VBDataType = vbCurrency
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 915
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "Item.PriceUpperBound"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Hi Bound"
VBDataType = vbCurrency
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 855
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "Item.SalePrice"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Sale Price"
VBDataType = vbCurrency
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 945
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "Item.SaleStartDate"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Sale Starts"
VBDataType = vbDate
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1020
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "Item.SaleEndDate"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Sale Ends"
VBDataType = vbDate
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 930
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "Item.ReorderPoint"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Reorder Pt."
VBDataType = vbDouble
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1065
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "Item.RestockLevel"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Restock Lvl."
VBDataType = vbDouble
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1305
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "Item.LastSold"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Last Sold"
VBDataType = vbDate
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 900
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "ExtendedCost"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Extended Cost"
VBDataType = vbCurrency
Formula = "Item.Cost * Item.Quantity"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1335
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "ExtendedPrice"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Extended Price"
VBDataType = vbCurrency
Formula = "Item.Price * Item.Quantity"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1365
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "Item.Inactive"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Inactive"
VBDataType = vbBoolean
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1057
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignCenterCenter
End Column
Run item value report to get cost/price, quantity and then extended cost/price with sum.
Step to Run Report: Manager>>Report>>Items>>Value List.
Thanks
24Seven Cart
you need to run the item value report. This will give you cost, price, and quantity. Then extended cost with sum, and extended price with sum.
So Manager -> Reports -> Items -> Value List. Then just run the report. This will filter out inactive items, but you can remove that if you need.
#1
André Arnaud de Cal...
291,711
Super User 2024 Season 2
#2
Martin Dráb
230,458
Most Valuable Professional
#3
nmaenpaa
101,156
All responses (
Answers (
