How are the tables linked?

Did you verify database?

Are there fields besides State and ZipCode from PR_EMPLOYEEPIIWrk. on the report?

The error means you are not accessing the PR_EMPLOYEEPIIWrk. table. 

John Nichols

Sage

Thank you for your input John.

Yes, I verified the database.  It was successful.

Yes, employee first and last name, address line 1, and city are on the report.  They can only come from PR_EMPLOYEEPIIWrk.

Interestingly, I found that the standard employee name and address listing comes up blank.  I am able to view each employee on the screen, and I did just to confirm that there was a state and zip code value for each employee.

Hi, the standard employee name and address listing should not return blank when run from the Sage 100 menu. If you can view the employee data on screen but not on the reports check and see if ODBC security is set on those employee fields.  From windows explorer browse to \MAS90\MAS_ABC\PRABC (where ABC is your company code) and delete all instances of PR_EMPLOYEEPIIWrkXXXXXXXXXX this XXX's refer to the session information.  If you can attach your .RPT to this thread I'll take a look. 

John Nichols

Sage

The ODBC Security tab doesn't respond when I checked the various Payroll module options in Role Maintenance. I'm not sure if this means it is inactive.

There were no instances of the PR_EMPLOYEEIIWrkXXX file in the path you described.

I'm happy to attach the .rpt file but there doesn't seem to be a way to do it in this thread.

Thank you again.

Hi Select Insert then Insert/Video/File

PR BPA CUSTOM.rar

Thank you.

It may be the RAR is being stripped by internal controls before I can extract it. Can you send the file compressed as a ZIP or just the plain RPT without saved data. 

Thanks John

Try this.

https://drive.google.com/drive/folders/1vI52eLXLOKmraDw4orFNCRI2kfnUypIU?usp=sharing

Let me know that you received it.  Thank you.

Sorry user error on my part. I have the .RPT from the original RAR file.  I need to load up some 2019 data and test it. Open up Report options

Check Convert Database NULL values to Default

Uncheck Display Alerts on Refresh

Uncheck Select Distinct Records.

I'll spend some more time on this tomorrow. Thanks John

Wow!  Thank you very much!  It worked!

Which of those settings really resolved the problem, and why?  Any idea?

Hi Robert, It is a combination of 

Check Convert Database NULL values to Default (The report contains formulas that use the isnull function, those references can throw an error when there is no default value for a null value in a record)  

Uncheck Select Distinct Records.(never, ever have this value checked in report options, if the report requires distinct records use the report selection criteria to achieve this not the report options). 

One recommendation - remove the sub-report. Subreports should only be used when there is no other way to retrieve the data.

Add the PR_PayrollHistoryDetail to the main report set your joins as below. All joins are EQUAL joins.

Copy all fields from the details section of the sub-report to the details section of the main report.

Copy all fields from the report footer section of the sub-report to the report footer section of the main report.

Remove the sub-report from the main report and save.

Performance of the report will improve dramatically.

John Nichols

Sage 

Hi Robert, It is a combination of 

Check Convert Database NULL values to Default (The report contains formulas that use the isnull function, those references can throw an error when there is no default value for a null value in a record)  

Uncheck Select Distinct Records.(never, ever have this value checked in report options, if the report requires distinct records use the report selection criteria to achieve this not the report options). 

One recommendation - remove the sub-report. Subreports should only be used when there is no other way to retrieve the data.

Add the PR_PayrollHistoryDetail to the main report set your joins as below. All joins are EQUAL joins.

Copy all fields from the details section of the sub-report to the details section of the main report.

Copy all fields from the report footer section of the sub-report to the report footer section of the main report.

Remove the sub-report from the main report and save.

Performance of the report will improve dramatically.

John Nichols

Sage 

Thank you again for your valuable input, John.  I have an open case with Sage Tech Support.  I'll let them know this resolved the problem.

I created Sage Knowledgebase KB article 105296 to cover the error and resolution.

jcnichols, please let me know of any edits needed.

support.na.sage.com/.../viewdocument.do