Sage 100

Payroll Masterfile Exports - SAGE 100 STD v2017. Trying NOT to reinvent the wheel. Is there a company that sells/did sell pre-written Crystal Reports? How about Crystal Reports for extracting Payroll Masterfile Data into Excel or CSV file. Client is going to Kronos Workforce Central web-based software at year-end. Can I buy the export(s)? Should I use V/I instead?

I would just do VI, include all field with headers. Short and sweet.

I tend to use excel to query the data. You get a tab for each table you need. It will take you under an hour to setup all the tables to separate tabs unless a great deal of formatting is required.

+1 on what @LarryBradford said. And if you do need to do formatting (e.g. let's say Kronos wants different date format), it's not a gigantic effort. One issue with V/I exports is by default after the last field of data is exported, it exports a trailing comma (or whatever you chose as the delimiter). Sometimes that's a PITB.

Excel is soooo much easier for exporting that VI... Once you get the hang of it, you will never use VI for exporting again (unless of course you are trying to find the record with a bad date and you can't find the link to the bad date utility that Sage just published, then VI works very well). And BTW, at one time, @SueBennett had a bundle of prewritten Crystal report for sale.

Thanks everyone, I will give Excel a try. And thanks @JeffSchwenk, @SueBennett is who I was thinking of ...Crystal Vision.

What happens to the above options once you upgrade to payroll 2018+ and have to deal with PII? Curious.

@WayneSchulz , I actually took that into consideration when answering. VI or Crystal become the solution. I personally hardly ever us VI export because I like Crystal and Excel so much better. The other tangent of your question would be the best method to transfer this sensitive data to Kronos. (A non secure email is not a good idea).

AFAIK, the only way to export is via an attached custom report using the PII work table(s) if there are other ways I'm very curious

These are the basic instructions for an excel query I share with our clients.

I had not tried VI yet in 2018.

x-fref: [Payroll 2018+ information is now encrypted as Personally Identifiable Information (PII) and cannot be accessed via ODBC. ](https://support.na.sage.com/selfservice/viewdocument.do?noCount=true&externalId=89390&sliceId=1&isLoadPublishedVer=&docType=kc&docTypeID=DT_Article&stateId=11719&cmd=displayKC&dialogID=513065&ViewedDocsListHelper=com.kanisa.apps.common.BaseViewedDocsListHelperImpl&openedFromSearchResults=true)

For PR 2018+ I wonder if a BOI script could be used to extract the keys / protected data into a file (which could be transmitted separately, and rejoined on the other side).

Right on Kevin. For PR 2018+ BOI can read the encrypted PII fields and potentially send the data to Kronos at same time depending on how Kronos can receive it. A Crystal report run from Custom Reports menu and exported from there would also work but has more manual steps. From MD perspective I've created utilities that run off the menu that do the same type of thing but have the front-end with a grid selection of dates and other criteria.

Who can help me with my rusty Excel skills. As recommended, I trying to use Excel (VLookup, Microsoft Query, Power Query, etc.) to export data from a v2017 **PR_01 Employee Direct Deposit Master** file. The PR_01 file layout is Dept,Emplee#, DD_Acct1,....DD_Acc1YTD, DD_Acct2,....DD_Acct2YTD, DD_Acct3,....DD_Acct3YTD, etc. I need to create a single line in Excel for each Employee/each Direct Deposit Account: Dept,Emplee#1,Acct1,Acct1YTD Dept,Emplee#2,Acct1,Acct1YTD Dept,Emplee#2,Acct2,Acct2YTD Dept,Emplee#3,Acct1,Acct1YTD Dept,Emplee#3,Acct2,Acct2YTD Dept,Emplee#3,Acct3,Acct3YTD In addition, after I have accomplished the above format in Excel, I need to merge data from the **PR1 Employee Master** file and insert fields on each line after Dept,Emplee#, fields like First_Name, Last_Name, Address, SS#, etc. TIA for any guidance you can provide.

Drink two beers, watch some TV, chill and call me in the morning.

@JeffSchwenk ...Will Do. Thanks!