We need to adjust some of the ItemLookup codes to reflect "Item is Taxable" and I can't find a qrp report that shows this information so we have a list to work from. Anyone know the field name to add this to an existing report, or offer any help?
Thanks, Rick
*This post is locked for comments
Fernando... Brilliant! This was exactly what I needed! This column, added to the previous report you suggested, gave me a list of items with the "ITEM IS TAXABLE" box unchecked. Works both ways. Thank you!
Rick - I don't have access right now to a store ops database, but I think if you add this column to the report I sent you, you can filter by taxable / non-taxable. I am almost positive the column is TAXABLE, but you can confirm this by running sp_columns item to view all the columns in the item table. Hope this helps.
Begin Column
FieldName = "Item.Taxable"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Taxable"
VBDataType = vbBoolean
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1057
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignCenterCenter
End Column
Using version 2.0.1004. I will try this and post back if it works. Thanks!
or SELECT i.ItemLookupCode, i.Description, i.ExtendedDescription FROM Item i WHERE i.Taxable = 1
Make a Query on DBase of RMS
I dont know what version u have but in Version 2 u can obtain all items that pay Tax
just running this
SELECT i.ItemLookupCode, i.Description, i.ExtendedDescription FROM Item i WHERE i.TaxID<>0
Thanks Jeff and Fernando - two good responses, but I'm not sure if I'm explaining what I need correctly. In the attached screenshot, under Department and Category, the checkbox for "Item is Taxable". That's what I need to create a report on, if that box is checked, or not. Am I missing something in the information you've provided, as to how to call this out in a report?
Thanks again.
Rick - see if this works for you; will also show what tax assignment the item has. Cheers
//--- Report Summary --- //
Begin ReportSummary
ReportType = reporttypeItems
ReportTitle = "Store Operations Item Tax Assignment"
PageOrientation = pageorientationLandscape
OutLineMode = True
Groups = 1
GroupDescription = ""
DisplayLogo = True
LogoFileName = "MyLogo.bmp"
ProcedureCall = ""
TablesQueried = <BEGIN>
FROM Item WITH(NOLOCK)
LEFT JOIN ItemTax WITH(NOLOCK)
ON Item.TaxID = ItemTax.ID
LEFT JOIN Tax WITH(NOLOCK)
ON ItemTax.TaxID01 = Tax.ID
LEFT JOIN Department WITH(NOLOCK)
ON Item.DepartmentID = Department.ID
LEFT JOIN Category WITH(NOLOCK)
ON Item.CategoryID = Category.ID
<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 = "ItemTax.Description"
DrillDownFieldName = "ItemTax.Description"
DrillDownReportName = ""
Title = "Item Tax"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1365
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "ItemTax.Code"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Item Tax Code"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1365
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "Tax.Description"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Sales Tax Desc"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1365
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "Tax.Percentage"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Tax %"
VBDataType = vbDouble
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1000
GroupMethod = groupmethodNone
ColFormat = "#.###"
End Column
Begin Column
FieldName = "Department.Name"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Department"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1560
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "Category.Name"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Category"
VBDataType = vbString
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1560
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.Cost"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Cost"
VBDataType = vbCurrency
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1100
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "Item.Price"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Price"
VBDataType = vbCurrency
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1100
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "Item.Quantity"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "On Hand"
VBDataType = vbDouble
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 800
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "Item.DateCreated"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Date Created"
VBDataType = vbDate
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1000
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 = 1000
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Rick,
Couple of different ways to see this.
HQ Manager | Maintenance mode | Wizards | 320 Adjust Item Sales Tax | Item Filter button | Filter on Tax <> <Not Assigned> for items with Tax or = <Not Assigned> for items without tax
OR
Item Snapshot List report | Filter on Tax <> <Not Assigned>
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,912 Super User 2024 Season 2
Martin Dráb 229,355 Most Valuable Professional
nmaenpaa 101,156