GRC
HR
SCM
CRM
BI


Blog

 

How to Eliminate Duplicate Line Reporting in Query Tools

by Danielle Larocca

December 1, 2011

Danielle Larocca

This weeks’ blog is based on an Ask The Expert Question

Dear HR Expert,

I have read a lot of the documentation in the HR Expert publication and there are often great tips on Ad Hoc and SAP Query reporting. However, one question continues to be an issue in regard to both. Is there any way to eliminate the issue of duplicate line reporting in Ad Hoc or SAP Query? A specific example would be on a basic report of current data (reporting period set to Today) of personnel number, last name, first name, annual salary, and annual bonus (a wage type) from IT0015. The output of this query would result in two records for each employee who has an annual salary stored and an annual bonus on IT0015. Another example would be pulling a rehire date and an original date of hire from IT0041. Wage types, date types, and certain subtypes always produce multiple lines per person. This causes much frustration in the HR department.

— Senior Programmer Analyst

 

Thanks for the question, which is a common one about HR reporting. The answer is that you can accomplish single-line reporting in SAP query-based tools such as Ad Hoc and SAP Query in three different ways:

  1.  By specifying parameters upon selection
  2. Creating calculated fields in your report
  3. Creating calculated fields in your data source (InfoSet)

Option 1 will be covered in this blog and I will cover options 2 and 3 in subsequent blogs. 

Let’s start by making sure that you have correctly input your paramete rs these include the date settings.  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 infotype0002. This is because infotype 0002 (personal data) stores each piece of information as a single identifiable field (the first name is stored in the P0002-VORNA field for example). To see the technical details, place your cursor into the field, press F1, and then click on the technical information button.  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.

Infotype 0041 permits storage of customer-specific dates. During your initial system configuration you determine the date types. For example an employee may have 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 look at the technical details of the first Date type 30 date, I see the field name is 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- DAT02, and 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 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.

To fix this and accomplish single-line reporting in SAP query-based tools such as Ad Hoc and SAP Query you have three options the first and easiest is by specifying parameters upon selection.  This solution can work for any infotype that either stores variable data like infotype 0041 or that has sub types like infotype 0006 or wage types like infotypes 0014, 0015, and 0267.  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 limited option 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.

The details of your second two options will be covered in next week’s blog.  Stay tuned….

Danielle Larocca, Sp inifexIT

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.

An email has been sent to:






More from SAPinsider



COMMENTS

Please log in to post a comment.

Danielle Larocca

9/25/2013 8:59:33 PM

Hi there Leah and thanks for reading the blog. What you are experiencing is a common challenge using the Query based solutions in SAP. There are lots of limitations to what you can do. I do not believe the query you specify is possible in the way you want it to be built with a query tool. You can build this type of report using multiple queries run separately and merged together and analyzed in Access for example or use a tool like Easy Reporter or ABAP to create them. Query tools are great for simple inquiries but not designed to handle these types of complex items.

Leah Lesyk

9/25/2013 8:59:32 PM

Hi Danielle,

Great blog, thank you! I have a situation where multiple records are appearing in a query output, I'm hoping you can help me troubleshoot.

The user wants to see all the IT2002 (attendance) records for an employee for the month of January. In the query, she specifies to output the pay scale group and level from IT2002, plus the hours. She also chooses to display the pay scale group and level from the employee's IT0008 record (to compare).

If an employee had a pay scale group and level change (IT0008) during the month of January, the report then shows two lines corresponding to those two values.

For example, an employee has 10 hours of regular time on Jan 1, at MINER 1 (IT2002). On January 1, the employee's PSG & L from IT0008 is MINER 3. On January 15, the employee changed their PSG & L (IT0008) to MINER 2.

If the query is then run with Reporting Period = Other Period = 2013.01.01 - 2013.01.31, it picks up two lines for each attendance record - one with PSG & L = MINER 3 and one with PSG & L = MINER 2.

My question is, is this related to the table joins in the infoset, or something else technical? I guess the query is not "smart" enough to pull the PSG & L from IT0008 that was valid on the date of the attendance?

I can't seem to avoid getting multiple entries unless I pick a single date, which doesn't help for this user because they want to see all of the attendance hours in that date range.

Hope you can help me here!
Many thanks,
Leah


SAPinsider
FAQ