Why create functional areas

ABAP Query allows you to evaluate data in the R/3 System. However, since the system contains several hundred thousand fields in logical databases, in tables, and sequential datasets, it is simply not practical to offer all these fields to a user for selection when creating queries. Therefore, before starting to create queries (using the component Maintain Queries), you create functional areas (using the component Maintain Functional Areas). Functional areas provide the user with a framework for defining a query quickly and easily.

Functional groups

When you create a functional area, you select a logical database from an application system. However, since one logical database can contain a vast number of fields, you combine fields together in logical units known as functional groups (see page ). A functional area not only allows you to restrict the number of fields and group them together in meaningful units, but it also allows you to define auxiliary fields and process them like database fields.

You can also read the long texts in additional tables (for example, the long text of an airline carrier in the table SCARR) and perform any necessary preliminary work. This means that you can evaluate sequential datasets just as easily as SAP databases. Starting with Release 4.0, a functional area can have a name of up to 24-characters. Previous releases were limited to four-character names. SAP now delivers functional areas in various applications.

What is a user group?

A user group is a collection of users that work with comparable data and accomplish similar tasks. Changes to functional areas and queries are immediately visible to all users.

The users assigned to a user group can only access the functional areas assigned to their user group. When creating a query, the user group controls which functional areas can be used. (However, user groups are irrelevant if a user has full authorization to all R/3 data.) Starting with Release 4.0, user groups can have 12-character names. Prior releases were limited to two-character names.

Using queries

If appropriate authorizations are in place, you can execute, modify, copy, and delete queries. Any modifications to queries affect all users of a user group. That is, if one user deletes a query, it is lost for all other users as well. All changes or new creations are at once visible to all users.

Starting with Release 4.0, queries can have 12-character names. Prior releases were limited to two-character names.

Logical database

To retrieve data, ABAP provides an extra read program called a logical database. This program reads the required data and provides it to you in the correct order. You only write the statements that process the data. To take advantage of this functionality for your report program, you must declare the logical database in the report attributes.

SAP delivers several logical databases in the application areas and gives a user the capability of creating their own. We strongly recommend that a basis/tools expert be involved in the creation of a logical database. To view the delivered SAP logical databases, follow the menu path: Tools ABAP Workbench Development Programming Environment Logical Databases, or use transaction code SE36.

Functional groups

If a field is not assigned to a functional group, it cannot be included in a query. Functional groups have two-character names and are functional-area dependent. There are no naming restrictions for functional groups.

ABAP Query Component

Menu Path

Maintain Queries

Tools ABAP Workbench Utilities ABAP Query Queries

Maintain Functional Areas

Tools ABAP Workbench Utilities ABAP Query Functional Areas

Maintain User Groups

Tools ABAP Workbench Utilities ABAP Query User Groups

Example: Bungee Corporation wants to view the account history for several G/L accounts at a timein a specified format. While they like many of the standard reports provided in R/3, they would prefer to change the order of the columns. Bungee Corporation believes that in the future they will include fields from internal tables, and thus would like to begin creating custom reports. Additionally, the company wants to be able to print the General Ledger history.

Note: In the example we use Release 4.0B. While this release has a different look-and-feel than prior releases, the underlying concepts and terminology remain the same.

Guided Tour

Choose Environment Functional Areas.

In Functional area, enter a name for the functional area (for example, ZZ_GL_DETAIL) you want to create.

Choose Create.

In the Name field enter a description for the functional area.

Leave Fixed point arithmetic selected to ensure that the decimal places are correctly set for the calculations.

In Logical data base, enter the name of the logical database you want to use for the functional area (for example, SDF).

To display the tables and fields available in the logical database, choose Display.

Select Table join.
Note: If you prefer to directly read a specific table or use a table join for multiple tables, then enter the name of the main table in the Table field and select either Direct read or Table join. Only one of two areas (steps 6 and 7 for logical databases or step 8 for specific tables) should be selected.

Choose Enter.

Choose the Create button (next to the Functional groups folder) to add two functional groups to the newly created functional area ZZ_GL_DETAIL.

Create the following two functional groups, A1 and A2 in the Create Functional Groups window:

<        A1 for Account Master Info

<        A2 for Account Transactions

Choose Enter.

Choose Expand to assign fields to the functional groups for queries. Once the nested detail is in view, the button turns from green to red.

Click line A1, Account Master Info, to highlight the group for field assignment.

To assign the fields to the functional group A1, click the minus (-) sign, next to the field names. The icon changes to a green plus (+) sign. The functional group A1 appears next to the green icon, indicating that the fields have been assigned to the highlighted functional group.

Continue adding fields to the functional group by repeating steps 1 and 3 (above) for each table that contains fields needed for your queries.

To select a different functional group to add fields to, double-click on the new functional group and perform steps 1 and 3 (above).

Selecting fields for functional groups

When selecting fields for your functional groups, select the field in the highest possible table. For example, company code (BUKRS) is available in tables SKB1, SKC1A, SKC1C, BKPF, BSIS, BSEG, and GSEG. The company code should be selected only from table SKB1 because it is the highest table in the logical database hierarchy that contains the company code. If the company code is selected from more than one table, it will appear multiple times to the users when creating queries. Multiple appearances can be extremely confusing.

The next two steps involve ABAP programming statements. You may want to ask a Basis/Tools expert to help you if you are not familiar with SELECT statements and basic ABAP code.

Click on the node for SKA1 to select the table in the Change Functional Area ZZ_GL_DETAIL screen.

Choose Extras in the toolbar to add an additional table.

Choose Create.

In Name, enter SKAT, the table to be added to the functional area. Leave Additional table selected.

Choose Enter.

Note that the selected table is SKA1.

The system defaults to the SELECT statement values it can determine. You must complete the missing entries, if any.

Choose Enter twice to return to the Change Functional Area ZZ_GL_DETAIL screen.

Be sure to add the fields from the additional table to a functional group. The additional tables fields will be listed under the table to which it was attached. In this example, table SKAT is attached to table SKA1.

Select the line BSEG.

Choose the Extras button to add an additional field.

Choose Create.

In the Name field, enter Correct_Amount, the name of the additional field to be added to the functional area.

Select Additional field.

Choose Enter.

In Description, enter an appropriate field description.

In Header, enter the column header text.

Enter the format of the field by either manually entering the type, length, output length, and decimals. Alternatively, you may specify the name of the field (for example, BSEG-DMBTR) whose formatting this additional field should mirror.

To enter the ABAP code for the additional field, choose Editor.

Enter the ABAP code that will determine the value of the additional field. In this example, we are checking the debit/credit indicator (BSEG- SHKZG). If we find an 'S' for debit, then the correct amount is equal to the local currency amount (BSEG-DMBTR). If it is not a debit, then it is a credit and the local currency amount must be multiplied by .

Remember to enter the periods at the end of each line!

Choose Save.

Choose Back.

Choose Enter twice to return to the logical database view of the functional area.

Double-click on the functional group A2 to select it.

To add the additional field (CORRECT_AMOUNT) to a functional group so that it can be used in queries, expand the BSEG table to show all the fields. Scroll down to the bottom and select the CORRECT_AMOUNT field.

Choose Generate to generate the functional area. This is the final step in creating the functional area.

Choose Back after the functional area has been generated.

The functional area must be assigned to a user group before it can be used in a query. From the previous screen (in step 3), choose Environment User Groups.

In User group, enter X1, the name of the user group.

If a user group does not exist, choose Create.

The rest of this example shows how to create a user group. If an existing user group will be used, choose Assign users and functional areas.

In the Users section, enter the user names of the users who should have access to the functional areas and queries of this user group. In this example, we added the user Tbecker to the user group.

Choose Assign funct. areas to assign the appropriate functional areas to the user group.

To assign the functional areas to the user group, select the appropriate checkboxes on the left side of the screen. In this example, we selected functional areas ZZGL and ZZ_GL_DETAIL.

Choose Save to save the assignments.

Choose Back twice to return to the main user group screen.

Now that the functional area and user groups have been set up, you are ready to build the query.

To go to query maintenance from the User Group screen, choose Environment Queries

Make sure you are working in the appropriate user group.

If not, choose the Other User Group button and switch to the correct user group.

In the Query field, enter a name for the new query. In this example, enter A1.

Choose Create.

You are now prompted to select a functional area. The list of functional areas includes all areas assigned to the user group you are working with. Double-click on the functional area you wish to use. In this example, double-click on ZZ_GL_DETAIL.

In Title, enter the title of the query, and in Notes enter any relevant notes. The notes are for reference only and do not appear in the query.

In Columns, enter the column size (in characters). In Lines enter the desired number of lines. In this example, the number of lines and columns is 65 and respectively. This should match a print format if the query is to be printed.

Choose Next Screen to continue.

Select the functional groups from which you want to choose fields. Remember that you are working with functional groups, not functional areas, in this step.

Choose Next screen to continue.

From each functional group, select the fields to be used in the query.

Use Page up or Page Down to scroll through the fields. In this example, the fields you select would be based on the desired report output (as shown in the sample report on page 27).

Choose Next Screen to continue.

From this list, select the fields that should be used as variables when executing the query. Some of these fields may already be variables in the logical database. If so, they will appear twice when executing the query. You may return to this screen and deselect them if they are duplicated.

If selection fields straddle multiple pages, use Page up or Page Down to scroll through the fields.

Choose Basic List to continue.

Enter the Line and Sequence for each field in the query. In the output for this sample report, recall that the account number and description appear above the transaction detail. This is because we specified that they would be on line . The transaction detail is on line . The sequence is the order in which the fields appear across the page.

Use Page up or Page Down to scroll through the remaining fields.

To view a full page of fields, choose Expand.

Select and order the sort fields in the Sort column.

The Total button is available on all numeric, currency, and quantity fields. This gives you the option to total the sort fields.

The counter field is new in Release 4.0 and allows you to count the number of entries in your query for the selected field. This will also total on the sort fields.

The Compressed display checkbox allows detail transactions to be suppressed on the initial screen (and then expanded if necessary). With compression on, you will see the account number and a total for the account. When expanded you will see all the related transaction detail.

Choose Next Screen to continue.

This screen helps you set up the sort fields.

To get text above each sort break, select Text.

To get totals for a sort level, select Total. This option works only if you have also selected Total for a numeric field on the previous screen.

To get counter totals for the sort field, select Cnt. (counter).

To get a blank line between account numbers, select BlnkLn.

To get a page break at each new company code, select NewPg.

Choose Next Screen to continue.

Each sort field for which you selected Text, Total, or Cnt. above has a separate screen (as shown here). You can specify the text that should appear next to each total and above each sort break.

Choose Next Screen to continue. Choose this button through each of the control-level text screens.

Each detail line of your query will have a line on the List line output screen. To get a column header for the list line, select Header line.

Selecting Ref. checks the referenced line (for example, line 02) which is the detail transaction line for an activity. If there is no activity for line 02, then line 01 is suppressed as well.

If the detail lines for an account break across pages, selecting Page Header repeats the first line (with the account number and description) at the top of the next page.

Choose Next Screen to continue.

The query gives the standard length of the field. You now have the option of shortening it.

If the Pos fields are left blank, the default is one space between each field. Entries in the Pos column determine where in the line you want the field to start. In our example, we want the account number to start in character position 02 and the text in character position 12. This means there will be 4 blank spaces between the account number and text (12 - 2 6 = 4).

Choose Next Screen to continue.

Note: You must remember where the line breaks occur (for example between the G/L account long text and the accounting document number). The long text is on line and the document number starts line .

This is the column header screen. To change a field header, double-click on the field header in the Page header section. In this example, we double-click on the Amount field.

Change the description in the popup box.

Choose Enter.

To enter a date, time, page, or name, put your cursor in the white line in the Page header section and type &%DATE, &%TIME, &%page or the appropriate variable name. To insert additional lines, double-click on the white line.

Choose Save.

Congratulations! Your query is now ready to run. Choose Execute to run the query.

To run a query, choose System Services ABAP Query.

Select the query from the list. In this example, user group X1 has only one query (A1).

Choose Execute.

Enter the variables.
In this example, enter:
in G/L Account
in Company code
in Open items at key date

Notice that the G/L account number and Company code fields appear twice because both the logical database and the query use these fields as selection criteria. Be sure to remove this duplication from the query.

Dynamic selections are also available because of the logical database. These selections should also be used instead of query selections.

Choose Execute to run the query.

The basic list shown here is a simple listing of detailed transactions for specific G/L accounts. In this example, 15 transactions have been posted to the G/L account based on the selection criteria specified at the time of executing the query.
From the report output, you may:

<     Expand or collapse the detail with the Basic List button.

<     Drill down (sometimes referred to as Report Call) to call up a variety of reports or transactions from this query. Drilldown capability is specific to the query.

<     Save the query to a file to be called up and viewed later.
Note that expand/collapse and the report call capability is not available in a saved list.

Creating variables

In the functional area, you can use available selection criteria to customize specific fields (variables) for entry at execution time. These new variables work for all queries created within the functional area. You can set up selection options and parameters that work just like the ABAP code statements Select-Options and Parameters.

With these selection options you can make the variables:

<     Work with their matchcodes

<     Mandatory fields

<     Display only

<     Highlighted

<     Set defaults

Table joins became available with Release 3.0C. If just a few tables are being used to create a functional area, tables joins can be more efficient than logical databases. However, logical databases are essential for hierarchical processing. Table joins should be considered for performance reasons when creating the functional area.

Only inner table joins are available in the standard 4.0x releases. Left outer tables joins are available in Release 4.5x. If left outer joins are needed in a 4.0x system, refer to OSS note 130837 on how to retrofit this functionality.

OSS note 48296 is also helpful when working with tables joins.

If the proposal did not create the intended join conditions, you may want to define additional join conditions. To do this, enter matching numeric values in the two-character space next to each field. For example, if Ind.: Flag material on MARD should match the Indicator: Flag material on MARC, enter a in the two-character space next to each of these fields. ABAP Query only allows join conditions between fields of a similar type.

Many customers like to drill down to display transaction codes for master records and detail transactions from queries. To add the Report Call functionality to a query:

You must be in Create or Change mode within the query.

Choose Goto Report assignment.

Assign any of the report types mentioned above to the query.