The most common date parameter for reporting in R/3 is to the date selection period Today. Selecting Today ensures that the data you retrieve from the database is valid as of today. One thing to note is that if you have any future-dated records (for example, increases or organizational changes), they are not included in your report output because technically they do not exist yet. Most users complain of duplicate record results when selecting Other or Person Selection Period date parameters. That is because multiple records may exist for that employee during the date range specified.

Now, to get to the heart of your question regarding retrieving duplicate records when 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. This is because infotype 0002 (personal data) stores each piece of information as a single identifiable field (Figure 1). 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.

Figure 1

Sample screenprint of infotype 0002 that contains single identifiable fields

That 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 (Figure 2).

Figure 2

Sample screenprint of infotype 0041 that contains variable data field entries

Infotype 0041 permits storage of customer-specific dates. During configuration, you determine the date types. In the example shown in Figure 2, the associate has three different date types stored as Date type 30, 36, and 66, 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 next to it would be P0041- DAT03, and the next one would be P0041-DAT04, 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 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 (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.

Methods for Single-Line Reporting

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

Selection Specification

The first workaround is the quick and dirty 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 36), 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 it, they would be excluded. I refer to this first workaround as the quick and dirty one as it is helpful when you are sure all associates meet the criteria entered on the election screen (so you get complete output) 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 service date from the above example, you would still get two lines for each associate.

Calculated Fields in the Query

The second workaround was discussed in my article, “Create Calculated Fields in SAP Query for HR and Payroll Data,” in the October 2003 issue of HR Expert. 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 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 to the query that they were created in and not to new queries.

Calculated Fields in the Data Source

The most detailed solution is to take it one step further by calling on the skill of an ABAP programmer. What you can do in this more detailed 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 Figure 3 for a sample of the ABAP program code from the SAP Query InfoSet (formerly known as a functional area) that I use for HR reporting. This code added seven fields from infotype 0041 to my InfoSet for reporting, so now I can 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.

Figure 3

Sample ABAP code

The date types referenced in the above code are specific to the way that my 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 annual salaries and hourly rates for associates. 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 my hourly rate wage type example, I could create some ABAP code in the InfoSet for infotype 0008 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.

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.