Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Multiple AL (AutoLink) Tables

Multiple AL (AutoLink) Tables

Multiple AL (AutoLink) Tables

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 !

RE: Multiple AL (AutoLink) Tables

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

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

RE: Multiple AL (AutoLink) Tables

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 !

RE: Multiple AL (AutoLink) Tables

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.

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

RE: Multiple AL (AutoLink) Tables

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 !

RE: Multiple AL (AutoLink) Tables

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close