At a recent customer onsite, a customer asked if we would be able to create a report that more accurately reflected their Gross Profit. Some of the challenges they were facing were related to the applications they were using. Either the lack of integration between the ATS (Applicant Tracking System) and ERP (Enterprise Resource Planning) or functionality. In this case payroll was not saving the costs in detail.
The customer had been told time and again that what they were trying to accomplish with their current systems was not possible. Even the most skilled consultants would not be able to create what they needed. While that may be true using native functionality, we put on our thinking caps and were able to devise solutions that allowed us to overcome these limitations.
To start, we needed to have a way to save the payroll details by employee by state by pay period within the current payroll solution, Dynamics GP US Payroll. A small dexterity customization was created to save these details to a permanent table so they could be joined with the ATS data. Other payroll solutions could be considered if they have an option to provide more details. For example, ADP or Paychex have reports that can be downloaded to show more details than the entries you use to create a financial journal entry. Reach out to find out more about this option.
Using a test environment, we were then able to reprocess old Period End Reports for the past two years. This would allow the customer to do year-over-year trend analysis. For example, they could see what customers were consistently profitable or if any departments were hitting their forecasted company goals.
Next, we needed to find a way to link the ATS data, in this case eRecruit, with the GP data. If you are in staffing you understand there are multiple scenarios that need to be considered: pay and bill transactions, pay no bill transactions and bill no pay transactions. In addition, a common practice is to adjust timesheets after they have been processed. Whether this is to add time to a period that was originally missed or to recover time that was incorrectly paid out or billed.
Once we identified all the necessary data points, we could design the procedures necessary for pulling all the data together. But pulling all the data together isn’t enough. With years’ worth of data possibly needed and merging that data, we needed to ensure the reports are performant. The goal was to make these reports fast so they could be run any time and not just at month end or after hours.
In the end, the customer was able to pull Gross Profit reports by customer, by placement, by department for different time periods and not wait hours for data to refresh. Next up on our agenda is to make them pretty using Power Bi. As we always say, unless the underlying data of the report is trustworthy users won’t use these reports. Data validation and accuracy is always first. Then, we can make the solution pretty.
