Detailed Sales report with Tax details

(0) Share

Report

Posted on by Community Member

I have a few reports regrading detailed sales and taxes but they all combine the various taxes into one figure. I need a report that would give me the individual tax figures not the total taxes paid.

RMS Setup - standalone SO

Taxes assigned by item
Item Taxes ( 4 defined)
Sales Tax ( 3 defined)

I would like a report that would break-down tax collected instead of giving me total sales tax collected per transaction. The stock "Detailed Sales with Taxes" report gives me the following.

Department Category trans # Comment Item Description " Qty Sold" "Sold Price" "Total (Tax Ex)" "Sale Tax" "Total Sales"

But I would like the "Sale Tax" column to be separate into three Sales Tax ie: TaxA TaxB TaxC .

Is it just a matter of me adding a few columns, any help would be appreciated.

Danny

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

Begin ReportSummary
   ReportType = reporttypeSales
   ReportTitle = "Detailed Sales with Taxes"
   PageOrientation = pageorientationLandscape
   WordWrap = False
   ShowDateTimePicker = False
   OutLineMode = True
   Groups = 1
   GroupDescription = ""
   DisplayLogo = True
   LogoFileName = "MyLogo.bmp"
   ProcedureCall = ""
   PreQuery1 = ""
   PreQuery2 = ""
   TablesQueried = <BEGIN>
     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   Register WITH(NOLOCK) ON Batch.RegisterID = Register.ID
         LEFT JOIN   Customer WITH(NOLOCK) ON [Transaction].CustomerID = Customer.ID
         LEFT JOIN   Cashier WITH(NOLOCK) ON [Transaction].CashierID = Cashier.ID
       LEFT JOIN   QuantityDiscount WITH(NOLOCK) ON TransactionEntry.QuantityDiscountID = QuantityDiscount.ID
<END>
   SelCriteria = ""
   GroupBy = ""
   SortOrder = ""
End ReportSummary

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (0)

Gerald Rothaus 2,934 on at

Like (0)

Report
Copy link

Link copied!

Only the Sales tax amount is recorded in the transaction (transaction.salestax) table and, by item, the transactionentry (transactionentry.salestax) table. you would have to derive the tax type via the item table.

I would suggest you create a view of the item table and all it's personalities (department, cat., tax type, etc), it might make the report a lot faster and easier to read your code.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

I was hoping someone already had a detailed Tax report - where the tax was broken down by sale's tax type and not just the aggregated salestax total value.

When trying to get a report to work I get confused on whether or not to take discounts into account. The transaction (transaction.salestax) table & transactionentry (transactionentry.salestax) table values should already account for any discounts that might have been applied to the items.

I agree a View is the way to go - just trying to get a rough draft working and will refine it afterwards.

Any more help would be appreciated.

Was this reply helpful? Yes No
Gerald Rothaus 2,934 on at

Like (0)

Report
Copy link

Link copied!

Hi. Please be specific about what columns you want and in what order.

-Jerry

JR Data Inc

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

The stock "Detailed Sales with Taxes" report gives me the following.

Department Category trans # Comment Item Description " Qty Sold" "Sold Price" "Total (Tax Ex)" "Sale Tax" "Total Sales"

But I would like the "Sale Tax" column to be separated into three Sales Tax ie: TaxA TaxB TaxC as per the Sale tax defined inthe database.

Thanks

Danny

Was this reply helpful? Yes No
ToddB on at

Like (0)

Report
Copy link

Link copied!

Good morning Danny,

Thank you for posting your report question.

The issue with adding the detailed tax information, onto a sales report, is that this will cause sales to be duplicated, triplicated, etc. depending on how many taxes are assigned.

For example, if you have a state tax and a county tax the sales would be duplicated (once for each tax showing on the report).

As such, we would not recommend adding the individual taxes onto the report.

Please let me know if you have any questions.

Was this reply helpful? Yes No