Quick Solution: Merging Data in a Flexible Spending Account Report
Merging Data in a Flexible Spending Account Report
There are many ways that our team at SpinifexIT helps customers be more productive with our software. One of those ways that I really enjoy is the Report Building Workshop. These focused workshops allow customers to bring their most difficult reporting challenges to the workshop where a SpinifexIT Solution Engineer works with them to show them how to build the reports. Workshop attendees can either create those difficult reports or learn new ways to streamline existing reports.
During a recent Report Building Workshop, I worked with the Benefits group at one of our customers to help them create a Flexible Spending Account report (Dependent and Health) using Easy Reporter. They needed a report that would list the benefit plan (Infotype 0170), the employee’s contribution (Infotype 0170) and the amounts deducted for a specific pay period (Payroll Results Table).
The following fields were pulled into the report:
For the Payroll Results fields, under Column Selection, we selected the wage types for Dependent and Health Care Spending accounts from the RT table.
When they initially ran the report, they discovered that the report was creating a separate row for the Payroll Results fields (see below, and click on any screenshots to view them in more detail). Per their report specs, they wanted the Payroll Results/deduction to appear on the same line as the benefit plan and EE contribution.
Using a “merge” seemed like an easy solution. We selected the Employee Number field as the unique field for the merge, but before we were able to use the merge, we did the following:
- Added the “Wage Type” field to the report (Payroll Results folder à Wage Type Clusters folder)
- Added a “Mapping” field to the report. The field setting on the mapping field looked like this:
In this report, we mapped the wage type for Dependent Care and Health Care to the plan (DCAR and HCAR respectively). The mapping timing was also changed to “After Substring and prior to Column Filters”.
- Once the “Mapping” field was added, we added a “Concatenate” field that combined the following two fields: 170-Benefit Plan and the Mapping field. After adding the “Mapping” field and the “Concatenate Field”, the report output looked like this:
Now we were ready to use the “Merge” field (under Advance Options in Tab 2). We merged using the Employee Number and Concatenate fields and this allowed us to output the deduction amounts on the same row as the Benefit Plan and EE Contribution amount (see below):
Once a report is merged, you can hide the columns you do not want to show on your report (we hid the Mapping, Concatenate and Wage Type fields). The end result was two lines per employee, one for Dependent Care and one for Health Care (see below).
Report Building Workshops are a lot of fun for both our team and our customers because we get to really explore the power of Easy Reporter and Easy Documents. If you’re already a customer, I hope you got a tip or two from this blog post that you can use. If you’re not already a customer, I hope this example gave you some insight into what you’re missing by not using Easy Reporter for your SAP HR/Payroll reporting!