Fixing duplicate line reporting in SAP HCM

August 13, 2017
Written by Danielle Larocca

Senior Vice-President of HCM Solutions Danielle Larocca has worked in the SAP HCM space for over 20 years. An SAP Mentor and featured speaker at numerous conferences, Danielle has authored four best-selling books on SAP, is the Technical Editor for the SAP Professional Journal, and often the Voice of the Expert on SAPInsider’s Ask the Expert series for HR.

Reporting in SAP HCM is never easy

There are multiple tools to choose from, including the Ad Hoc Query and the SAP Query, both accessible via transaction code SQ01. These tools have limited functionality; however they do, in most cases, allow you to report on basic infotype data for employees.

One frustrating result output that you will find with these tools is that sometimes you will see report output that has multiple lines per employee instead of a single line output per employee. There are a couple of different scenarios in which this may occur:

1) Date Selection Impacts to Multiple Line Reporting

The first instance in which this occurs is related to date selection. Virtually every HCM infotype record is effectively dated. In other words, each has a Start Date and an End Date showing the effective date range for which the record is valid (see picture below). Employees at any given time might have multiples of the same infotype, each with a different date range. A popular example: An employee may have three Infotype 0008 (Basic Pay) records, one for each salary that they had over the past three years. The historical information is useful, however it may produce a challenge in reporting if the appropriate selections are not made.

Upon execution of any virtually any report in SAP, you will see a Selection Screen which provides you with the opportunity to specify the criteria for the data you want to see in your output. The most common date parameter for reporting in SAP is to the date selection period ‘Today’. Selecting ‘Today’ ensures that the data you have retrieved from the database is valid as of today. One thing to note here is that if you have any future dated records (for things like increases or organizational changes) they will not be included in your report output, because technically they do not exist yet. Most users complain of duplicate record results when selecting the ‘Other’ or ‘Person Selection Period’ date parameters, and that is because multiple records may exist for that employee during the date range specified. Being sure to select ‘Today” will often solve many of the duplicate records you see in reporting output.

2) Variable Field Impacts to Multiple Line Reporting 

The second instance is a bit more complicated, and that’s related to retrieving duplicate records even though you are selecting ‘Today’ on your selection screen.  This is specific to certain infotypes that have multiple values in a single or table-based storage space. That sounds pretty technical, but basically what it means is that the database pulls all the records meeting your criteria. This issue of duplicate records does not occur with some infotypes, such as infotype 0002 (Personal Data). This is because infotype 0002 stores each piece of information as a single identifiable field (see sample below).

Duplicate line reporting in SAP HCM

The first name is stored in the P0002-VORNA field. To see the technical details, place your cursor into the field, press F1, and then click on the Technical Information button. The first name is the only information that can be stored in that field. Let’s compare that to an infotype that does produce duplicate records, such as infotype 0041 (Date Specifications). Date specifications does not have a single field identified for only a single piece of data. Rather, the data that can be stored in each field is variable (see sample below).

Fixing duplicate line reporting in SAP HCM

Infotype 0041 permits storage of customer-specific dates. During configuration, each customer determines the date types that work best for them. In the example shown in the picture above, the associate has five different date types stored as Date type 36, 40, 41,44 and 48, listed in numerical order. However, unlike infotype 0002 in which the fields store only certain objects (for example, the first name field only stores first names in the P0002-VORNA field), the fields on this screen can store variable data. Date type 30 could appear in the first box or the last, depending on how many date types are on the screen.

When I repeat the steps mentioned in the infotype 0002 example to see the technical details of the Date type 30 date, I get the value P0041-DAT01. If I look at the details of the second date it would be P0041-DAT02, which refers to the second date box on the screen. The date type field for the next one would be P0041-DAT03, etc.

However, that value of DAT01 is assigned because the date is stored in the first position on that screen. If I added a new date type for the associate, such as Date type 23, that would then become P0041-DAT01 because it would then be in the first numerical position. If I created a query-based report containing a specific field such as First name (P0002-VORNA), it would output on a single line. However, if I created a query-based report to output the date field from Infotype 41 (Date for Date type), behind the scenes the system would read through all of the P0041-DAT01 to P0041-DAT12 fields and output a line in the report for each date stored.

Three Methods for Single-Line Reporting

Three possible workaround solutions exist for single-line reporting of infotypes, including infotype 0041, for dates and the similar wage type reporting off infotypes 0008, 0014, 0015, and 0267.

1) Selection Specification

The first workaround is the fast and limited version. For example, if I wanted to create a basic query-based report that would include an associate’s hire date, using my example Date type 40 (First Working Day), all I would need to do is to include the Date Type field on my reports selection screen. Using that method, I can, upon report execution, specify that I only want that one date type in my report output, thus ensuring I get only a single line. This same method works on the wage type-based infotypes. For example, if I wanted to create a report that listed the employee’s name, position title, and hourly rate (i.e., wage type 3005), I could do so by including the wage type field on my reports selection screen. Upon report execution, I can specify that I only want that one wage type 3005 in my report output, thus again ensuring I get only a single line.

I mentioned that this is a limited workaround because of the way a selection screen works. It only includes data in your report that meets the criteria entered on the selection screen. If I were to produce a report of everyone and their hire date on a single line as I mentioned previously, my single line report output would only include those associates who have that date type. Similarly, the output of the hourly rate report would be limited to only those associates who have an hourly rate, using wage type 3005. If some folks were missing that field, they would be excluded from the output. I refer to this first workaround as the fast and limited one as it is helpful when you are sure all associates meet the criteria entered on the election screen or if you want your report output to only include those associates. Because most companies require a date of hire, it would work for that example. Another downside here is that you are limited to reporting off only one date type. If you wanted to include hire date and an adjusted service date from the above example, you would still get two lines for each associate.

2) Calculated Fields in the Query

The second workaround is to create calculated fields in the SAP Query itself that identifies each field as unique. The premise is that if you use a reporting tool such as SAP Query, you can create calculated fields that output your data all onto a single line. The SAP Query allows you to create multiple types of calculated fields. You can create a calculated field for example called Salary Increase, which would be a calculated field of the annual salary multiplied by 3 percent to perform salary increase budgeting. No ABAP skills are required for basic calculations. Basically, you create a new field available for output in the query for each date type. The one downside to this workaround is that any calculated fields are available only within the query that they were created in and not to new queries.

3) Calculated Fields in the Data Source (InfoSet)

The most thorough solution is to take it one step further by calling on the skill of an ABAP programmer. What you can do in this more advanced workaround is to create calculated fields in the data source used for the queries, the InfoSet. That way, the calculated fields are available to any new queries. The fields are written in ABAP code within a program in the InfoSet itself. Earlier I mentioned that the reason why the first name field on infotype 0002 never gives duplicate records is that the field it is stored in is designed only to store that data. Using the infotype 0041 example, you can create calculated fields that store each specific date type, giving you the flexibility to include as many as you want, all on a single line.

See below for a sample of ABAP program code that was added to the SAP Query InfoSet. This code added seven fields from infotype 0041 to my InfoSet for reporting. These new fields in my InfoSet allow me to report off any dates on 0041 (regardless of whether the associate has values for the date types), all on a single line in SAP Query reporting. The date types referenced in the code sample are specific to the way a sample organization has them configured. The code reads through all of the different table values and assigns each date to a specific field for use in reporting. You can also use this for wage type reporting. A common request is to produce a report of the entire organization that includes deduction information and hourly rates for associates.

Sample ABAP Code for InfoSet

       DAT LIKE PA0041-DAT01,


   DO 20 TIMES
      VARYING dar FROM I0041-dar01 NEXT I0041-dar02
      VARYING dat FROM I0041-dat01 NEXT I0041-dat02.
      IF dar IS INITIAL.
      RESULT = DAT.

In this scenario, you would build a program using the same looping concepts detailed in Figure 3; however, you would create fields for each wage type. Using the hourly rate wage type example, I could create some ABAP code in the InfoSet for infotype 0008 (Basic Pay) that reads through the wage type fields and outputs the data in specific fields. For example, the wage type amount associated with wage type 3005 would be output in a new field called hourly rate. Keep in mind that every organization is different; however, you can use the code sample as a reference to set up your own custom fields for reporting on information such as date types and wage types.

Or you could put an end to the frustration of SAP HCM reporting by using a tool like EPI-USE Labs Query Manager that makes it easy for you to access ALL of the data you need for SAP HCM reporting. Learn more here. 

Learn more about SAP reporting




Explore Popular Tags

Query Manager SAP SuccessFactors SAP HCM reporting SAP HCM HCM Reporting SAP Payroll SAP Reporting EPI-USE Labs HCM SAP SuccessFactors Employee Central Payroll SAP SuccessFactors Reporting Intelligent HR and Payroll PRISM reporting Human Capital Management (HCM) Payroll Document Builder Payroll reporting Query Manager Analytics Connector SAP Analytics Cloud SAP HCM Data SAP Query Microsoft PowerBI SAP SuccessFactors People Analytics SAP Payroll data Variance Monitor HR and Payroll data SAP HXM SAP S/4HANA SAP S/4HANA Private Cloud Edition (S/4 PCE) Tableau Artificial Intelligence (AI) HXM Move Payroll Data SAP SAP ERP HCM SAP HCM On-Premise Solutions SAP HCM Payroll SAP HR Reporting people analytics sap query hr Data Sync Manager Employee Central Payroll Journey to SAP SuccessFactors PRISM for HCM (Private Cloud Edition) PRISM free assessment SAP HCM journey SAP and SuccessFactors HXM Reporting COVID-19 Cloud-based SAP HCM solutions Employee payroll H4S4 HCM Productivity Suite HR SAP HCM/HXM SuccessConnect reporting solution ABAP DSM for HCM Employee Central Payroll Reporting Employee data GeoClock Let's Talk HCM Machine Learning (ML) On-Premise Payroll Pay Recon SAP Data Warehouse Cloud SAP HCM Analysis SAP HCM for SAP S/4HANA On-Premise SAP HR SAP SuccessFactors HCM Journey SAP SuccessFactors Roadmaps SAP data privacy and compliance SuccessFactors Ultimate Guide: SAP HCM & Payroll Options data validation payroll control center AI BTP Data Sync Manager for HCM Digital transformation Employee Central GDPR HCM, HR Joule OData PRISM for ECP PRISM for H4S4 Query Manager with Document Builder Real-time reporting and document creation SAP Analytics Cloud (SAC) SAP Business Technology Platform SAP HCM On-premise SAP HCM for S/4HANA SAP On-Premise customers SAP Payroll to the Cloud SAP Road maps SAP customers SAP data SAP data privacy & security Success Factors SuccessConnect 2019 Tax Reporting Transformation without re-implementation accurate payroll data certification custom infotype data source ebook on-premise SAP HCM s/4HANA Analytics solutions Automated reports Automation ChatGPT Cloud migrations DSM Object Sync for SuccessFactors Hybrid Data Secure Data Types Data analysis EPI-USE Labs’ solutions Employee Letters Employee communication Free HCM Assessment HR Journey HR employee reports Human Experience Management (HXM) Human Resources Human Resources data Hybrid Reporting SAP and SuccessFactors Hybrid SAP and SuccessFactors Hybrid reporting Hybrid reporting solution Integrated reporting SuccessFactors SAP Intelligent Enterprise Microsoft Excel News OData integration OM Object Sync On-Premise Payroll S/HANA Sidecar On-premise reporting Organization of the data PA People Analytics Workforce Planning Personalized documents Protect personal employee data Report Stories Reporting and analysis Robotic Process Automation (RPA) Robotic Process Automation framework S/4HANA Private Cloud Edition (PCE) SAP Data Privacy Suite SAP Data Security SAP ERP Payroll customers SAP HCM 2023 SAP HCM Roadmap SAP HCM and Payroll customers SAP HXM 2021 SAP Mentors SAP SuccessFactors Hybrid SAP SuccessFactors Next-Gen Payroll SAP SuccessFactors Release updates SAP SuccessFactors Time Management SAP SuccessFactors Time Tracking SAP Wage Type Reporter SAP certified solution SAP migration SAPPHIRE 2018 SuccessFactors and the Intelligence Enterprise SuccessFactors' Employee Central Payroll TCO Calculator The Report Center The Road to People Analytics Time management Workforce Planning ad hoc data variances easy reporter high-speed, low-risk on-premise SAP data partner roadmap single reporting solution sq01 stories in SAP SuccessFactors People Analytics technology third party ALE STP report ASUG Accessing COVID-19 data Ad Hoc Query American Payroll Association (APA) Analytics Connector Analytics reports Analytics solution At-risk employees Australian Payroll Australian Tax Office (ATO) Automated analysis and pay run reconciliations Automatic HR reports Best practice in BI Bots Business Analytics Business Intelligence COVID-19 statistics COVID-19 vaccinations Certified solutions Check for data replication errors Client Sync Client-centric Cloud hosting SAP PCE Company Branding Compare legacy HR and Payroll data Comparing data Configuration Center Copy and mask test data Coronavirus Created timestamp Custom Development Custom store Customer-specific infotypes DSAG Data Privacy Data Replication Data Sources Data Sync Manager (DSM) Data access Data privacy regulations Data production support issue solution Democratize data Description Diversity & Inclusion reporting DocuSign Document Building Dubai Dynamic data ECC EPI-USE ERP Education sector Electronic Signatures Embedded Analytics Edition Employee Central time Employee Central timesheets Employee NICs Employee Retention Analytics (ERA) Employee payment summaries Employee right to privacy Employer NICs
+ See More

Get Instant Updates

Leave a Comment: