Creating calculated fields in your report (How to Eliminate Duplicate Line Reporting in Query Tools)

Danielle Larocca

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:

1. By specifying parameters upon selection (see last week’s blog)

2. Creating calculated fields in your report

3. Creating calculated fields in your data source (InfoSet) (in next weeks’ blog)

When I first learned how to use the SAP Query, transaction code SQ01, I did not know you could create calculations directly in the reports themselves.  It’s actually quite easy and does not require any programming skills.  You can create calculated fields to do basic mathematical equations or even more complex if…then type logic.  You can also use it for the scenario where you want to eliminate duplicate or multiple line reporting.

I refer to them as calculated fields but SAP calls them Local fields.  Local fields are new fields added to your existing reports in SAP.  Local fields allow you to generate new information from the fields that are already available to you for reporting.  You can create local fields from the Select Fields screen (screen #2) of SAP Query.

Often, when you want to perform calculations in SAP Query, you do so using existing fields. For example, you want to create a new calculated field that takes a person’s annual salary and increases it by a number or fixed percentage.  If you were doing this through traditional ABAP code reporting, you would need to write an ABAP program that references the technical table and field names and also be familiar with the relationships behind-the-scenes inside SAP tables.  Because SAP Query is an end-user reporting tool – no ABAP skills required, you can create your own short names for your fields so that you can easily reference them in your calculations.

Creating a Calculated (Local) Field in SAP Query

Start by creating an SAP Query report and include a handful of fields like Personnel No, Name, Annual Salary etc. Let’s use the example of taking the Annual Salary and adding a fixed amount to it.  Because many of the calculated fields that you will create will be based on existing fields, you often start the process of creating a calculated field by giving short names to some of the fields already in your query – you do this on the Select Fields screen (screen #2) of SAP Query.

1. Navigate to the Select Field screen (#2) of SAP Query
2. Place your cursor in the box for the field that you want to give a short name to and follow the menu path Edit > Short names > Switch on/off
3. A new column, labeled “Short name,” appears. Enter a short name for the field you want to use in the calculation formula.  I will give the Annual Salary field a short name of SALARY that I will use later in the calculation.
4. Next follow menu path Edit > Local field > Create and a dialog box will appear
5. Give your new local calculated field a short name, field description, and heading
6. Define the attributes of your new field by selecting it from the list of buttons or by saying your field has the same attributes as another field already in your report.  Using the dropdown box I will select the SALARY field which means that my new field will be a currency field just like the Annual salary field on Infotype 0008.
7. For the line that says Calculation Formula, use your short name and any math you want. In my example, I will take the Annual salary field short name SALARY and add 1000.
8. Select the green check mark icon to proceed and return to the Select Fields screen
9. Navigate to the Basic List Line Structure screen (#5) and add your new calculated field to your Basic List Line Structure screen (#5) so that the field is included in your report output
10. Press F8 to execute your report and your report will now have a new column that displays each employee’s Annual Salary + 1000.

That same process can be used to specify certain date types or wage types to eliminate duplicate lines in your report.  The calculated fields that you create will exist only in that report/query.  In next week’s blog I will show the most advanced version which is to customize your Infoset (SQ02) with an ABAP program so that the calculated fields are available in the data source so the fields are available for all reports.

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.