I moderated a Web forum with HR Expert author and SpinifexIT executive consultant Danielle Larocca on SAP reporting options for HR, on June 22, 2011.
For the full Q&A, you can view the questions from Insider Learning Network members and Danielle’s responses in the HR Forum, or read excerpts from the transcript of the Q&A, below.
Make Sense of Your SAP ERP HCM Reporting Options:
Q&A with HR Expert Danielle Larocca
M.S. Hein (Moderator):
Welcome to today’s forum on SAP HCM reporting with Danielle Larocca!
Danielle, thank you for joining us today! Before you respond to questions, I'd like to start with one.
I know at this year’s HR conference in Amsterdam there was a lot of talk about ad hoc query versus SAP query, and there is a lot of confusion out there on this topic. What are your thoughts?
That’s a popular question.
Both Query tools—SAP Query and the Ad Hoc Query—can be used to create basic reports of your SAP HCM data. The Ad Hoc Query is a quick single-screen view for a basic look-up. With the Ad Hoc Query, formatting and advanced options are not available and the tool is designed for single-use queries—not those that can be used over and over again. The SAP Query on the other hand is a more robust solution. It allows for formatting, colors, calculations, sorting, sub-totaling, graphics, etc. The tool can be used to create not only basic list reports but also Statistic and Ranked lists as well. Reports designed with SAP Query are designed to be used over and over again and can be scheduled as jobs, linked to transaction codes, etc.
There is a big misconception that the Ad Hoc Query is the only tool available for SAP HR reporting. Although around since the 4.0 days, it is only in recent years that the SAP Query tool has been become popular.
Hi Danielle. Is there a way end users can have the ability to run ad hoc reports in background mode and have the output sent out to them over email?
I would like to have end users have the ability to execute reports faster and at the same time would like to avoid them the need to access various transactions to get the output extracted.
With the Query tools you can schedule them as jobs and have them sent to files or email but you will need to write an ABAP Function module to d
o that. I have included the instructions on how to do that below.
CALL FUNCTION 'SX_TABLE_LINE_WIDTH_CHANGE‘
EXPORTING LINE_WIDTH_SRC = 134
LINE_WIDTH_DST = 255
TABLES CONTENT_IN = bufferpdf "table from FM
CONTENT_OUT = out_table
EXCEPTIONS ERR_LINE_WIDTH_SRC_TOO_LONG = 1
ERR_LINE_WIDTH_DST_TOO_LONG = 2
; ERR_CONV_FAILED = 3
OTHERS = 4.
Regarding Anand’s question, I would also like to know about Standard Audit reports for employee data changes/new hires, and with the ability for our business users to run themselves.
I have read your post about tips for creating HR and Payroll report SQ01; is it safe to give access to business users?
IF the reports created via SQ01 are based on an SAP standard delivered logical database (like pnpCE), then yes it’s the same as them having access to the reports in the Easy Access menu. There are conference presentations available on this site that provide the instructions for how to set it up correctly such that the security and performance are not a concern for end users using the SAP Query.
And of course if you want to see the only real-time reporting soluti
on that runs live inside SAP in real time for all core SAP HR and Payroll data that is SAP certified—check out www.spinifexit.com/easy-reporter/a>.
Hi Danielle. Can you tell me a little about how you set up subtotals with an SAP Query report? Thanks!
Let me start by saying that there are two ways to design reports in SAP Query. There is a graphical view (which I think is cumbersome and difficult to work with) and a standard view. By default SAP has the graphical view turned on and when you go to create reports and add subtotals it’s easy to get lost. What I recommend is using the standard view.
Go to transaction code SQ01 and follow menu path Settings > Settings > and unmark the box that says Graphical Query Painter. Now the tool will be more user-friendly to use. The SAP query itself has seven different screens
Screen 1 – Give it a title
Screen 2 – Pick the infotypes
Screen 3 – Pick the Fields
From here you can select the BASIC LIST button. It is on this screen where you specify your sorts and subtotals. This screen will list all of the fields you want in your report. Next to each field will be a box to say if you want to sort on it (and you can insert a number saying 1 first sort by this field and then pick another field and enter a 2 to do a secondary sort, etc.) Next to that is a small checkbox called Total. Any field that you wish to subtotal on should have this box marked.
Hey Danielle. What truth is there that SAP is no longer
supporting or enhancing both AD Hoc Query as well as SAP Query in R/3 and that any new functionality will only be in BW?
Oh rumors—who knows if it is true. My opinion on it is that the Query-based tools are quite limited in what they can do and without a general overhaul of their entire design they will not be able to keep pace with all of the changes that SAP keeps making. I think this is especially true with SAP’s newer modules E-Learning, Compensation, etc.
Do I think BI/BW is their go-to solution for everything in the future? I do not know how it could be as again it is not designed for transactional-level reporting and it is not real-time. The use of BI, even if it is no longer licensed separately, is a huge financial commitment to develop and maintain. Each new bit of data that you want to store in the BI/BW warehouse and report off of requires configuration, InfoCube design, extraction, etc. All that and the data is still not real-time and often not to the transactional level so important to HR and Payroll.
Hi Danielle. In our company we are using ECM functionalities for Merit, Bonus Recommendations and also for Appraisals (Past performance Ratings). We also have HCM and BI/BW installed and use them for HR administration and Data Reporting, respectively. Very often we run into the question of Reporting Abilities of HR Reporting vs. BI/BW.
Is there a document that gives a comprehensive picture of what are the abilities of the SAP standard offerings for these two modules?
This just may be the most popular question I am asked and I intend to write an article on it in the near future. In the interim I have a chart showing each SAP ERP
R/3 solution and what it does in the article that this Q&A is based on. I mention BI/BW separately and here is why. In a nutshell it comes down to this:
In your standard SAP system you have five pre-delivered options for SAP HCM reporting.
1) Standard reports delivered with R/3
2) The Ad Hoc Query tool (InfoSet Query)
3) The SAP Query
4) The QuickViewer/SE16
5) Custom hard-coded ABAP reports
In addition to these five SAP HCM reporting solutions, you also have the option of installing a separate module for SAP NetWeaver Business Intelligence (sometimes referred to as BI or BW). This is a separate SAP module that serves as a data warehouse where data can be exported from various SAP modules and other computer systems for strategic analysis and reporting.
BI/BW is an amazing reporting solution that drives strategic reporting for organizations and can allow you to report on data across your organization (HCM, Finance, etc.). It is important to note that SAP NetWeaver Business Intelligence is n
ot designed for real-time reporting and is not designed for transactional reporting, which is so important for HR and Payroll.
Now, regarding the specific modules you mention—you may find yourself limited with the Query-based tools for reporting because that information in the newer incarnations of Compensation, Performance Appraisals, etc. is not traditionally stored on infotypes making access to that data more challenging.
Hi Danielle. My question is in line with Anand’s earlier post. We recently implemented SAP HCM and are looking to leverage the data. Is there a documented best-practice approach companies have taken with SAP reports, to leverage the data and provide value to the business/organization?
The decision and use of any reporting solution in SAP requires that you plan ahead and make sure that you have all of your administrative bases covered before you dive right in and start creating reports. The first item you must look at is what your report distribution strategy is going to be (email, transaction codes, portal, etc.) and who are you going to allow to create reports—which departments, who owns the reports, etc. Often companies make the mistake of just jumping right in and start using whatever tools their onsite consultants are familiar with. This is risky because of the limitations of many of the standard options; for example, SAP Query, Ad Hoc Query, etc., are very limited in the data that they can retrieve, which leaves a lot of organizations creating custom ABAP reports -- which is a costly endeavor that requires a lot of resources to maintain.
I do not have a quick 3-minute answer for you because it is a large, important decision and
will impact your companies’ ability to produce accurate information in the future. My advice to you is read through the article that accompanies this Q&A and then let’s have a quick chat (I’m not a salesperson—I can just give you better guidance!). [Editor’s note: It's not to late to register for the free article download, click here.]
Hello Danielle. While using Ad Hoc Query, the selection criteria works very well for inclusive conditions. For example, PSA - xyz, ESG – 99, etc.
But there are cases when the query does not fetch the same results when we use the exclude condition, as in PSA xyz, all ESG excluding 7 and 8.
This is the case particularly when there are multiple exclusions. What would you suggest?
A major complaint of the Query-based tools, SAP query and the Ad Hoc Query, is their inability to effectively report on negative conditions (show me who is missing an infotype 0008, for example).
I agree that the Ad Hoc Query has trouble with EXCLUDE conditions although I have not experienced this problem with the SAP Query. Using the Advanced selections button on the Selection screen gives you the option to include/exclude values or ranges; each has its own tab. I’ll agree it sometimes d
oes not work for ranges (especially those that are alphanumeric) but I find that I have more luck using the EXCLUDE tab on the selection screen than I do using the Exclude option on the field itself.
I am curious to get your take on the new Strategic Workforce Planning as I was surprised it was the first application built for the in-memory technology.
Also curious if you know of many customers using the new HR BO Dashboards as from what I have seen adoption has been slow.
Great job providing your expertise to the ILN!
Hi Jarret. Leave it to you to ask the hard question! No, I am actually not familiar with any customers using the new HR BO dashboards although I am curious to see how it works.
Hello Danielle. There are many requirements where we need to query on OM and PA data in one report. The most common requirement is to report on the employee’s manager.
It is my understanding that this information is not readily available in infotypes and needs to be derived from OM relations.
Is there a way to include objects and their relationships and link the positions with the employee numbers, such that both OM and PA data can be queried on the same report?
Our users use Adhoc Query; can we built these kind of reports using ad-hoc query?
According to SAP this is possible using the SAP Query although I have faced struggles with this one myself. Here’s the answer, SAP delivers logical databases (DataSources) containing all SAP ERP HCM infotypes to use for reporting. You simply select the infotypes and fields in which you are interested in reporting:
-PAP is normally used for recruitment data
-PNPCE is normally used for PA and concurrent employment
-PNP is normally used for PA data and includes Infotypes 0000-0999
-PCH is normally used for PD data and Data related to an object.
SAP Help recommends that you begin using the standard logical database PCH and when you design the report you select the PA infotypes to be included when running the report. Then when you go to execute the report, on the selection screen, specify an object type and enter an evaluation path. I have not been able to get this to successfully work to date for anything more complex than a basic field from PA and a field from an Org Management infotype.
For third-party options, the only SAP certified solution that I know of that makes this possible can be found at www.spinifexit.com/easy-reporter/a>.
Hi Danielle. We have used Enterprise Compensation Management for Annual Increments in our Company this year. However we found it very challenging to take out reports on specific fields like:
1. New Salary
2. New Comp-ratio
3. Reference Salary
4. Notes captured by Line Managers etc.
As these fields are not stored against infotypes and are populated in Portal using specific Function Modules and in cluster tables, is there a way of reporting these fields through Ad hoc Query or SAP Query?
I agree that the standard Query-based tools do not easily give you access to this information. It is, however, possible to customize your InfoSet with ABAP to get at this data. This is not something I would recommend doing ligh
tly because it essentially would call a function module to retrieve the data that you want and make your use of the Query tool more complex. I have worked with organizations where we have done exactly that (and I am happy to share the code we used) although I’m sorry to say in its current form your Query-based options are quite limited in reporting this information on their own without customization.
Scenario: A report is run using Ad hoc Query where 10 fields have been marked for output. This report is run for a duration (let’s say current year). Now the output contains multiple rows for each employee number depending upon the number of times these fields have changed uniquely.
In this case we are only interested in tracking one single field and the changes made to this field in the current year, and for all other 9 fields we want the latest record. Is this something possible on Adhoc/SAP Query?
That may be the second most popular question I regularly hear and the #1 complaint about the Query-based tools. They produce multiple rows per employee, making it very difficult to discern the appropriate row, last row ending in 12/31/999, and/or consolidate the multiple lines of data into a single row. The way that SAP stores the information causes this to happen. You would have to build something custom in ABAP or use a third-party solution to get away from this problem.
Using PNPCE database, it’s easy to report on Employee and OM Object, like Position, which is directly linked to employees. However we do struggle to report around other objects and their attributes which are maintained against Position.
I do understand the logic for why it’s not easy to report on t
hem, but is there an option to report on following:
1. Ethnic Origin
3. Payscale Group/Level maintained against Job
The ethnic origin can be reported from the Infotype 77 directly via PNPCE. The exemption status and pay scale group and level (if you want the ones assigned to the Position object) could be read from the PD infotypes (via the position or job respectively) but you will run into the same challenges of the cross PA/OM reporting in a single line, as mentioned earlier.
Hi all and thanks for giving me the opportunity to participate in the Q&A. I hope that I was able to answer your questions satisfactorily.
Much of my work to-date has been about creating workarounds to address the long list of limitations inherent in the SAP Query tools for SAP HR and Payroll. Not long ago, I learned of a company called SpinifexIT that makes an SAP reporting tool for HR/Payroll called the Easy Reporter. I witnessed a demonstration and came away highly impressed. The SAP-Certified tool allows you to report on everything inside SAP in real-time. The tool gives you access to all HCM data including Master Data, Payroll Results, Finance Postings, Time Clusters, Tax Reporter, CATSB, Audit logs, PA and Org. Management combined, and more.
I don’t have a background or interest in sales and I do not want to sell you anything. I’m just an SAP HR professional who came upon a great product that addresses a whole host of limitations in SAP HCM reporting that I’ve been focused on for a number of years. I was so impressed with SpinifexIT’s Easy Reporter tool that I joined the company a year ago and I now manage their operations in
North America. My job is essentially to keep all of the clients in North America happy, which is easy considering the tool works so well. If you are interested in learning more feel free to contact me or check out the company site and a member of our sales team would be happy to set you up for a 30-minute WebEx demo of the reporting solution.
Thanks to all who posted questions and followed the discussion!
And finally, thank you to Danielle for taking the time to respond to these questions.
Danielle Larocca is a leading HR consultant and an HR Expert author, who has written more than 24 articles, including Best Practice Design for HCM Personnel Structure and Employee Status Fields and Create Statistics Lists in SAP Query for a Summarized Analysis of Your HR Data. For more tips and articles like these, subscribe to HR Expert.