End User Reporting with

SAP Query (Everything You Need to Know)

Session Code: 2802

Danielle Larocca SignorileJCSDL@prodigy.net

Your Reporting Options in SAP

1. SAP Standard Delivered Reports2. Information Systems Reporting (EIS, LIS)3. Custom ABAP Reports (hard-coded reports created

by ABAP programmers)4. InfoSet Query and Quick Viewer5. SAP Query

Reporting Options Outside of the R/3 Environment1. BW (Business Information Warehouse and Data Warehouse)2. Third Party Reporting Tools (Crystal reports, Microsoft Access etc.)

What is SAP Query?

SAP Query, formerly known as the “ABAP Query” in versions earlier than 4.6 is an end user reporting tool that can be used to easily create reports from any application area in SAP

SAP Query offers users a broad range of ways to define reporting programs and create different types of reports such as basic lists, statistics, and ranked lists.

Application Areas

How Does it Work?

Query Groups(User Groups)

InfoSets(Functional Areas)

SAP R/3 Database

SAP Query SAP R/3 Security

Language Comparisons

How Long Does it Take to Setup The SAP Query?

The actual configuration of the tool takes less than 5 minutes to complete. Prior to setting up the tool you should review the slide titled “Administrative Decisions on SAP Query Use” found at the end of this presentation

Review Administrative Standards

Create Query Groups (User Groups)

Assign Users to User Groups

Create InfoSets

Assign InfoSet to Query Group

What is a Query Group?

A “Query Group” or “User Group” is a defined grouping area in SAP where queries are housed

Users groups are NOT about security groupings, they are only for logically grouping reporting users in categories

For example a user group called Benefits Group would contain all of the report users in the benefits department and all of their queries would be grouped together into a single user group to make accessing their InfoSets and queries easier.

Users can be in more than one user group and can have one set as their default.

Create SAP Query Groups

Navigate to Transaction Code SQ03

Type in a Query Group Name and select the Create button

Assign Users to User Group

From the main screen select the Assign users button.

Type in the SAP User ID’s for all users you want in the group and select the Save button

What is an InfoSet?

An “InfoSet” (formerly known as a “Functional Area in versions earlier than 4.6) is a prearranged grouping of data from the SAP R/3 database

The SAP Query tool uses an InfoSet as the source of the data used for reporting. It can be a table, a program or the most popular option, a Logical Database (LDB).

The LDB is a hierarchal pre-arranged subset of data. There are hundreds of LDB’s delivered with your SAP system.

Create an InfoSet

Navigate to Transaction Code SQ02

Type in a InfoSet Name and select the Create button.

Create an InfoSet Continued . . .

Give your InfoSet a name

Select a Logical Database and then select the Enter key

Create an InfoSet Continued . . .

The InfoSet screen is divided into 3 areas.

A – Lists the tables contained in the LDB

B – Lists the “Field Groups”

C – Field level detailA

B

C

Note: InfoSets look different in version 4.6 (shown here) than in earlier versions.

Assign Fields to Field Groups

All of the tables from your LDB are listed in the “A” window. You can right click on the fields within the tables to add them to the Field Groups shown in window B.

Only the fields added to field groups will be available for reporting using the SAP Query.

Once you have added all of the fields you wish to add select the Save button followed by the Generate button.

BA

Assign InfoSet to Query Group

From the InfoSet main screen, select your InfoSet and then select the User Group Assignment button.

The User Group that you created in step #2 will appear, select it and select the Save button.

The configuration of SAP Query is complete.

Configuration of SAP Query Recap

The configuration of the tool itself takes only 5 minutes to complete and once it is configured you can create an unlimited number of queries from it.

At this point you are probably starting to think about some of the administrative options regarding the configuration and the use of the SAP Query, we will address those questions in a slide at the end of this presentation titled Step #1 Review Administrative Standards.

Lets create a report using the SAP Query!!

Create a Report with the SAP Query

Give the report a name

Select field groups

Select fields

Add fields to the reports selection screen

Specify the order and output of your fields

Selection screen

Your finished report

Creating an SAP Query Report - Step by Step

Navigate to the SAP Query (SQ01).

Type in a name for your report and select the Create button.

You will be prompted to select your InfoSet.

Screen #1 – Title Format Screen

Stores the name of the report, page size and other basic information

You can navigate between the basic screens using the directional arrows on the toolbar

Screen #2 – Select Field Groups

This screen is where you select the Field groups that contain the fields that you want to output in your report.

(This list of Field groups is from the InfoSet we created moments ago)

Screen #3 – Select Fields

This is where you select fields from the Field groups (highlighted in blue) indicated on Screen 2 to be included in your report

Screen #4 – Add Fields to Selection Screen

This will list all the fields you selected on the previous screen.

From here you can select fields to appear on the selection screen (screen #6) to be used as additional selection criteria.

Screen #5 – Basic List Line Structure

Here is where you set up what you want your report to look like.

Specify the order and output of your fields and indicate any counts or totals.

Select Save to save your report

Select F8 to Execute your report

Test

Screen #6 – Selection Screen

The selection screen lets you specify exactly what data you want to see in your output. Fill in any specifications and select the Execute button to see the finished report.

The fields added in Step #4 (Plane type and Airline Carrier ID) are now displayed on the selection screen under Program Selections.

Enter your selections and select F8 to Execute your report

Screen #7 – Your Finished Report

In Ver 4.6 your default report is displayed in the in the “SAP List Viewer” shown here.

Versions earlier than 4.6 your report is displayed as a basic ABAP list by default.

Comparing Report With Screen 5

SAP Query

Creating SAP Queries is very easy using the 7 Basic screens outlined.

In addition to these 7 basic screens – there are 7 additional screens that can be used to further detail your reports.

I refer to the first 7 screens as the “basic screens” and the latter seven screens as the “advanced screens”. The strategy for training the tool is best when you teach the basics (screens 1-7) and then, once mastered, teach the remaining 7 advanced screens. This is my lingo only. In SAP’s eyes there is no distinction between the screens.

To navigate to the advanced screens, begin from screen #5 Basic List screen and use the forward arrow to sequentially advance through each of the advanced screens.

SAP Query Advanced Screens

Add Subtotals to the report

Change Subtotal texts

Format the list line

Alter the column widths, add colors, hide leading zeros and create templates

Insert text to appear before output for each line

Create custom headers and footers

Create graphics (charts and diagrams)

SAP Query Sample Report

This screen increases the options you have for varying the layout of your list.

Any field indicated on screen 5 as a “Sort” is available here

You specify sub-totaling from this screen.

Screen #8 Add Totals to the report

See What the Subtotal Looks Like

Screen #9 - Change Subtotal Texts on the Report

This screen increases the options you have for varying the layout of your list.

Any field selected on screen 8 for subtotaling is available here

You can modify the way the sub-total texts appear on your report from this screen.

See What the New Subtotal Text Looks Like

Before

After

Screen #10 - Change Line Output Options

This screen increases the options you have for varying the layout of your list.

You an change the color of the list, remove the headers and insert blank lines or pages.

See What the New Changes Looks Like

Before

After

Screen #11 - Field Output Options

This screen increases the options you have for varying the layout of the columns in your list.

You can change the color of a column, vary position in the file, change column widths, hide leading zeros and create templates.

See What the New Changes Looks Like

Before

After

Screen #12 - Inserting Text Before Data Output

Any field selected on screen 11 for template is available here

You can insert texts into the report output for each individual line item.

See What the New Changes Looks Like

Before

After

Screen #13 - Create Custom Headers & Footers

You can insert Headers and Footers into your report, including the insertion of page numbers, dates, etc.

Shortcuts&%DATE &%TIME&%PAGE

See What the New Changes Looks Like

Before

After

Screen #14 - Graphics

One-line basic lists, statistical lists, and ranked lists can be displayed in graphical format.

See What the New Changes Looks Like

Before

After

How Long Does it Take to Setup The SAP Query?

The actual configuration of the tool takes less than 5 minutes to complete. Prior to setting up the tool you should review the slide titled “Administrative Decisions on SAP Query Use” found at the end of this presentation

Review Administrative Standards

Create Query Groups (User Groups)

Assign Users to User Groups

Create InfoSets

Assign InfoSet to Query Group

Review Administrative Standards

1. What will be your client/transport strategy?A popular best business practice is to design the InfoSets in your development environment and maintain your Query(User) Groups and the Queries themselves in your live productive environment.

2. Will objects be created in the Standard or Global Application area?A popular best business practice is to create objects in the Standard area and to keep them client specific.

3. Who will “own” the InfoSets?A popular best business practice is to determine ownership of the InfoSets. Because they can be completely customized to your needs, a well trained technical member of the team should “own” them, work on them in the development client, move them to QA for testing against queries created in QA and once they pass testing, move them to live production client.

For more information on the administrative decisions read the SAP Professional Journal Vol 4, Issue 2 March/April 2002

Other Cool Things that SAP Query Can Do

Create local fields within the query and do calculations (hire date + a year, salary + 4% etc.)

Add additional ‘tables’ to your existing logical database Use it to create interfaces (flat files, positional files) Schedule queries to run behind the scenes on a schedule

(weekly, monthly etc.) Add queries to transaction codes so that they be run by users

who do not have access to the tool Insert symbols and icons into your reports Use the query for standard downloads to Microsoft (Excel,

Word, Outlook and Access)

Some Popular SAP Query Questions

Is the SAP Query a Resource Hog?

Proper Configuration is the Key to Ensuring that the SAP query Does Not Become a Resource Hog!

• Enforce the use of Logical database in the creation of InfoSets (avoid the use of custom programs or direct table reads for transactional based tables)

• Properly train your end users on the use of selection screens and variants

• Enhancements made to the tool (formerly known as the ABAP Query) were made in ver 4.0 and later including enhanced database indexing to improve data retrieval

Can SAP Query Be Used for Payroll Results?

You can evaluate payroll results using a query. A number of predefined infotypes are available for the evaluation. The wage types required for the evaluation are entered in these infotypes:

You can either activate these predefined infotypes or create your own infotypes using the same structures. You can enhance these infotypes by using additional wage types and then activating the infotypes. This makes the necessary data, from the payroll results, available on the PNP logical database.

More Info: http://help.sap.com/ select the Find button and type in “Infotypes for Evaluating Payroll Results ”

Want More Information?

#1 Visit the ASUG Website and review these past conference presentations2002 Conference Session Code “4024” Human Resources Reporting Using the ABAP Query (version 4.0 + 4.5)2001 ConferenceSession Code “2810” End-User Reporting with ABAP Query (version 4.0 + 4.5)2000 ConferenceSession Code: “2601” End User Reporting in SAP (version 4.0)

#2 Review the SAP Professional Journal ArticlesSAP Professional Journal Vol 4, Issue 2 March/April 2002 Reconsidering the ABAP Query for Your Reporting Needs, proper Configuration is the Key to Success (versions 4.0 + 4.5)SAP Professional Journal Vol 5, Issue 3 May/June 2002 Create Powerful reports Quickly and Easily: A train the Trainer Approach to Learning and Teaching the ABAP Query Tool (versions 4.0 – 4.6)

Or stop by their SAP Professional Journal booth at the vendor fair for more information

Thank you for attending!

Please remember to complete and return your evaluation form following this session.

Session Code: 2802