Using Crystal and Vista to Populate Total Rewards Statements
IMA has done Total Compensation Statements for over 20 years. However, producing them hasn’t always been easy. From 1996 to 2011, The data was compiled from several different systems, merged into an Excel spreadsheet and then processed by a Macro. Once complete, LuCinda Kathol, Director of Corporate Benefits, would select a special paper for the actual statements, and a cover which had to be die cut for the name and address window. For many years she printed them herself, first completing the front page, and then flipping them over for the back page before folding each of them by hand and stapling the content into the envelope, complete with a gold IMA seal! After sorting, they would be sent inter-office mail to each location. Sound like a lot of work? It was.
In 2011 we contracted with our favorite PDS consultant, Perry Williams, to assist in automating and “electrifying” our statements. LuCinda mapped out each data field and Perry wrote the Stored Procedure pulling pieces of information from current benefit plans, prior year’s data using empdedh, empearnh, empfrngh, emptaxesh and populating a database table. The benefit history on employer paid benefits was the tricky part, but like father like son, Ryan Williams suggested we set up a non-cash fringe to capture the prior year employer costs. There are two pieces of data that come from an outside source, and that data is updated to the database table directly.
LuCinda designed the Crystal report using graphics and all the numerous data fields and one sub-report. We use Employee Self-Service to deploy to our users in a drop-down on the Benefits menu. Suddenly the process went from taking weeks, to only a few days. As a bonus, the Total Compensation Statements, now referred to as “Total Rewards Statements” are viewable from year-to-year (just like W2’s and now 1095s).
Here is a sample of what a few of our pages look like:
IMA is not the only customer taking advantage of the power of Vista and sharing with their employees what their total compensation looks like. Here are four other success stories using Vista:
Denyse Sheppard, Manager of HR Administration and Payroll Services at Alberta Motor Association based in Canada said although they’ve had the Total Rewards Statement for several years, it was only recently that they went paperless. Providing it electronically has been more efficient and it has been very well received by their employees. “Seeing it in a graph really stands out and shows the whole picture.”
At Nelnet in Lincoln, NE, Gretchen Schmidt, Payroll & Benefits Analyst uses a Stored Procedure to generate a custom table within their Vista database, and just like IMA, the Total Comp Crystal Report uses the data from that table. “Each line in the report is its own detail section of the Crystal Report. By setting it up that way we can use formula suppression on each section to make lines or entire sections disappear when they don’t apply to the associate.
For example, the Medical, Dental, and Vision lines under Health and Wellness in Report A don’t show up in Report B because that employee wasn’t enrolled in coverage. And the ED Fringe Benefit section on Report B is not shown in Report A where the employee wasn’t eligible for the benefit. This reduced lines cluttering the report.
Our Total Comp statements are provided to the employee using a Self-Service Web Page Option in Vista. The web page option links to the Crystal Report and uses a person_id parameter that is set to be the LoggedInPersonID. Employees can access their report from either the internal or external Vista environment and they don’t have to input anything to run the report for their information.”
Derek Hime, Development Team Supervisor at AECI in Springfield, MO has two versions of their statement (union and non-union).
“We have a process that aggregates the information and stores it in custom tables so that they can be run for historical years. This is an SSRS report. We provide self-service access to the report where it knows who is logged in and only shows that user’s information. Retirement and 401k information from our national organization, it is not stored in PDS so another custom table is referenced. Our union workforce reports are printed and mailed since those users have limited access to computers.”
At Norton Rose Fulbright, Meribeth Carter also uses a stored procedure and a table to populate their Total Rewards Statement using Crystal Reports. “We create a PDF using a hidden employee number that is referenced on each statement. A macro runs to do a page break for each statement, creating individual PDF’s which can be loaded into each employee’s record in Vista documents.” Although this is a new process at Norton Rose Fulbright, like IMA, employees will be able to view their current statement as well as past statements.
Here is a sample of what their 9-page Statement looks like:
As you can see, the graph showing not just compensation, but ALL employer paid benefits paints a powerful picture for employees.
With Vista, there truly is an Oasis of Possibilities!
Authors Note – A party is defined as “a social gathering of invited guests, typically involving eating, drinking, and entertainment,” LuCinda wasn’t fooling anyone with her reference to the “Total Comp Folding Party.” There was no eating or drinking, and the only entertainment was her yelling at us if the folds were in the wrong place.
Co-Chair, PDS UGA and
The IMA Financial Group, Inc.