Changing Crystal Report Pointers to the Abra HRMS Data Access Connection

June 15, 2016

Changing Crystal report pointers is now easier since you can use ODBC connections. You can see how to access your payroll data through Abra HRMS Data Access. Bay adding the connection in Crystal report you can quickly access payroll data. These tips and trick will also help you update your existing custom payroll reports.


"My name is Mary Anne Giezentanner. I’m a senior application consultant with CS3 Technology. Today I’d like to talk about changing your Crystal Report pointers to the Abra HRMS data access connection. Beginning with HRMS  2015, and now with HRMS 2016, Sage began including views of the payroll data within the Sage HRMS live database, thus eliminating the need for additional ODBC connections to report on payroll data when using Crystal Reports. I’d like to cover a few points on this today. Including the benefits of connecting to your payroll data through Abra HRMS data access, how to add the connection and Crystal Reports, then how to use that connection for accessing your payroll data, how to update your existing custom payroll reports,and a few tips and tricks you’ll need to know when working with this connection. Let’s get started.

We’ll start out by working with this blank Crystal Report. One thing to note is that in order to access the Abra HRMS data access connection.You do need to be logged in to HRMS and have an employee demographics screen pulled up. The Abra HRMS data access connection is the same connection used by your HRMS client to access your database. Using this connection for your Crystal Reports will help to ensure that your security settings from HRMS, under group and user security, are handed down to your Crystal Reporting. Again, it will require that you’re logged into HRMS and that you have an employee demographics screen pulled up. Once you’ve done that, you’re ready to start working with the connection.

You’ll notice here under my connections that I have my ODBC connection for my payroll database. Those of you with multiple payroll companies probably have a list of these connections to use when doing your payroll reporting. That brings me to another benefit of using the Abra HRMS data access connection. All of your payroll company data will be available through this single connection. If you log into Crystal and view your connections, and you do not see Abra HRMS data access available—no worries. To add this connection, expand ‘Create New Connection,” then expand “OLE DB,” and double click on “Make New Connection.” Under your list of providers, you should see Abra HRMS data access. Select this connection and click “Finish.”

This connection will then be added to your list of available connections. You might notice under this connection that in addition to the tables you’re accustomed to seeing (such as “HR personnel” or H Bene), you now have your payroll tables available as well. Now you’re ready to start reporting using your Abra HRMS data access connection. You probably have some existing payroll reports that you’d like to convert to using this connection instead of the separate ODBC connections. Let’s take a look at an existing payroll report.

I have a simple report here earnings, deductions, and taxes.This report is currently using the ODBC connection to my payroll company,specifically tables UPCHKD, KH, and UPEMPL. To convert this report is “OLE DB”connection, I can simply go to “Database” and select “Set Data Source Location.”Here you’ll see the tables listed under my ODBC connection. So I’m going to select my first table, and then find that table under my new connection. Selected in the new connection and click “Update.”

Here we have a date field from the ODBC connection that is using the old date format. Not a problem. We can just de-select “Match Type”and then find “Period” in here, and click “Map.” Then we can click “OK.” We might have the same thing with UPCHKH if we have something using the “Check date”or “Trans Date.” And we do, we have “Trans Date” and “Period End.” So again, we will de-select “Match Type,” find “Trans Date” in our new connection, and “Map.”And then again, same thing with “Period End.” All right. And our last one will be UPEMPL. I don’t anticipate any transaction data issues with that one.

All right, so now we have successfully converted our connection from our ODBC connection to our new Abra HRMS data access connection. The date fields we just mapped may have some issues that we need to update in our report. Let’s take a look. Go ahead and run my report. So here I have an error message: “Bad Date Format Stream.” Those of you that have used the “Trans Date” or “Period End” date from the ODBC connection may be familiar with the formula used to format that date. This is no longer necessary with the OLE DB or the Abra HRMS data access connection. I have an option of just deleting this formula field and putting the “Trans Date” in my report. However,this report is grouped on this formula field, so I’m going to work around that by just commenting this formula and adding just that field here. Once I’ve done so, my report should refresh successfully. Alright, and there we go.

So today, we’ve talked about the benefits of connecting to your payroll data through Abra HRMS data access, how to add this connection in Crystal Reports, and how to use the connection to access your payroll data. We’ve also looked at updating existing custom Crystal Reports, and we’ve covered a few tips and tricks you’ll need to know along the way."

Get Access to our extended Educational Video Library

Thank you! Your message has been sent and one of our team members will reach out to you shortly.
Oops! Something went wrong while submitting the form.