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).
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).
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
REPORT ZHR_DATES .
INFOTYPES: 0041 NAME I0041.
TABLES: PA0041.
DATA: DAR LIKE PA0041-DAR01,
DAT LIKE PA0041-DAT01,
PROBATIONEND LIKE PA0041-DAT01,
FIRSTWORKINGDAY LIKE PA0041-DAT01,
FIRSTPAYDAY LIKE PA0041-DAT01,
ADJSVCDATE LIKE PA0041-DAT01,
VESTINGDATE LIKE PA0041-DAT01,
SEPARATIONDATE LIKE PA0041-DAT01,
BENTERMDATE LIKE PA0041-DAT01,
KEY_DATE1 TYPE D.
FORM GET_DATE USING VALUE(PERNR)
VALUE(DATUM)
VALUE(TYPE)
CHANGING RESULT.
CLEAR RESULT.
PERFORM READ_INFOTYPE(SAPFP50P) USING
PERNR '0041' SPACE SPACE SPACE DATUM DATUM '0' 'NOP' I0041.
IF SY-SUBRC EQ 0.
DO 20 TIMES
VARYING dar FROM I0041-dar01 NEXT I0041-dar02
VARYING dat FROM I0041-dat01 NEXT I0041-dat02.
IF dar IS INITIAL.
EXIT.
ENDIF.
IF DAR EQ TYPE.
RESULT = DAT.
exit.
ENDIF.
ENDDO.
ENDIF.
ENDFORM.
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.
Leave a Comment: