Hi, I'm looking for the script to add the field "last sold" to a report in HQ. From what I understand this would need to reference the Item Dynamics table for each store.
Here is the report we are starting with:
//--- Report Summary --- //
Begin ReportSummary
ReportType = reporttypeItems
ReportTitle = "HQ REORDER/RESTOCK"
PageOrientation = pageorientationLandscape
OutLineMode = True
Groups = 1
GroupDescription = "Total"
DisplayLogo = True
LogoFileName = "MyLogo.bmp"
ProcedureCall = ""
TablesQueried = <BEGIN>
FROM Item WITH(NOLOCK)
INNER JOIN Department WITH(NOLOCK) ON Item.DepartmentID=Department.ID
INNER JOIN Category WITH(NOLOCK) ON Item.CategoryID=Category.ID
LEFT JOIN Supplier WITH(NOLOCK) ON Item.SupplierID = Supplier.ID
LEFT JOIN ItemDynamic AS S1 WITH(NOLOCK) ON S1.ItemID = Item.ID AND S1.StoreID=1
LEFT JOIN ItemDynamic AS S2 WITH(NOLOCK) ON S2.ItemID = Item.ID AND S2.StoreID=2
LEFT JOIN ItemDynamic AS S3 WITH(NOLOCK) ON S3.ItemID = Item.ID AND S3.StoreID=3
LEFT JOIN ItemDynamic AS S4 WITH(NOLOCK) ON S4.ItemID = Item.ID AND S4.StoreID=4
LEFT JOIN ItemDynamic AS S5 WITH(NOLOCK) ON S5.ItemID = Item.ID AND S5.StoreID=5
LEFT JOIN ItemDynamic AS S6 WITH(NOLOCK) ON S6.ItemID = Item.ID AND S6.StoreID=6
LEFT JOIN ItemDynamic AS S7 WITH(NOLOCK) ON S7.ItemID = Item.ID AND S7.StoreID=7
LEFT JOIN ItemDynamic AS S8 WITH(NOLOCK) ON S8.ItemID = Item.ID AND S8.StoreID=8
LEFT JOIN ItemDynamic AS S10 WITH(NOLOCK) ON S10.ItemID = Item.ID AND S10.StoreID=10
<END>
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 = "As Of: <Report Date>"
Font = "Arial"
FontBold = True
FontSize = 10
Color = "Black"
End TitleRow
//--- Filters ---//
//--- Columns ---//
Begin Column
FieldName = "Department.Name"
DrillDownFieldName = "Department.Name"
DrillDownReportName = ""
Title = "Dept. Name"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 2895
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "Category.Name"
DrillDownFieldName = "Category.Name"
DrillDownReportName = ""
Title = "Cat. Name"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 2820
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "Supplier.SupplierName"
DrillDownFieldName = "Supplier.SupplierName"
DrillDownReportName = ""
Title = "Supplier"
VBDataType = vbString
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 825
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "Item.ItemLookUpCode"
DrillDownFieldName = "Item.ItemLookUpCode"
DrillDownReportName = ""
Title = "LookUp Code"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1245
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "Item.Description"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Description"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1600
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "Item.Price"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Price"
VBDataType = vbCurrency
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 765
GroupMethod = groupmethodAverage
ColFormat = ""
End Column
Begin Column
FieldName = "Item.Quantity"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "On-Hand"
VBDataType = vbDouble
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1080
GroupMethod = groupmethodSum
ColFormat = "#.##"
End Column
Begin Column
FieldName = "S1.SnapShotTime"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "South SnapShot Date"
VBDataType = vbDate
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1395
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "S1.SnapShotQuantity"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "South Quantity"
VBDataType = vbDouble
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1000
GroupMethod = groupmethodSum
ColFormat = "#.##"
End Column
Begin Column
FieldName = "S1.SnapShotQuantityCommitted"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "South Committed"
VBDataType = vbDouble
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1200
GroupMethod = groupmethodSum
ColFormat = "#.##"
End Column
Begin Column
FieldName = "S1.SnapShotReorderPoint"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "South Reorder"
VBDataType = vbDouble
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1100
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "S1.SnapShotRestockLevel"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "South Restock"
VBDataType = vbDouble
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1100
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "S2.SnapShotTime"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "West SnapShot Date"
VBDataType = vbDate
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1395
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "S2.SnapShotQuantity"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "West Quantity"
VBDataType = vbDouble
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1000
GroupMethod = groupmethodSum
ColFormat = "#.##"
End Column
Begin Column
FieldName = "S2.SnapShotQuantityCommitted"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "West Committed"
VBDataType = vbDouble
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1200
GroupMethod = groupmethodSum
ColFormat = "#.##"
End Column
Begin Column
FieldName = "S2.SnapShotReorderPoint"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "West Reorder"
VBDataType = vbDouble
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1100
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "S2.SnapShotRestockLevel"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "West Restock"
VBDataType = vbDouble
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1100
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "S3.SnapShotQuantity"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Mikes Quantity"
VBDataType = vbDouble
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1000
GroupMethod = groupmethodSum
ColFormat = "#.##"
End Column
This continues on for all 9 of our "stores" (two are stores, seven are small "warehouses" for backup stock)
Thanks!
Devon
*This post is locked for comments
Hi Upendra Nath M.
You can use the "CASE" tag in formula field. For example, the field can be something like this next:
Formula = "CASE [FieldName] WHEN Null THEN 0 ELSE [FieldName] END"
Also, You can use tSQL function ISNULL, and assign a 0 or by default value when your field is 0...
Hope this helps, A.
Hello Devon,
Thanks, but there are lot of NULL values, how to handle them?
Thanks & Regards,
Upen
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