Sage 100

I'm working on a crystal report for a client regarding state unemployment wages.

I would like to have the report pull in the taxable wage base for the state automatically based on the quarter I have selected..  The quarterly tax reports do pull that info in and display it but I'm stumped trying to find it in the database.  I've hunted and hunted through the data files but so far have come up blank.

I've seen some suggestions that this value is not saved on the local Sage system, but is pulled in from some server somewhere therefore making it inaccessible to Crystal.

Someone please tell me I'm wrong.

------------------------------
Thomas Rogers (TomTarget)
Target System Technology, Inc.
Spokane Valley WA
------------------------------

Hi Thomas.  The data is in PR_EmployeeTaxHistory.m4t.  You will need to look for the State record, for the end of the quarter OR the current month, and the taxable SUI wages is on line 20.


------------------------------
Michelle Taylor
ERP Consulting Manager, CS3 Technology
918-388-9772
------------------------------

I understand he is looking for the limit in effect for that period. 

Use a Report Setting on the Quarterly Tax Report and modify it instead of starting from scratch. 


------------------------------
Eric Lunceford
First Mate Business Solutions
Oklahoma City, OK
877-880-8960
https://www.firstmatellc.com/
------------------------------

Original Message:
Sent: 03-18-2020 15:23
From: Michelle Taylor
Subject: Getting state taxable wage baes into a crystal report

Hi Thomas.  The data is in PR_EmployeeTaxHistory.m4t.  You will need to look for the State record, for the end of the quarter OR the current month, and the taxable SUI wages is on line 20.

------------------------------
Michelle Taylor
ERP Consulting Manager, CS3 Technology
918-388-9772
------------------------------

I think you have the right idea Eric.  The fun will be in stripping out all the unneeded crap.

This really wasn't a complicated thing in the legacy payroll.

------------------------------
Thomas Rogers (TomTarget)
Target System Technology, Inc.
Spokane Valley WA
------------------------------

Original Message:
Sent: 03-18-2020 16:37
From: Eric Lunceford
Subject: Getting state taxable wage baes into a crystal report

I understand he is looking for the limit in effect for that period.

Use a Report Setting on the Quarterly Tax Report and modify it instead of starting from scratch.


------------------------------
Eric Lunceford
First Mate Business Solutions
Oklahoma City, OK
877-880-8960
https://www.firstmatellc.com/
------------------------------


Original Message:
Sent: 03-18-2020 15:23
From: Michelle Taylor
Subject: Getting state taxable wage baes into a crystal report

Hi Thomas.  The data is in PR_EmployeeTaxHistory.m4t.  You will need to look for the State record, for the end of the quarter OR the current month, and the taxable SUI wages is on line 20.

------------------------------
Michelle Taylor
ERP Consulting Manager, CS3 Technology
918-388-9772

Dang.  The canned report contains the calculated data for the "capped" wages,  but it doesn't contain the data for the gross wages subject to UI.  It does have gross wages in total,  but in Washington state there are some exceptions for certain wages that are not included in unemployment wages, so it is not a guaranteed that would be the correct number.

------------------------------
Thomas Rogers (TomTarget)
Target System Technology, Inc.
Spokane Valley WA
------------------------------

Original Message:
Sent: 03-18-2020 16:37
From: Eric Lunceford
Subject: Getting state taxable wage baes into a crystal report

I understand he is looking for the limit in effect for that period.

Use a Report Setting on the Quarterly Tax Report and modify it instead of starting from scratch.


------------------------------
Eric Lunceford
First Mate Business Solutions
Oklahoma City, OK
877-880-8960
https://www.firstmatellc.com/
------------------------------


Original Message:
Sent: 03-18-2020 15:23
From: Michelle Taylor
Subject: Getting state taxable wage baes into a crystal report

Hi Thomas.  The data is in PR_EmployeeTaxHistory.m4t.  You will need to look for the State record, for the end of the quarter OR the current month, and the taxable SUI wages is on line 20.

------------------------------
Michelle Taylor
ERP Consulting Manager, CS3 Technology
918-388-9772

Thanks for the reply.  

That was my first thought as well.

However my problem seems to be that the data field you mention seems to be giving you all wages that could be subject to the unemployment tax, not just the taxable amount.  For example:  If the employee is paid $60,000 and the wage limit is $50,000 it contains $60,000K.  

Client wants a report that shows the total wages that could be subject to tax regardless of whether it goes over the base (no problem here) and the actual wages taxes (basically limited by the base). and then the excess wages.   Legacy payroll had the easily available in a report so I'm trying to recreate that report for them.

I could have the client input the wage base as a parameter,  but I think it would be much nicer to be able to pull the base automatically from the tax tables, but so far I have been unsuccessful finding it.

------------------------------
Thomas Rogers (TomTarget)
Target System Technology, Inc.
Spokane Valley WA
------------------------------

Original Message:
Sent: 03-18-2020 15:23
From: Michelle Taylor
Subject: Getting state taxable wage baes into a crystal report

Hi Thomas.  The data is in PR_EmployeeTaxHistory.m4t.  You will need to look for the State record, for the end of the quarter OR the current month, and the taxable SUI wages is on line 20.

------------------------------
Michelle Taylor
ERP Consulting Manager, CS3 Technology
918-388-9772
------------------------------

Got it.  You start with PR_TAXGROUPDETAIL and find the tax group and tax code.  From there this is TAXRULE that you link to PR_TAXRATEHISTORY.

Only found it by working backwards from what I knew was the limit for the year.

------------------------------
Thomas Rogers (TomTarget)
Target System Technology, Inc.
Spokane Valley WA
------------------------------

Original Message:
Sent: 03-18-2020 16:44
From: Thomas Rogers
Subject: Getting state taxable wage baes into a crystal report

Thanks for the reply.

That was my first thought as well.

However my problem seems to be that the data field you mention seems to be giving you all wages that could be subject to the unemployment tax, not just the taxable amount.  For example:  If the employee is paid $60,000 and the wage limit is $50,000 it contains $60,000K.

Client wants a report that shows the total wages that could be subject to tax regardless of whether it goes over the base (no problem here) and the actual wages taxes (basically limited by the base). and then the excess wages.   Legacy payroll had the easily available in a report so I'm trying to recreate that report for them.

I could have the client input the wage base as a parameter,  but I think it would be much nicer to be able to pull the base automatically from the tax tables, but so far I have been unsuccessful finding it.

------------------------------
Thomas Rogers (TomTarget)
Target System Technology, Inc.
Spokane Valley WA
------------------------------


Original Message:
Sent: 03-18-2020 15:23
From: Michelle Taylor
Subject: Getting state taxable wage baes into a crystal report

Hi Thomas.  The data is in PR_EmployeeTaxHistory.m4t.  You will need to look for the State record, for the end of the quarter OR the current month, and the taxable SUI wages is on line 20.

------------------------------
Michelle Taylor
ERP Consulting Manager, CS3 Technology
918-388-9772

Slightly off the original topic but it has to do with the PR_EmployeeTaxHistory.m4t @Michelle Taylor mentions above.  Client is a multistate employer and is having reconciliation issues for the quarter.  This is her first quarter end using the new PR and quarterly tax report.  The new report has changed causing reconciliation unrest.  So dumped the data into Excel and immediately noticed that gross wages for the three states do not total to the Federal gross wages.  I am at a loss as why.  Shouldn't Federal gross wages equal the sum of the states?



​

------------------------------
Jeff Schwenk
FORMER 90M Board Member
Bottomline Software, Inc.
Waynesboro VA
540-221-4444
------------------------------

Original Message:
Sent: 03-18-2020 17:40
From: Thomas Rogers
Subject: Getting state taxable wage baes into a crystal report

Got it.  You start with PR_TAXGROUPDETAIL and find the tax group and tax code.  From there this is TAXRULE that you link to PR_TAXRATEHISTORY.

Only found it by working backwards from what I knew was the limit for the year.

------------------------------
Thomas Rogers (TomTarget)
Target System Technology, Inc.
Spokane Valley WA
------------------------------


Original Message:
Sent: 03-18-2020 16:44
From: Thomas Rogers
Subject: Getting state taxable wage baes into a crystal report

Thanks for the reply.

That was my first thought as well.

However my problem seems to be that the data field you mention seems to be giving you all wages that could be subject to the unemployment tax, not just the taxable amount.  For example:  If the employee is paid $60,000 and the wage limit is $50,000 it contains $60,000K.

Client wants a report that shows the total wages that could be subject to tax regardless of whether it goes over the base (no problem here) and the actual wages taxes (basically limited by the base). and then the excess wages.   Legacy payroll had the easily available in a report so I'm trying to recreate that report for them.

I could have the client input the wage base as a parameter,  but I think it would be much nicer to be able to pull the base automatically from the tax tables, but so far I have been unsuccessful finding it.

------------------------------
Thomas Rogers (TomTarget)
Target System Technology, Inc.
Spokane Valley WA


Original Message:
Sent: 03-18-2020 15:23
From: Michelle Taylor
Subject: Getting state taxable wage baes into a crystal report

Hi Thomas.  The data is in PR_EmployeeTaxHistory.m4t.  You will need to look for the State record, for the end of the quarter OR the current month, and the taxable SUI wages is on line 20.

------------------------------
Michelle Taylor
ERP Consulting Manager, CS3 Technology
918-388-9772

It is possible to have a Tax Profile for only a State and not Federal, and vice versa.  Might that be the case?

------------------------------
Michelle Taylor
ERP Consulting Manager, CS3 Technology
918-388-9772
------------------------------

Original Message:
Sent: 03-31-2020 15:32
From: Jeff Schwenk
Subject: Getting state taxable wage baes into a crystal report

Slightly off the original topic but it has to do with the PR_EmployeeTaxHistory.m4t @Michelle Taylor mentions above.  Client is a multistate employer and is having reconciliation issues for the quarter.  This is her first quarter end using the new PR and quarterly tax report.  The new report has changed causing reconciliation unrest.  So dumped the data into Excel and immediately noticed that gross wages for the three states do not total to the Federal gross wages.  I am at a loss as why.  Shouldn't Federal gross wages equal the sum of the states?

​

------------------------------
Jeff Schwenk
FORMER 90M Board Member
Bottomline Software, Inc.
Waynesboro VA
540-221-4444
------------------------------


Original Message:
Sent: 03-18-2020 17:40
From: Thomas Rogers
Subject: Getting state taxable wage baes into a crystal report

Got it.  You start with PR_TAXGROUPDETAIL and find the tax group and tax code.  From there this is TAXRULE that you link to PR_TAXRATEHISTORY.

Only found it by working backwards from what I knew was the limit for the year.

------------------------------
Thomas Rogers (TomTarget)
Target System Technology, Inc.
Spokane Valley WA


Original Message:
Sent: 03-18-2020 16:44
From: Thomas Rogers
Subject: Getting state taxable wage baes into a crystal report

Thanks for the reply.

That was my first thought as well.

However my problem seems to be that the data field you mention seems to be giving you all wages that could be subject to the unemployment tax, not just the taxable amount.  For example:  If the employee is paid $60,000 and the wage limit is $50,000 it contains $60,000K.

Client wants a report that shows the total wages that could be subject to tax regardless of whether it goes over the base (no problem here) and the actual wages taxes (basically limited by the base). and then the excess wages.   Legacy payroll had the easily available in a report so I'm trying to recreate that report for them.

I could have the client input the wage base as a parameter,  but I think it would be much nicer to be able to pull the base automatically from the tax tables, but so far I have been unsuccessful finding it.

------------------------------
Thomas Rogers (TomTarget)
Target System Technology, Inc.
Spokane Valley WA


Original Message:
Sent: 03-18-2020 15:23
From: Michelle Taylor
Subject: Getting state taxable wage baes into a crystal report

Hi Thomas.  The data is in PR_EmployeeTaxHistory.m4t.  You will need to look for the State record, for the end of the quarter OR the current month, and the taxable SUI wages is on line 20.

------------------------------
Michelle Taylor
ERP Consulting Manager, CS3 Technology
918-388-9772

Hmmm.

they do have tax profiles where IA is for SIT and SD is SUI only

------------------------------
Jeff Schwenk
FORMER 90M Board Member
Bottomline Software, Inc.
Waynesboro VA
540-221-4444
------------------------------

Original Message:
Sent: 03-31-2020 16:12
From: Michelle Taylor
Subject: Getting state taxable wage baes into a crystal report

It is possible to have a Tax Profile for only a State and not Federal, and vice versa.  Might that be the case?

------------------------------
Michelle Taylor
ERP Consulting Manager, CS3 Technology
918-388-9772
------------------------------


Original Message:
Sent: 03-31-2020 15:32
From: Jeff Schwenk
Subject: Getting state taxable wage baes into a crystal report

Slightly off the original topic but it has to do with the PR_EmployeeTaxHistory.m4t @Michelle Taylor mentions above.  Client is a multistate employer and is having reconciliation issues for the quarter.  This is her first quarter end using the new PR and quarterly tax report.  The new report has changed causing reconciliation unrest.  So dumped the data into Excel and immediately noticed that gross wages for the three states do not total to the Federal gross wages.  I am at a loss as why.  Shouldn't Federal gross wages equal the sum of the states?

​

------------------------------
Jeff Schwenk
FORMER 90M Board Member
Bottomline Software, Inc.
Waynesboro VA
540-221-4444


Original Message:
Sent: 03-18-2020 17:40
From: Thomas Rogers
Subject: Getting state taxable wage baes into a crystal report

Got it.  You start with PR_TAXGROUPDETAIL and find the tax group and tax code.  From there this is TAXRULE that you link to PR_TAXRATEHISTORY.

Only found it by working backwards from what I knew was the limit for the year.

------------------------------
Thomas Rogers (TomTarget)
Target System Technology, Inc.
Spokane Valley WA


Original Message:
Sent: 03-18-2020 16:44
From: Thomas Rogers
Subject: Getting state taxable wage baes into a crystal report

Thanks for the reply.

That was my first thought as well.

However my problem seems to be that the data field you mention seems to be giving you all wages that could be subject to the unemployment tax, not just the taxable amount.  For example:  If the employee is paid $60,000 and the wage limit is $50,000 it contains $60,000K.

Client wants a report that shows the total wages that could be subject to tax regardless of whether it goes over the base (no problem here) and the actual wages taxes (basically limited by the base). and then the excess wages.   Legacy payroll had the easily available in a report so I'm trying to recreate that report for them.

I could have the client input the wage base as a parameter,  but I think it would be much nicer to be able to pull the base automatically from the tax tables, but so far I have been unsuccessful finding it.

------------------------------
Thomas Rogers (TomTarget)
Target System Technology, Inc.
Spokane Valley WA


Original Message:
Sent: 03-18-2020 15:23
From: Michelle Taylor
Subject: Getting state taxable wage baes into a crystal report

Hi Thomas.  The data is in PR_EmployeeTaxHistory.m4t.  You will need to look for the State record, for the end of the quarter OR the current month, and the taxable SUI wages is on line 20.

------------------------------
Michelle Taylor
ERP Consulting Manager, CS3 Technology
918-388-9772