Using SQL commands in ABRA HR

Wednesday 13 March 2013 @ 9:36 pm

I don’t know how many of you out there using Crystal with the ABRA HR/Payroll software, but I just had a real challenging episode with it and ended up learning quite a bit (the hard way).

My task was to speed up a report that took 3 hours to run. The problem was obvious – a recurring subreport that ran 900 times on a typical payroll. A subreport was used because linking the table would have required adding a filter to an outer join, something that the CR linking window can’t do.

Plan A:
To eliminate the subreport we needed a SQL Command that included a Left Outer Join to a subquery. But when I wrote the command it generated errors. So I started simplifying the SQL to find the problem. When I got down to one field from one table it became obvious that the OLE DB connection did not support SQL commands. I learned that ABRA has a proprietary OLE DB driver called “Abra Data Access” or ADA. It ensures that all users see only the data they are authorized to see. However this driver can’t pass a SQL command.

Plan B:
Then I noticed that there was an ODBC driver that connected to the same data. This older driver bypassed the security model, but since my users was the admin that was OK. I did a quick test to confirm that this driver would allow SQL Commands and then started writing the SQL.

But when I got to the Subquery it failed. I found that the ODBC driver allows commands, but not subqueries.

Plan C:
Instead of a subquery I tested using two separate SQL commands linked with a Left Outer Join. This can be a performance killer but here it seemed to work pretty well, so I created the rest of the report.

But when we tested it we found that there were lots of mysterious extra records making it into the report. The records were data that was valid at some point, but had since been updated. I couldn’t find any way to exclude the obsolete records because there was no field to distinguish them.

Plan D:
So I did some more research. This ODBC driver is for Visual Fox Pro (VFP) and apparently tables in VFP sometimes maintain records that are ‘marked for deletion’. The ODBC driver we had could be set to include or exclude these records. The setting is inside the ODBC data source (DSN). It looked like a simple fix.

But when we went into the ODBC Administrator we found that the ODBC driver was no longer listed so there was no way to reconfigure the connection. The driver was still there and working but the configuration utility to create or modify the settings was missing.

Plan E:
So I went to Microsoft’s web site, figuring I could download and reinstall the ODBC driver to restore the configuration utility.

But I found that this ODBC driver is no longer available because it is no longer supported by Microsoft. They recommend OLE DB but I was concerned that installing another OLE DB driver might cause issues with the proprietary OLE DB that they already had for other users.

Plan F:
Since the ODBC connection was including deleted records, I figured that the setting must exist somewhere in a place I could change manually. First I went to the ODBC INI files to see if there is anything in there about deleted records. I find the connection name listed but no settings. Then I go into the registry and search for the name of that ODBC connection. When I find it I see a registry key below it called “deleted” and it is set to “No”. I change it to “Yes” and all the obsolete records disappear. Now the report is correct and runs in roughly one minute.

Now I am told that ABRA is in the process of converting to SQL and that this transition is part of that process. So hopefully these issues are just temporary. But if you are in the middle of this and you need to write commands, hopefully you will find this info helpful. If you are still stuck, at least you know where to get help.









Leave a Reply

Jeff-Net
Recrystallize Pro

Crystal Reports Server