https://us-kb.sage.com/portal/app/portlets/results/viewsolution.jsp?solutionid=225924450088325&page=1&position=0&q=225924450088325

How to display Personally Identifiable Information (PII) fields on Custom Crystal Reports in Sage 100 2018 and higher

Please mark this as a verified answer if your question has been answered.

Greetings,

The user needs to have a role assigned that includes the appropriate Security Option to see the encrypted data on standard Sage 100 reports.

For custom reports, the report needs to be added to the Sage 100 Menu via Report Manager (Library Master Setup Menu).  When adding the report to the menu, select the appropriate PII worktable to be included. Please note that this will only include the PII worktable in the Crystal Report, the specific fields will still need to be added to the report from the PII worktable(s).

The custom report will also need to be run in Sage 100 by a user with a role that includes the appropriate Security Option.

Reports run outside of Sage 100 via an ODBC connection will not have the ability to print unencrypted data.

As the question refers to both Taxpayer IDs and SSNs, I am guessing this is for A/P Vendor 1099 information. If so, the unmasked values can be included in the standard Sage 100 Vendor Listing if the user's role includes the A/P Security Option "Allow Viewing, Printing, and Editing of Highly Sensitive 1099 Information".  A checkbox titled "Print Unmasked TaxpayerID/SSN" will appear allowing the data to be included. 

For a custom report, include the AP_VendorPIIWrk table when adding it to the Sage 100 menu via Report Manager.

Thank you,

Kent

Thank you for the detailed answer, very informative.

Kent - In the 2023 Premium edition of Sage 100, this process doesn't work in the A/P module.  Adding AP_VendorPIIWrk to the custom report when adding to the menu always results in the following error message:

The database login failed. Error in File AP_custom 12700_13000_{A5C8CDDF-1B07-43A8-A523-7C03FD14D87E}.rpt: The table could not be
found.

If we remove the AP_VendorPIIWrk file from the crystal report, it will run, but we won't get the Tax ID number to print on the report.

Is there an issue with the report generation process when using SQL vs ProvideX that is not properly creating and populating this temp table?

Please make sure the current company selected in Sage 100 2023 Premium has A/P setup.  Then retry adding the PII worktable. 

There was a change made in Sage 100 2024 for the Add Report to Menu wizard to only display PII worktables for modules that are setup in the current company.

If that doesn't work, then please contact Sage 100 Customer Support.

Thanks,

Kent

Thanks for the speedy response Kent. 

Looks like we'll need to contact support, since this is a problem with all of our client's installations but only in the AP.  Doesn't seem to be a problem with G/L or Payroll.

Did you confirm the Role Maintenance AP / Security tab task is selected to "Allow Viewing, Printing, and Editing of Highly Sensitive 1099 information?

Greetings,

I had some time this morning to test adding a Custom Report on a Sage 100 Premium 2023 install.  I was not able to duplicate your error.

I created an AP_VendorListingCUSTOM.rpt and when adding it to the Sage 100 Menu via Report Manager, I was able to include the AP_VendorPIIWrk table.

Looking at the error you are getting, I wanted to confirm that CUSTOM is at the end of the report name just before the ".rpt".  In the error, it shows AP_custom 12700_13000_.  Please make sure that the report name ends with uppercase CUSTOM.rpt.

Thanks,
Kent

I am experiencing this same issue with one of my clients on Premium v2023.  In their case, they are printing a customized Report Setting of the Payment History report which ran correctly in their old version.  Now, the TIN is encrypted, so I added the AP_VendorPII_wrk table and get the same error as TPeterson reported.  Did you get any help from Sage on it?  

Yes, the AP module is set up.

When creating a new Report Setting, there is no where to tell it to add the work table, so I added it manually.  

Yes, "Allow Viewing, Printing, and Editing of Highly Sensitive 1099 information?" is selected in Role Maintenance.

There are no instructions on how to add the PII Wrk tables correctly to a custom report that's created through the Report Settings, instead of a standalone custom report.  In this client's case, this report is exactly what they need, and they just needed to add a few informational fields from the vendor, so there was no reason to start from scratch.  Until now?

Greetings,

The report needs to be added to the menu in Sage 100 and cannot be run outside of the Sage 100 Launcher. You should not need to start from scratch.  If you already have a custom report, you can use Report Manager to add the PII Wrk table to the report.  Below is part of the Report Manager help:

Use Report Manager to manage all forms and reports. Select a form or report to customize using the SAP Crystal Reports Designer.

The Report Manager window lists all modules that have forms or reports in a tree view. Expand the module to select the available forms, reports, and custom reports. All forms, reports, and custom reports are listed in alphabetical order.

You can also add and delete custom reports. When you click Add Report to Menu, the Menu Wizard opens and guides you through the process.

Note: If you're adding a report for a module that includes one or more PII worktables, you'll have the option to include the worktables in the wizard. These worktables allow you to include personally identifiable information (PII), such as names and social security numbers, in the report. If you include one or more of these worktables, they will be populated with unencrypted data when the report is run from the Custom Reports menu.

If you include the PII worktables, you must link the work tables to the existing report tables and insert the PII data into the report.

Anyone who has rights to run the report will see the PII data. As with the standard reports, the ability to view Social Security numbers, bank account numbers, and bank routing numbers is based on the "Allow Viewing, Printing and Editing of Personally Identifiable Information" security event in Role Maintenance.

Thank you,

Kent