Advanced data merging with Easy Reporter: A case study
Advanced data merging with Easy Reporter: A case study
The Solutions team at SpinifexIT is always being asked for Easy Reporter tips and tricks, so I thought I would publish this little “how to” blog post about advanced merging techniques. The inspiration for this post came directly from an interaction I had a couple of weeks ago with a customer so, if they are reading this, thanks for the idea!
Easy Reporter’s data merging capability is very powerful and it’s one of the features that allow you to output one line of data per employee. For most reports our customers run, this is the goal. For example, if you’re sending a report to a manager or using Easy Reporter to produce an interface, you typically you want one row per employee for ease of reading as well as meeting most interface file format requirements. If you try to do this with some of the standard SAP tools, it can be difficult. To get around multiple rows of data per employee usually requires custom report code. With Easy Reporter, users can output a single row of data per employee without coding or programming.
For our example scenario, let’s assume I am producing an interface that includes data about an employee’s educational background. From this report output, I need to know if an employee has a master’s degree and also output their graduation date. The output should look something like this:
Educational information in SAP is typically stored in infotype 0022. In my system’s configuration, the record begin and end dates (BEGDA and ENDDA) represent the dates the employee was attending the school to get their degree and the infotype data holds the degree that they attained. For our example, let’s say we have a record like the one shown below.
In theory, it’s easy enough to pull the end date of the record from infotype 0021 where the degree code equals MA and we have our solution. In reality, things are rarely that simple. If they were, I wouldn’t be writing this blog post. In the real world, employees can in fact have multiple masters degrees stored on infotype 0022 like this:
To get what we’re looking for in this scenario, we need to do a few things with Easy Reporter that might otherwise require coding. What we want to do is this: 1. Pull all infotype 0022 records where the Degree Code is MA. 2. Sort the records by End Date, and 3. Only display the one that shows up in the first row of our sorted list.
That is a little more logic than most reporting tools can handle. Most tools will end up giving you output that looks like the table below since they can’t easily determine the record with the highest end date.
Using traditional reporting methods, we would end up getting multiple records for the employee in this example because as you see above, they have two records with the degree code MA. If I didn’t have Easy Reporter, I would have to take this data, dump it to Excel (let’s not even get into the risks of exporting and manipulating data outside of SAP!!), sort it by employee number and end date descending, identify the employees that have multiple records, and delete the 2nd record. Phew.
We can accomplish this with Easy Reporter without resorting to coding, so let’s break this down. I mentioned that this post was about merging and that is what we are going to do. But first, let’s look at the report components I need to get my desired result:
- Employee Number
- Employee Name
- Personnel Area
- End Date of Degree
- Degree code
So the minimum set of data I need looks like this:
You’ll notice that all of the fields are exactly the same except for the end date of the degree. This is an ideal set of data for merging. If you have multiple records that are exactly the same, Easy Reporter will merge them into one record so you don’t have duplicated data in your report. Looking at this particular set of data, I know that if I could get the End Date of 8/1/2009 in both records of this report, Easy Reporter would merge it for me and I would get the one record I’m looking for – the one with the most recent graduation date.
In Easy Reporter, the merge itself is automatic, but in this case we are going to have to tweak some settings to get the scenario we need: having 8/1/2009 appear as the End Date in both records to facilitate the merge. To get my desired result, I will instruct Easy Reporter to sort this employee’s education records by the descending End Date and then replace the End Date value in each record with the End Date from the first record. This will put 8/1/2009 in both records and enable Easy Reporter to merge down to one record. This would work even if someone had 3 or 4 master’s degrees.
Lets see how it works. First, in our merge settings I’m going to specify that I want a unique record per employee. This is important as I’m going to set some merge options to replace data and I only want it to occur on an employee by employee basis, not for the whole report.
On the sort settings, we are going to instruct Easy Reporter to sort the records in descending order by the End Date of infotype 0022. This will ensure that the highest degree graduation date is always in the first record per employee.
Next, I will access the merge options and tell Easy Reporter to replace each preceding instance of the End Date with the first record it finds, which we know is the greatest date since we sorted it in descending order in the previous step.
Although you won’t see it because the merge would take place once this is set, the unmerged data would look like this:
I replaced the End date with the first End Date for the employee by copying 8/1/2009 to both records. Since the merge setting is “on”, we would not see the above. Since all the records are now the same, Easy Reporter would merge it down to one record and you would get the output below:
I would then hide my degree code column from the output and rename the End Date field and would be left with exactly what I was looking for: one record per employee displaying the highest graduation date at which they earned a master’s.
In the merge option drop down list, there are a variety of ways we can merge data and I’ve even amazed myself using some of those settings to perform things I didn’t think were possible without breaking out my ABAP editor. I hope you enjoyed this post. Happy Reporting!