Sale tax

Hi Gill

Below is the syntax of the detailed sales report I edited to show both tax collected on the sale and current item tax assigned. Save it as a custom report - you can filter for zero tax dollars collected to see sales of items that did not charge sales tax. Hope this helps...

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

Begin ReportSummary ReportType = reporttypeSales ReportTitle = "Detailed Sales with Sales Tax Collected" PageOrientation = pageorientationLandscape WordWrap = False ShowDateTimePicker = False OutLineMode = True Groups = 1 GroupDescription = "" DisplayLogo = True LogoFileName = "MyLogo.bmp" ProcedureCall = "" PreQuery1 = "" PreQuery2 = "" TablesQueried FROM TransactionEntry INNER JOIN [Transaction] WITH(NOLOCK) ON TransactionEntry.TransactionNumber [Transaction].TransactionNumber INNER JOIN Batch WITH(NOLOCK) ON [Transaction].BatchNumber = Batch.BatchNumber LEFT JOIN Item WITH(NOLOCK) ON TransactionEntry.ItemID = Item.ID 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 LEFT JOIN ReasonCode AS ReasonCodeDiscount WITH(NOLOCK) ON TransactionEntry.DiscountReasonCodeID ReasonCodeDiscount.ID LEFT JOIN ReasonCode AS ReasonCodeTaxChange WITH(NOLOCK) ON TransactionEntry.TaxChangeReasonCodeID ReasonCodeTaxChange.ID LEFT JOIN ReasonCode AS ReasonCodeReturn WITH(NOLOCK) ON TransactionEntry.ReturnReasonCodeID = ReasonCodeReturn.ID LEFT JOIN Shipping WITH(NOLOCK) ON [Transaction].TransactionNumber Shipping.TransactionNumber LEFT JOIN Cashier WITH(NOLOCK) ON [Transaction].CashierID = Cashier.ID LEFT JOIN ItemTax WITH(NOLOCK) ON Item.TaxID = ItemTax.ID LEFT JOIN Tax WITH(NOLOCK) ON ItemTax.TaxID01 = Tax.ID

SelCriteria = "" GroupBy = "" SortOrder = "" End ReportSummary

//--- Title Rows ---//

Begin TitleRow Text = "" Font = "Arial" FontBold = True FontSize = 16 Color = "Blue" End TitleRow

Begin TitleRow Text = "" Font = "Arial" FontBold = True FontSize = 12 Color = "Black" End TitleRow

Begin TitleRow Text = "Generated On " Font = "Arial" FontBold = True FontSize = 10 Color = "Black" End TitleRow

//--- Filters ---//

Begin Filter FieldName = "[Transaction].Time" FilterOp = reportfilteropBetween FilterLoLim = "" FilterHilim = "" 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 = 1515 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 = 825 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column

Begin Column FieldName = "Item.ItemLookupCode" DrillDownFieldName = "Item.ItemLookupCode" DrillDownReportName = "" Title = "Item Lookup" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1380 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column

Begin Column FieldName = "Item.Description" DrillDownFieldName = "Item.Description" DrillDownReportName = "" Title = "Description" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 2315 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column

Begin Column FieldName = "Item.Quantity" DrillDownFieldName = "" DrillDownReportName = "" Title = "On Hand" VBDataType = vbDouble Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 810 GroupMethod = groupmethodNone ColFormat = "#.#" End Column

Begin Column FieldName = "Item.Price" DrillDownFieldName = "" DrillDownReportName = "" Title = "Price" VBDataType = vbCurrency Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 555 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 = "TransactionEntry.Quantity" DrillDownFieldName = "" DrillDownReportName = "" Title = "Qty Sold" VBDataType = vbDouble Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 900 GroupMethod = groupmethodSum ColFormat = "#.##" ColAlignment = flexAlignCenterCenter End Column

Begin Column FieldName = "TransactionEntry.Price" DrillDownFieldName = "" DrillDownReportName = "" Title = "Sold Price" VBDataType = vbCurrency Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1050 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignCenterCenter End Column

Begin Column FieldName = "TransactionEntry.SalesTax" DrillDownFieldName = "" DrillDownReportName = "" Title = "Tax $" VBDataType = vbCurrency Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 800 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignCenterCenter End Column

Begin Column FieldName = "SalesTaxPercent" DrillDownFieldName = "" DrillDownReportName = "" Title = "% Tax" VBDataType = vbLong Formula = "CASE WHEN TransactionEntry.Price = 0 THEN 0 ELSE (TransactionEntry.SalesTax/(TransactionEntry.Price*TransactionEntry.Quantity)) END" ColHidden = False ColNotDisplayable = False FilterDisabled = True ColWidth = 800 GroupMethod = groupmethodNone ColFormat = "#.#%" ColAlignment = flexAlignCenterCenter End Column

Begin Column FieldName = "TransactionEntry.Cost" DrillDownFieldName = "" DrillDownReportName = "" Title = "Sold Cost" VBDataType = vbCurrency Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 1250 GroupMethod = groupmethodSum ColFormat = "" End Column

Begin Column FieldName = "Total" DrillDownFieldName = "" DrillDownReportName = "" Title = "Total Sales Pre-Tax" VBDataType = vbCurrency Formula = "TransactionEntry.Price * TransactionEntry.Quantity" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 1800 GroupMethod = groupmethodSum ColFormat = "" End Column

Begin Column FieldName = "[Transaction].TransactionNumber" DrillDownFieldName = "[Transaction].TransactionNumber" DrillDownReportName = "" Title = "Trans#" VBDataType = vbLong Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 800 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignCenterCenter End Column

Begin Column FieldName = "[Transaction].Time" DrillDownFieldName = "" DrillDownReportName = "" Title = "Date Sold" VBDataType = vbDate Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 900 GroupMethod = groupmethodNone ColFormat = "" End Column

Begin Column FieldName = "ReasonCodeTaxChange.Description" DrillDownFieldName = "Reasoncode.Description" DrillDownReportName = "" Title = "Tax Change Reason Code" VBDataType = vbString Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 2280 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column

Begin Column FieldName = "Cashier.Name" DrillDownFieldName = "" DrillDownReportName = "" Title = "Cashier" VBDataType = vbString Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 2080 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column

Begin Column FieldName = "ItemTax.Description" DrillDownFieldName = "ItemTax.Description" DrillDownReportName = "" Title = "Current Item Tax" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1600 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignCenterCenter End Column

Begin Column FieldName = "Tax.Description" DrillDownFieldName = "Tax.Description" DrillDownReportName = "" Title = "Current Sales Tax1" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1680 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignCenterCenter End Column

Begin Column FieldName = "Tax.Percentage" DrillDownFieldName = "" DrillDownReportName = "" Title = "Curr ST1%" VBDataType = vbDouble Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 980 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignCenterCenter End Column

"Gill" wrote: