Multiple AL (AutoLink) Tables

[vlfox]

We recently switched to ReportSmith Version 3.12, SQL based.

In trying to pull individual check details that include Deductions & Earnings Codes, it was identified that those codes are actually in 2 (or more) AL/AutoLink tables.

We'd still like all of one employee's data to be in 1 row by using derived fields, but our contacts with ReportSmith phone support result in being told "that's not possible; anytime you have more than 1 AL table, you will have more than 1 row per person."

It doesn't seem like we'd be the only company that would want this much detail in as simple a format as possible, so I'm throwing a line out to ya'll see if anyone's discovered a "secret" ReportSmith doesn't know about.

ThanX !

 

[CharlesCook]

Just to bring me up to speed with where you are tell me what tables are you using and how you have them linked?

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[vlfox]

The 2 for my current report attempt are: PS_AL_CHK_DED AND PS_AL_CHK_HRS_ERN. I have both linked back to my first table PS_JOB by EMPLID only.

ThanX !

 

[CharlesCook]

So you are getting a ton of records for each person/check. Or a record for each deduction and hours earning code and check per person.

And you are looking for 1 record per person per check with each deduction / earnings code in a separate column?

This can be done using Database Grouping. Database Grouping is a fairly advanced subject. You can get a bit of an idea how it works by looking at this power point

http://charlescook.com/PPS/ReportSmithandADPPCPayrollforWindows-DatabaseGrouping.pps

This powerpoint is using PC Payroll. If you are new to this subject you may want a consultant to do the first one for you.

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[vlfox]

We've used Database Grouping successfully while using PCPW and also now that we're on Enterprise for reports that don't have multiple AL tables.

So far, everytime we've needed data from multiple AL tables we've built multiple reports and manipulated much in Excel. For the 3 months on Enterprise that's been painful, but as our data grows in Enterprise, that's just not going to be feasible.

ThanX !

 

[jdiguy]

You can use derived fields to get the data on 1 row. But I suspect you may also have additional tables you didn't mention, like INFO, TAXES, etc.?

Usually, the hard part with these tables (CheckView tables) is the inability to link them correctly. ReportSmith will support only 1 outer join, and you need 3 to get all the checkview data in 1 row.

The basic problem is that ADP is mixing parent and child row concepts, and just putting them in different tables. The INFO table is the main table; there is 1 row per check (could be multiple checks per employee) like a parent concept. But for Hours, Earnings, Deduction and Memo tables, they are more like child row concept, in that there will be many Deduction rows for 1 check in the Info table. However, if there are no deductions, then there will be no rows present! That's why joining these tables is a problem--not just with ReportSmith, but because of the design of the data structure.

You are not the only company wanting this--ADP needs to hear that you want a consolidated view of the checkview and YTD tables.