This weeks’ blog is a continuation from last weeks’ blog which was in response to a question about “How to Eliminate Duplicate Line Reporting in Query Tools“. You can accomplish single-line reporting in SAP query-based tools such as Ad Hoc and SAP Query in three different ways:
- By specifying parameters upon selection (see Dec 1, 2011 blog)
- Creating calculated fields in your report (see Dec 7, 2011 blog)
- Creating calculated fields in your data source (InfoSet)
This is the final blog in this series and it’s fair to say that this is the most complex solution to the question above. The available Query based tools for SAP HCM are quite limited in the data that you can report on. However they can be used to successfully report off of master data infotypes.
The challenge is that some of the data on the infotypes is either not available or not available in single line reporting. A real world example is infotype 41, Date Specifications. This screen stores approximately a dozen different dates for an employee. It is configured by each company to store whatever dates they wish. These dates can be reported using a Query tool and have 1 line per date per employee for only ONE DATE AT A TIME (by specifying the sub type on the selection screen). If you tried to output multiple dates you would get multiple lines per employee. This is explained in greater detail in the 12/1/2011 blog.
The option to fix this and allow for multiple date types (or other infotypes) to be output along a single line is to customize the data source itself that houses all the fields. And here is where this gets technical. This solution requires a trained ABAP programmer to implement. This data source is called an InfoSet, in earlier versions of SAP it used to be called a Functional Area. This InfoSet is setup initially when you begin using the Query tools. If you follow best practices your InfoSet would be set up by a technical person in your Development environment and transported through to your production client. In most cases it would be set up based on SAP’s pre-delivered logical database pnpCE. This logical database provides access to all of the Master Data infotypes.
Because the InfoSet is not designed for the some infotypes that cause multiple line reporting – you can customize it using a call out to an ABAP program so that you can essentially create new fields in your data source to store the data you want to output on a single line. In the Infotype 41, Date Specifications example you would use an ABAP program to create a field to store a field for each of your date types so that in your reports in the future you can simply drag the new fields on and you can report as many date types as you wish and still get only one line per employee!
Creating an ABAP program and integrating it into your existing InfoSet is an activity that should be performed only by a trained ABAP programmer. Here is a sample of code that can be used that can be called from your InfoSet to create a field for each date type on infotype 41.
INFOTYPES: 0041 NAME I0041.
DATA: DAR LIKE PA0041-DAR01,
DAT LIKE PA0041-DAT01,
HIREDATE LIKE PA0041-DAT01,
REHIREDATE LIKE PA0041-DAT01,
LASTHIREDATE LIKE PA0041-DAT01,
ADJSVCDATE LIKE PA0041-DAT01,
TERMDATE LIKE PA0041-DAT01,
SEPARATIONDATE LIKE PA0041-DAT01,
BENTERMDATE LIKE PA0041-DAT01,
KEY_DATE1 TYPE D.
FORM GET_DATE USING VALUE(PERNR)
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.
IF DAR EQ TYPE.
; RESULT = DAT.
The picture included in this blog will show you how to refer to the custom program from the InfoSet.
Next week’s blog I will share the ABAP Code that you can use to get at the most popular fields not available in Query tools including Previous position title, Hourly Rate of Pay and Manager name which you can use as a reference to create even more fields.
Danielle Larocca, SpinifexIT
Connect with me on Linked In at www.linkedin.com/in/daniellelaroccasap
spinifex IT is the creator of Easy Reporter, the only SAP certified solution that runs live inside SAP for real time HR and Payroll reporting. Check it out for yourself online at www.spinifexit.com/easy-reporter/ or contact me for a live WebEx demonstration.