Custom Report: Show Individual Sales Taxes

Hi Julian - I couldn't figure out how to display the taxes in a linear format as you show in your example, as this would require that you display rows as columns based on how the tables are structured. I googled "sql rows as columns" and "pivot" which supposedly allows you to display data in this format but I got a dbcomplevel error message when I tested the pivot query (maybe PIVOT is not supported in sql express). I'll keep digging when I have some time to see where the error emanates from, anyway in the meantime you may try these queries - they'll breakdown the tax collected but you'll see two rows per item one for each sales tax.

to see tax breakdown by transaction select transactionnumber, description, tax from taxentry left join tax on taxentry.taxid = tax.id

to see tax breakdown by item select transactionentry.transactionnumber, itemlookupcode, item.description, transactionentry.price, taxentry.taxid, taxentry.tax from transactionentry left join item on transactionentry.itemid = item.id left join taxentry on transactionentry.id = taxentry.transactionentryid

Akber the RMS Gurus may know how to do this type of report - I would also be interested in getting it...

Thank you both for your help. I'm still looking into the SQL. I guess I should have mentioned that I need it grouped by CATEGORY. My finance dept needs to strip out GST and PST in order to put it into a seperate bank account. Therefore they need to see "total sales", but also "Gst" for that category, and "Pst" for that category.

dept : category : sales : gst : pst :

--------------------------------------- tix : pop : $100 : $6.50 : $7 : tix : rock : $200 : $13 : $14 : tix : class : $300 : $26 : $28 :

"Danny" wrote:

This might help someone:

/*

Author: Julian Bara Date: 27th November 2007 Comments: Show tax breakdown for daily sales

-- select database USE MSRMS

-- declare variables DECLARE @datStartDate as smalldatetime DECLARE @datEndDate as smalldatetime

-- initialise variables SET @datStartDate = '2007-11-18 00:00:00.000' SET @datEndDate = '2007-11-20 00:00:00.000'

-- get results SELECT tax.Description as [TaxID Description], SUM(taxentry.tax) [Total Tax by TaxID] FROM transactionentry LEFT JOIN item ON transactionentry.itemid = item.id LEFT JOIN taxentry ON transactionentry.id = taxentry.transactionentryid LEFT JOIN tax ON tax.ID = taxentry.TaxID WHERE transactionentry.transactionnumber IN ( -- get all transaction numbers from that day. SELECT DISTINCT TransactionEntry.TransactionNumber FROM TransactionEntry INNER JOIN [Transaction] WITH(NOLOCK) ON TransactionEntry.TransactionNumber = [Transaction].TransactionNumber LEFT JOIN TaxEntry ON TaxEntry.TransactionNumber TransactionEntry.TransactionNumber WHERE [Transaction].Time > @datStartDate AND [Transaction].Time < @datEndDate AND [Transaction].StoreID = '0' -- University Life Store ) AND tax.description is not null GROUP BY tax.Description ORDER BY tax.Description