Combining data from R/3 with data from another system is easy as long as both sources of the data contain a similar unique element. The author shows how using the example of a company that wants to combine employee data from R/3 with average clock-in and clock-out times from an external card key system.
As an HRIS director, I am often asked to combine data from R/3 with data from another system into a single report. Some examples include:
- Requests to produce a report containing data from R/3, such as pay grades and salaries, to compare it to an external pay grade survey to see how our company’s ranges match up to a standard
- Requests to produce a report containing data from R/3 such as employee schedules and hours worked to compare it to an external legacy system that measures productivity per department
Satisfying these types of requests is easy as long as both sources of the data contain a similar unique element. Basically, they both need to have a common unique field, such as a Social Security number or personnel number that creates a relationship between the two. Let’s use the following example. A company stores its employee data in R/3 and it also has a separate legacy system for employee ID cards (security swipe cards). See the sample tables shown in Figure 1.
Figure 1
R/3 master data and legacy system data
The company wants to create a report that contains data about the employee from R/3 and information about the average clock-in and clock-out times from the external card key system. Its interest is twofold: to determine if people are coded in the appropriate work schedules and to see if the employees are working when they should be.
Because both tables have a common unique field (EE Personnel #), you can use Microsoft Access to create a report containing data from both sources by following these steps:
- Download a file from R/3 that contains the data you wish to use in your report, and save it in Microsoft Excel format (.xls).1
- Download a file from your external legacy system that contains the data you wish to use in your report and save it in Microsoft Excel format (.xls).
- Open both spreadsheets, one by one, in Microsoft Excel to ensure the data is what you need. Close both spreadsheets.
- Launch Microsoft Access and select the option to create a new database using a blank access database. Give the database a name and save it locally on your computer.
- In Access, follow the menu path File>Get external data>Import. The Microsoft import text wizard will launch, prompting you through the import of your first Microsoft Excel file from R/3. Once this is complete, repeat these steps for the second file.
- When both the files are in Microsoft Access, select the Queries tab and the New button to create a new query. When the wizard appears, select the design view option. A small box appears, listing both of your tables. Select each table name once with your cursor and then select the Add button.
- By default, Microsoft Access may create a relationship between the two tables for you. This will be indicated by a line drawn between the two tables. Select that line with your cursor and select the Delete button.
- To create a relationship between the two tables, place your cursor on the common field in the first table (EE Personnel #) and drag it to the identical field in the second table (EE Personnel #). A line appears showing that a relationship exists between the two.
- Double-click on the line and you can see all of your options (Figure 2). You can pick from any of the three options. Select the OK button to continue.
- Now that you have a relationship between both tables, select fields from both tables (with your mouse) and drag them down to the bottom half of the screen.
- Select the Save button to give a name to your query.
- To view your report with data from two tables (both R/3 and a legacy system) select the View button on the menu bar (Figure 3).
- From here, you can download this to Excel, print it, or use Microsoft Access to create reports of the combined data using the report wizard.
Figure 2
Options for merging records
Figure 3
Data from R/3 and the card-key system
___________________________
1 This example uses two Microsoft Excel spreadsheets as the source of the data. Legacy data can be imported into Access in the following formats: Excel spreadsheets, text DBASE, FoxPro, HTML, and ODBC.
Danielle Larocca
Danielle Larocca is currently the Senior Vice President of Human Capital Management for EPI-USE Labs. Previously she was the Executive Vice President of Operations/Chief Knowledge Officer at a technology start-up. She has more than 20 years of strategic leadership experience in multi-national business, business process re-engineering, and project and people management. Danielle is an expert on SAP Human Resources (HR) and reporting and has authored four best-selling books on SAP. She is a regular speaker at numerous conferences around the world on topics such as HR, technology, change management, and leadership. She is an official SAP Mentor, a global designation assigned to less than 160 professionals worldwide, who serve as influential community participants in the SAP ecosystem. This group is nominated by the community and selected by the SAP Mentors’ Advisory Board to keep SAP relevant. Danielle also serves as an expert advisor for SAP Professional Journal.
You may contact the author at me@daniellelarocca.com.
If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.
