Sage 100

Hey all you sage 100 Payroll Pro's. I need to know what tables to work with to produce a report for a client. He needs to be able to enter a data range in Crystal and get information about payroll checks dated within that range. I will need to pull earnings and deduction detail so I am looking for the detail check history table. I thought PR_22 and PR_23 would hold what I am looking for but after looking the ABC company files and running a full payroll cycle through ABC, I didn't find any records in these tables. What am I missing? There aren't any files that look like they are applicable other than current pay period files. Am I missing something?

Neither ABC or EEC has ""perpetual history"" turned on, so bad companies to use. You'll have to enable the option and process some payrolls, or use another dataset you might have. Those are the correct tables. though.

PR_22 and PR_23 are the right tables. By default ABC isn't set up to retain perpetual history. Turn this option on and try again.

As I was typing my question, something was tickling that back of my brain. It was the need to enable perpetual history (as the table descriptions note!) Thank you both for such a quick response - 2 minutes. Wow I love this tool and this group! Cheers!

Depending on the number of employees, their perpetual history tables can become quite large. They are usually the largest tables in PR. If you are running the Premium version, running the Crystal report will be quite zippy. However, if you are running the other two, it may begin to drag. A perfect time to start using PTQ's in Access or create a SQL mirror table. Just a couple pennies worth.....

@JeffSchwenk I had already thought about how big the tables could get. SQL Mirror - are you referring to the DSD product or are you using a linked table is SQL and pulling all the rows into SQL table periodically with a SQL Agent job?

Nothing specific comes to mind as there are probably many options ranging in costs. Comes down to what you are comfortable with. I lead off with Access PTQ's. I know you do a lot with SQL so that will be your starting point.

Shawn, I have a sample pivot table for Payroll history detail on our web site, you can download and follow the instructions for linking the pass through query to SOTAMAS90 and how to insert a pivot table into an Excel Spreadsheet. This was part of the MOTM 2014 seminar. There is no limit to the data size for a pivot table. Excel stores this information internally as a ""Data Model"". My largest data model so far is 1.8 million rows. If you need some assistance contact me directly, i can walk you through the process. The final result is an interactive reporting tool that the customer can refresh and configure on their own.

Thanks for the suggestion Doug. I will take you up on in.

@DougJenkins - Are you Vegas bound??

Yes i am.