Question: Sometimes we get requests from clients that are not able to be done with Standard Sage 100 reporting. We can use Crystal Reports and simple example that you can follow the flow and methods for yourself.
NOTE: We advise that you consult with your certified Sage 100 ERP consultant before making any changes to your Sage 100 system. If you don’t currently work with a Sage 100 consultant, contact us, call 760-618-1395 or email us at mike.renner@wacptrs.com and we are available to help.
Answer: In this case we are looking at a Payroll Report that would include: Check number, Employee first and last name, check data and check amount. This example is done in version 2017 of Sage 100cloud and Crystal version 11.
- We first need to know the tables that would be needed to be used. We can find the tables and fields in Sage 100cloud by going to Resources>File Layouts and Program Information. A list of the tables is shown below. In our case we are using Tables PR1_Employee Master and PR5_Check History.
2. We then create a new Crystal Report using the Report Wizard, add tables PR1_Employee Master and PR5_Check History to the report as well as add the fields from the tables to the report that include:
Check number, First Name, Last Name, Check Date and Amount. You can see a copy of the tables below, by using the Database Manager and clicking the Links tab you can also see the links. For the tables we want to clear the links and link Department and Employee number together.
3. We then want to right click on each of the Links, select Link Options and make the Links a Left Outer Join as shown below. What is a Left Outer Join: You use outer joins when you need all of the results from one of the join tables, whether there is a matching row in the other table or not.
4. You can then preview the report and Save it and it will look like what is shown below.
How do I find out more information? You can contact us via our Contact Form, call us at 760-618-1395 or email at mike.renner@wacptrs.com.Written by Mike Renner: WAC Solution Partners are dedicated to offering business solutions like Sage 100 (formally MAS 90) with over 25 years in the accounting software industry.Mike is also a Sage Certified Trainer and a recognized leader in the design, implementation and support of ERP systems, including Sage. Specialties: Sage 100 (formerly MAS90), Acumatica, QuickBooks Pro Advisor.
