Dynamics GP
Dynamics GP
Organizer:
- Organized by
-
-
Sales Tax Reports with both Schedule and Detail
-
Sales Tax Reports with both Schedule and Detail
-
I am looking for a sales tax report that will give me sales period reports that include both the Tax Schedule with the Tax Detail in a sort of parent/child relationship.For example:
Tax Schedule
comprised of Tax Detail 1
Tax Detail 2
Tax Detail 3So that I can easily complete various states’ sales tax returns that are structured with
County
State rate
Local rate
Transportation rate
Tax on breathingI cannot find anything in GP that will allow me to report that way. Does it require a SQL view? Has anyone invented one yet?
Any help will be much appreciated!!
——————————
Anne Provost
Chief Financial Officer
TNR Technical, Inc.
Sanford FL
—————————— -
We do these sort of reports
We develop using dexterity and report writer and will do fixed priced custom reports as well
They are $ 1000 each plus 20% for annual maint
——————————
Gerald Clement MBA
Dynamics GP 1993, Dexterity 1995
Computer Productivity Services Inc.
cps@cpsinc.ca
——————————
——————————————- -
Hi Anne,
Are you finding the Tax Detail Transactions SmartList not enough for this? The only thing it does not have is the Tax Schedule ID, but I find that if your Tax Details and/or their Descriptions are named in a consistent way, this is enough.
As a side note, I can see there being an issue with adding the Tax Schedule to a report like this because GP does not store the Tax Schedules at the individual tax level and the actual taxes entered on a transaction may be completely different from the Tax Schedule that shows on a transaction.
——————————
Victoria Yudin
Microsoft Dynamics GP MVP 2005-2017
Flexible Solutions, Inc.
http://www.GPReportsViewer.com
http://victoriayudin.com
——————————
——————————————- -
I am open for suggestions on how to name the Tax Details so that I don’t need the Tax Schedule information. The Tax Schedule is currently identified by State-County. The Tax Details are the State, Local, Transportation, Education, etc. taxes that comprise the total taxes for each county.
Tax Schedule SC-Spartansburg
Tax Detail 1 SC-State Rate
Tax Detail 2 SC-Local
Tax Detail 3 SC-Transportation
Tax Detail 4 SC-Education
Tax Detail ETC SC-ETCSome counties only need Tax Detail 1 (State Rate) assigned to them, others need more.
Thanks.
——————————
Anne Provost
Chief Financial Officer
TNR Technical, Inc.
Sanford FL
——————————
——————————————- -
I don’t have an answer, but I am still chuckling about “Tax on breathing”. 🙂
——————————
Nancy Edwards
Accountant
H&W Computer Systems, Inc
Boise ID
——————————
——————————————- -
Anne,
You may have to use the Tax Detail Description, as there might not be enough space in the ID, but I am thinking for each tax detail that has to be in the county, you would add the county name (or abbreviation) to it. Then you could sort/group by that.
Another option is using either Extender or just SQL directly to create a mapping of what county includes what Tax Detail(s). Then you can create a custom report/SmartList that would group the taxes using this mapping.
Also, for the states where we report on sales taxes, we don’t need to separately report on counties that only get the state sales tax. So we only have one Tax Detail for the state sales tax. But if you have to report on those separately, even if they’re only getting the state tax, you may consider creating separate tax details for the state portion of each county. That would be more work for setup and changing the rate when needed, but a lot easier for reporting.
Another, completely different option if you have this going on in multiple states and it’s getting more and more complicated is to use a 3rd party like Avalara. They will calculate all of this automatically for you without any of this hassle.
——————————
Victoria Yudin
Microsoft Dynamics GP MVP 2005-2017
Flexible Solutions, Inc.
http://www.GPReportsViewer.com
http://victoriayudin.com
——————————
——————————————- -
Thanks Victoria. I’ll check those options out.
——————————
Anne Provost
Chief Financial Officer
TNR Technical, Inc.
Sanford FL
——————————
——————————————- -
I created a query that will, by document, identify the tax schedule id and each detail in the ID. The amount of the sale and tax for each of the details is also included.
With this detail you can total by schedule. Do you want the schedule just so you can determine what state is associated with the detail?——————————
Leslie Vail
Accounting Systems Consulting, Inc.
Dallas TX
——————————
——————————————- -
Nice! I want the Tax Schedule so that I can see what Tax Details I need to report for each county. Both the schedule information and the tax amounts would be awesome!
——————————
Anne Provost
Chief Financial Officer
TNR Technical, Inc.
Sanford FL
——————————
——————————————- -
Anne,
I posted the query, but it didn’t show up on the forum. Here it is again. Voids have not been filtered out.SELECT ‘RM30601’ AS DbTable
, RM30601.RMDTYPAL
, RM30601.DOCNUMBR
, RM30601.CUSTNMBR
, RM30101.TAXSCHID
, TX00102.TAXDTLID AS TXTaxDtl_ID
, RM30601.TAXDTLID AS RMTaxDtl_ID
, RM30101.ORTRXAMT
, RM30101.TAXAMNT AS RM301TaxAmnt
, RM30601.TAXAMNT AS RM601TaxAmnt
, RM30601.TAXDTSLS
, RM30601.ORTOTSLS
, RM30601.TDTTXSLS
, RM30601.ORTXSLS
, RM30601.ORTAXAMT
, RM30601.TRXSORCE
, RM30601.ACTINDX
, RM30601.STAXAMNT
, RM30601.ORSLSTAX
, RM30601.FRTTXAMT
, RM30601.ORFRTTAX
, RM30601.MSCTXAMT
, RM30601.ORMSCTAX
, RM30601.POSTED
, RM30601.SEQNUMBR
, RM30601.CURRNIDX
, RM30601.BACHNUMB
FROM RM30601
INNER JOIN RM30101
ON RM30601.CUSTNMBR = RM30101.CUSTNMBR
AND RM30601.DOCNUMBR = RM30101.DOCNUMBR
INNER JOIN TX00102
ON RM30601.TAXDTLID = TX00102.TAXDTLID
/* AND RM30101.TAXSCHID = TX00102.TAXSCHID
WHERE (RM30601.TAXDTLID <> ‘avatax’)
AND (RM30101.TAXSCHID NOT LIKE ‘%exempt%’)
AND (RM30101.TAXSCHID NOT LIKE ‘%outof%’)
AND (RM30101.TAXSCHID NOT LIKE ‘%resale%’)
*/
UNION
SELECT ‘RM10601’ AS DbTable
, RM10601.RMDTYPAL
, RM10601.DOCNUMBR
, RM10601.CUSTNMBR
, rm20101.TAXSCHID
, TX00102.TAXDTLID AS TXTaxDtl_ID
, RM10601.TAXDTLID AS RMTaxDtl_ID
, rm20101.ORTRXAMT
, rm20101.TAXAMNT AS RM301TaxAmnt
, RM10601.TAXAMNT AS RM601TaxAmnt
, RM10601.TAXDTSLS
, RM10601.ORTOTSLS
, RM10601.TDTTXSLS
, RM10601.ORTXSLS
, RM10601.ORTAXAMT
, RM10601.TRXSORCE
, RM10601.ACTINDX
, RM10601.STAXAMNT
, RM10601.ORSLSTAX
, RM10601.FRTTXAMT
, RM10601.ORFRTTAX
, RM10601.MSCTXAMT
, RM10601.ORMSCTAX
, RM10601.POSTED
, RM10601.SEQNUMBR
, RM10601.CURRNIDX
, RM10601.BACHNUMB
FROM RM10601
INNER JOIN rm20101
ON RM10601.CUSTNMBR = rm20101.CUSTNMBR
AND RM10601.DOCNUMBR = rm20101.DOCNUMBR
INNER JOIN TX00102
ON RM10601.TAXDTLID = TX00102.TAXDTLID
AND rm20101.TAXSCHID = TX00102.TAXSCHID
/*
WHERE (RM10601.TAXDTLID <> ‘avatax’)
AND (rm20101.TAXSCHID NOT LIKE ‘%exempt%’)
AND (rm20101.TAXSCHID NOT LIKE ‘%outof%’)
AND (rm20101.TAXSCHID NOT LIKE ‘%resale%’)
*/
——————————
Leslie Vail
Accounting Systems Consulting, Inc.
Dallas TX
——————————
——————————————- -
Hi,
Anne and I have modified the original query a bit and I changed the statement so that it would create a view. For anyone following along, I thought I’d attach the final (well, nothing’s ever ‘final’) copy of what we came up with.
Kind regards,
Leslie——————————
Leslie Vail
Accounting Systems Consulting, Inc.
Dallas TX
——————————
——————————————- -
Thank you again Leslie! I am now in the running with Sparkly Steve as to who is your biggest fan!!
——————————
Anne Provost
Chief Financial Officer
TNR Technical, Inc.
Sanford FL
——————————
——————————————- -
You guys are spoiling me. I love it. 🙂
——————————
Leslie Vail
Accounting Systems Consulting, Inc.
Dallas TX
——————————
——————————————-
-
-
0 Replies
Sorry, there were no replies found.
The discussion ‘Sales Tax Reports with both Schedule and Detail’ is closed to new replies.
