SAP Query Tool Exercise

Some SAP Terms - 1
Basis - The database and supporting platform for the SAP applications is called Basis. ABAP - Advanced Business Application Programming (ABAP) is the programming language developed by SAP for its numerouse applications. Client - In SAP a client is not a customer. An installation or instance of SAP is referred to as a client. It represents a single business entity but that entity may be a multinational corporation with several subsidiary companies. Transaction code SAP assigns a transaction code to label each user interface (one or more screens). For example, VA01 is the create sales order transaction code.
More SAP terms - 2

Logical database A predefined dataset from a group of base tables (where the data is actually stored). A logcial database handles how tables are joined. The user doesnt have to.
InfoSet This is a subset of columns from base tables or a logical database. Custom queries are built from InfoSets.
Query group A user that creates custom queries creates a user group and includes any user accounts that can use these custom queries. For example, you might create a user group for everyone in your department so they can use the queries you build.
SAP Display technical names (transaction codes)

Log in to SAP. Select Extras Settings
Check the option to display technical names. Click the checkmark, a.k.a Continue or Enter.
SAP Query Tool - Terms

Query areas
Standard (client-specific) Youll use this area.
Global (client-independent)
Query groups Group of users allowed to execute and maintain queries.
InfoSet Defined ata sources for queries.

Logical database predefined groupings of data from multiple tables (there thousands of tables in SAP).
Preparation for using SAP Query Tool

1. Create a query group. 2. Assign users to the query group.
3. Create an InfoSet.
4. Assign InfoSet to the query group. 5. Create query using the InfoSet.
SAP Query Groups 1

Create a query group (SQ03)
Naming convention:
z<your NMSU ID>
Note: All custom objects in SAP

should start with the letter Y or Z. This distinguishes them from standard features and objects within SAP.
Click Create then fill in a description when prompted
SAP Assigning users to query groups 2

Click Assign users and InfoSets
Include yourself in the query group (your SAP user account) and add the other user accounts for this company (config_$2 config_$8). Click the Save icon.
SAP InfoSet 3.a

To create an InfoSet (SQ02), open that transaction code screen. Make sure youre in the appropriate query area Standard Area then click the Choose button.
NOTE: You can transport an InfoSet from the global area to the standard area, if needed.
SAP InfoSet 3.b

Enter a name: Z<your NMSU ID> and click Create. Type an InfoSet description: z<your NMSU ID> - Test InfoSet. Select Logical Database and click the Search button.
In the search dialog box, type sflight for the table name and click Continue.
Select the first logical database (F1S) and click Continue. You can expand/collapse the tables shown in the left frame to see the fields in each table.
NOTE: The airline flight tables were created by SAP to use in training.
SAP InfoSet 3.c

You assign fields from the left frame to the field groups shown in the frame on the right. Only the fields you assign will be available in this InfoSet when you use the SAP Query tool. 1. First, select the field group on the right. 2. On the left side, rightclick a field and select Add field to field group.
See the next page for the list of fields to include and which field group to include them in.
SAP InfoSet 3.e

Be sure to add fields to the appropriate field group, as shown below.
Click the Save icon. Select Local Object when prompted. Click the Generate button (red & white ball).
SAP InfoSet 3.f

If there is an error when the InfoSet is generated, you will need to adjust the InfoSet. Errors can occur because of changes in the tables and columns in the Basis data dictionary. Adjusting the InfoSet will bring the table & fields names in sync with the current data dictionary.
1.
2.
Generate the InfoSet.

If an error occurs, in the error dialog box look for the Adjust button and click it.
3.
Generate the InfoSet again and there should be no errors.
NOTE: You can transport an InfoSet from the global area to the standard area, if needed.
SAP Assign InfoSet to query group 4

On the InfoSet initial screen, find your InfoSet (or type the name) then click the Role/User Group Assignment.
1 2 3
6 Click the Save icon.
SAP Query Tool: Creating a query 1.a

Open the SAP Query screen (SQ01)
Turn off (uncheck) the default Graphical Query Painter.
Select your query group with Shift-F7.
SAP Query Tool: Creating a query 1.b

Type the name of your query.
Naming convention: z<your NMSU ID>_##
Where ## is a number, such as 01.

Click the Create button. Select your InfoSet.
Enter the Title for your query.

Naming convention: z<your NMSU ID> Flight Report.
SAP Query Tool: Creating a query 1.c

Click the next screen button.
Check the field groups to include then move to the next screen.
Select fields to include in the query output. Move to the next screen.
SAP Query Tool: Creating a query 1.d

Selections: In the screen labeled Selections, check the selection fields shown. Click the Basic List button.
Basic List Line Structure: Enter line #s, sequence #s and check totals, as shown.
SAP Query Tool: Creating a query 1.e

Run the query with the F8 key or by using the menus.
The fields you checked in the selections screen appear in the initial query screen. This screen is use to filter output based on criteria you enter. Enter the data shown.
Click the Execute (clock) button.
SAP Query Tool: Creating a query 1.f

The output shows three different airlines with flights from New York to Frankfurt during the specified time period.
Similar to MS Access and SQL Developer query output, you can change the sort order and add additional filter criteria.
Query output can also be exported.
Another InfoSet - 1
Create a new InfoSet. The name should start with z and have your NMSU ID.
When prompted add a title and add a basis table, not a logical database. Table VBAP is for items listed in sales orders.
Another InfoSet - 2
You see the table shown in a graphical layout.
Add another table by clicking the icon the cursor points to.
Add: VBAK
Click the InfoSet button.
Another InfoSet - 3
Select Create empty field groups.
Select fields for the field groups. Be sure to select the field group on the right before selecting fields on the left. For Item Data, theres a long list of fields on the left. Youll have to comb through these to find the fields shown on the right.
Dont worry about the order. Only add fields to one field group.
Another InfoSet - 4
Generate the Infoset.
Hit the back button (arrow points left).

Make sure your new InfoSet is highlighted then click the assignment button. Select your query group and click save.
Query: List # of items sold - 1

Leave the InfoSet transaction and open Queries.
Type a new query name. Name it with z<yourNMSUID>_items.

Select your new InfoSet for this query. Enter a query title.
Click the Next Screen button.

Check the Item Data field group and click the Next Screen icon (upper left).
Select fields to include in the query. Move to the Next Screen.
Check the material number field. It will be used as a filtering criterion.
Click the Basic List button.

Add numbers for lines and sequences and check Total for the quantity field.
Run the query. Hit F8. The initial screen allows filtering criteria. Enter one product number based on your companys letter.

This example output lists the individual sales for one product and the total number of this product sold.

SAP Query Tool Exercise

Uploaded by

Copyright:

Available Formats

SAP Query Tool Exercise

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

SAP Query Tool Exercise

Uploaded by

Copyright:

Available Formats

Some SAP Terms - 1

More SAP terms - 2

SAP Display technical names (transaction codes)

SAP Query Tool - Terms

Standard (client-specific) Youll use this area.

Query groups Group of users allowed to execute and maintain queries.

InfoSet Defined ata sources for queries.

Preparation for using SAP Query Tool

SAP Query Groups 1

z<your NMSU ID>

Note: All custom objects in SAP

Click Create then fill in a description when prompted

SAP Assigning users to query groups 2

SAP InfoSet 3.a

SAP InfoSet 3.b

SAP InfoSet 3.c

SAP InfoSet 3.e

SAP InfoSet 3.f

Generate the InfoSet.

Generate the InfoSet again and there should be no errors.

SAP Assign InfoSet to query group 4

6 Click the Save icon.

SAP Query Tool: Creating a query 1.a

Turn off (uncheck) the default Graphical Query Painter.

Select your query group with Shift-F7.

SAP Query Tool: Creating a query 1.b

Where ## is a number, such as 01.

Enter the Title for your query.

SAP Query Tool: Creating a query 1.c

SAP Query Tool: Creating a query 1.d

SAP Query Tool: Creating a query 1.e

Click the Execute (clock) button.

SAP Query Tool: Creating a query 1.f

Query output can also be exported.

Hit the back button (arrow points left).

Query: List # of items sold - 1

Type a new query name. Name it with z<yourNMSUID>_items.

Click the Next Screen button.

Query: List # of items sold - 2

Select fields to include in the query. Move to the Next Screen.

Check the material number field. It will be used as a filtering criterion.

Click the Basic List button.

Query: List # of items sold - 3

Query: List # of items sold - 4

You might also like