Lesson 26 Techniques for List Creation and SAP Query

TECHNIQUES FOR LIST CREATION AND SAP QUERY:


The QuickViewer is a tool for developing ad hoc reports that is new in Release 4.6A. You can start the QuickViewer using the menu path QUV-1.

The QuickViewer can use a database table or a database view as a data source. Lists can be generated using the fields in the data source specified. Two modes are available for this: basis mode and layout mode The QuickViewer provides interfaces, for example, to the EIS, ABC analysis or the ALV Grid Control. The list can also be processed further in external programs, such as Word.

The generated list can be saved and then displayed again in the QuickViewer. Selection criteria are also saved along with the list, and can be queried again at any time.

Each user defines their own user-specific QuickViews which only they can display. This means that you cannot copy other users' QuickViews. You can, however, compile an SAP Query from a QuickView, if the QuickView uses a functional area from the standard system as a data source (see unit 'SAP Query - Creating Lists'). The query is then visible to the user group.

QuickViews are not connected to the correction and transport system.

You must name a data source in order to generate a QuickView. The data source can be a database table, a database view, a logical database, a table join, or even a functional area of SAP query. The functional area must lie in the (client-specific) standard area.

You can access the specified data, but you cannot extend it with additional fields (also see Local fields under SAP Query).

When you specify a table join as the data source, you have to define the join before you can structure the list in Query Painter.

You define the table join graphically. You have to specify the links between the tables, and you can have the system propose a value. It does this using information from the Dictionary .

You determine the resulting quantity by deciding on either Inner or Left Outer Join logic. For example, if you only want to output airlines from table SCARR in a list when these airlines have flights in table SPFLI, this corresponds to the Inner Join logic. In contrast, if you want to output all the airlines regardless of whether flights exist in table SPFLI, then you would link both tables using Left Outer Join logic. In this case, the left table is SCARR.

Alias tables enable you to use the same (database) table several times when defining the join

In basic mode, the screen is divided into four areas. The available fields (data source) are displayed to the left in tree form. Further information on how to work in the basic mode is displayed in the lower left window. You can maintain the title and comments and control the output (list or Excel) in the upper right area. This is also where you control the list structure, set the sort sequence and define the selection criteria. You can branch to the online documentation from the lower right window.

You can structure your Quick View using two table controls. Select the fields you want in your list in the right table control and use the transfer functions to move them to the left table control ('List fields'). You can also control how many lines the list should have (using the 'Add line' function) in the left table control ('List fields').

Follow the same procedure for the sort and selection fields: select the fields you require in the right table control and copy them to the left control.

You can structure your Quick View using two table controls. Select the fields you want in your list in the right table control and use the transfer functions to move them to the left table control ('List fields'). You can also control how many lines the list should have (using the 'Add line' function) in the left table control ('List fields').

Follow the same procedure for the sort and selection fields: select the fields you require in the right table control and copy them to the left control.

When you create a list with a report, the data is usually retrieved via a logical database, processed by the report and then output as a list.

Queries evaluate data and can be created without any prior programming knowledge using the SAP Query tool.

The query results in a sequence of screen fields which you use to describe the line structure and list layout. Starting in Release 4.6A, you can use the Query Painter to add graphics to query lists.

When the query is started, an internal report generator creates a program that corresponds to the list definition. That program then reads the data, processes it, and outputs the data as a list. The program is named AQmmbbbbbbbbbbbbqqqqqqqqqqqqqq. You can display the report names with the menu path displayed in appendix documentation AQL-1.
mm - encoded client (standard area) or ZZ (global area)
bbbbbbbbbbbb - Name of user group (12 places)
qqqqqqqqqqqqqq - Name of query (14 places)
Spaces in query program names are replaced with '='.

The administrative tasks in the query environment include creating functional areas and user groups, as well as assigning the functional areas to the user groups.

The functional area determines the tables (and the fields of those tables) to which a query can refer.
Functional areas are frequently based on logical databases.

Users may create and start queries only when they belong to at least one user group. A given user can belong to several user groups. Users in a user group all have the same privileges.

Functional areas are allocated to a user group; the members of a group can access the functional area to which the group is allocated.

A functional area can be allocated to several user groups.

Several functional areas can be allocated to a user group.

Queries are always created for a specific user group and a specific functional area. Users in a user group have access to all the queries allocated to that group.

If you have been allocated to several user groups, you can switch within these groups.

A query is always created from a specific functional area. The functional area must be allocated to the user group in which the query was created.

You can access all queries that have been allocated to your user group.

If you are authorized to define a query with a functional area, you can list all the queries for that functional area.

You can only copy a query from a different user group to your user group when the functional area of the query to be copied has also been allocated to your user group.

The query results in a sequence of screen fields in which you use

Selection (checkboxes)
Number assignment (sequence, sort, ...)
Texts (headers, group level texts) to determine the line structure and the list layout.

Starting in Release 4.6A, you can use the Query Painter to add graphics to basic lists.

You can use SAP Query to generate different types of lists (partial lists):

Basic List: Single line or multiline. Multiline basic lists can be compressed.
Statistics, ranked lists: Require a numeric field. Data can be compressed.
You can combine different partial lists in a single query. Starting in 4.6A, you can also print the individual partial lists.

You can also define local fields within a query, which means you can calculate new values from the collected data.

While you cannot generate interactive lists you have defined yourself, some standard interaction functions are available. For example, you can pass on the generated lists for further processing (Excel, EIS, ABC analysis), display them in graphical form (SAP Graphics), save them, or edit them in table form (table control and ALV grid control).

You can use the menu paths displayed in appendix documentation AQL-2 to create, change, and execute queries with the ABAP Workbench.

Queries are created either in the standard area or the global area. A query area covers a set of query objects that are internally complete and consistent - this means objects with the same name but with a different meaning can exist in the various query areas. The global and standard areas have separate namespaces.

The standard area is client-specific and is not linked to the Workbench Organizer (WBO). The query objects in the global area are available in all clients and linked to the WBO. If you create a query in the global area, you have to assign it to a development class.

When creating a query, you must first choose a functional area. The system displays all the functional areas that have been assigned to your user group. Once you have chosen a functional area, you cannot modify your choice: the functional area is the basis for data retrieval.

SET/GET parameters AQW and AQB are available and can be used in your user parameters to define default settings for the query area (global area: AQW = G) and your user group.

When selecting fields, the system leads you through the following screens:

Title, format:

Used to assign the query title You can set the page layout by making entries for the format. You can also set additional characteristics for the query with special attributes.

Functional area

Functional areas are divided into functional groups. These form logical groups of data. You choose the required functional groups here.

Field Selection

Here you choose the required data fields of the previously selected functional groups. If you require local fields, you can also define them here.

Selection fields:

You can define fields to add to the selection screen and further limit the selection criteria.

Depending on which type of list you want to generate, edit the screen fields or use layout mode (Query Painter) for the basic list. You always have to use the Field selection screen field to create local fields.

By defining local fields, you can generate additional information from the fields that are available in a functional area.

If pre-existing fields are required for the definition of a local field, short descriptions must be provided (see the menu path displayed in appendix documentation AQL-3).

A short description can be assigned for each field.

Short descriptions are also used to retrieve values of the corresponding fields in the list headers.

You can define local fields for a query (menu path AQL-4)

Local fields are defined with calculation rules. In the simplest case, calculation rules consist of a single formula formed with normal mathematical rules and consisting of operands and operators.

The calculation of a field's value can be made condition-dependent. In this case, values are calculated according to certain rules only when a particular condition is met. If the condition remains unmet, the field receives a default value. Multiple conditions are allowed.

You can sort the values of key columns of statistics in ascending or descending order.

Numerical fields in statistics are accumulated. Statistics only make sense with numerical fields.

Statistics allow you to display the average value, the percentage breakdown, and the number of records read for each numerical field.

You can define up to 9 statistics individually or as a supplement to a basic list.

If you work with different currency or quantity fie lds within statistics, you must enter a reference currency or a reference unit for each field, so that the system can convert it into that currency or unit.

The list displays the conversions processed by the system. In the event of an error, the system logs any conversions that did not take place. In addition, the system highlights the affected currency or quantity fields within the statistics.

With the appropriate definition, subtotal lines can also appear within statistics. If you compress the statistics, the system displays only the subtotal lines and the grand total.

Ranked lists are special forms of statistics. However, they are always sorted based on one numerical value. This value is referred to as the ranked list criterion. In addition, the system only outputs a certain number of records.


Ranked lists are sorted according to only one fie ld, and the number of output lines is limited.


You can define up to 9 ranked lists individually or as supplements to a basic list.

You can also define each ranked list as statistics.

The rules for conversions of currency and quantity fields also apply to ranked lists.

To create basic lists, use the Query Painter. In the Query Painter, the screen is divided into four areas. The available fields (data source) are displayed to the left in tree form. The list structure is displayed with sample data in the upper right area. Information for the currently active element is displayed in the lower left portion of the window. Links to documentation and any warnings that are output while formatting the list are displayed in the lower right section of the window.

You can edit list characteristics (frame, width) by selecting a field, right-clicking with the mouse and choosing 'List options' from the menu. While editing, you are working in the lower left window. If you have created new characteristics, then you need to confirm the values you have changed using the APPLY function.

You can edit list line characteristics (color, separators, and so on) by selecting a field, right-clicking with the mouse and choosing 'Line options' from the menu. While editing, you are working in the lower left window. If you have created new characteristics, then you need to confirm the values you have changed using the APPLY function.

You can edit field characteristics in the lower left window by selecting the appropriate field. Further field characteristics are available in the menu displayed with the right mouse button.

You can move column and list headers to a mode that is ready for input by double -clicking.

Selecting a field in the upper left window automatically adds that field to the list (is appended at the end of the current line). The individual fields are represented by field values. Sample data records are read from the source. If this is not possible, field values are simulated. The structure of the layout determines the structure of the subsequent list - that is, it contains the order of the fields, the headers, the colors, totals lines, and so on. To display the list structure for multi line hierarchy lists, several sample records are read and displayed.


In addition, tools are available in the Query Painter to design the list. You can change the arrangement of the tools with drag and drop. Select the tool, such as the trash (a frame is displayed), with the left mouse button. You can now drag the selected area to the new position as long as you keep pressing the left mouse button.

You can also use drag and drop to edit the list. Example: You want to change the field sequence. To do this, point the mouse at the field you want to move, click and hold the left mouse button (the cursor changes), drag the field to the desired location, and release the mouse button. To delete a field, just drag it to the trash.

You can also change the output position and output length with entries in the lower left window.

Press Apply to apply your values to the list structure.

You can set up control level lists. To do this, you have to determine the sort fields. The sort sequence can be defined in either ascending or descending order separately for each field. To create a sort field, drag a field from the list to the Sort tool.

You can define control levels with or without a total at the end of the control level (subtotal). You can change the text accompanying the subtotals.

If you total a field, the total is output to the same column as the field, with the same output length. Accordingly, the output length may be too short and result in an overflow (an asterisk appears in the first position of the value). To prevent overflows of totals, you can simply increase the output length of the field you wish to total.

You can output blank lines and/or force a page break before outputting control levels.


You can hide and change introductory and concluding texts for control levels.

The system automatically creates a currency distribution for currency totals.

List overview:


If your list consists of several partial lists, for example a basic list, two statistical lists and a ranked list, the system offers you the ability to display the partial lists individually. The partial lists can also be printed separately.

Report/report interface (RRI): You can use this interface to call query programs (receiver) and other reports (sender). Additional information is available in the online documentation.

Table display: The list is displayed as a table control or using the ALV grid control. Starting in Release 4.6A, you can also display multi line lists. The different lines are summarized in one line. Graphics: The information contained in a list can be displayed with SAP Presentation Graphics.

File storage, private storage: Saves the data as a file on the presentation server or in the private folders.

Word processing and spreadsheets: Transfer data to MS Word or Excel (for example)

Selection: Indicates which selections were input in the selection screen.

Drilldown functions: For expanding and collapsing the list.

Totaling: Totals for numeric fields.

You can save a list generated by a query using the menu path AQL-5 and re-display it later.

Subsequent display of a saved list does not require database access to retrieve data. Such a display is therefore much quicker than restructuring the data running the query again.

Saving a list stores the list itself and supplemental information. Storage of additional information is a special function of saving lists that is supported only by query. This makes it possible to perform interactive functions in the saved list.

When a query is integrated in an area menu (not the AQ... query program), then all the saved lists are automatically passed on to the area menu, and can be displayed there. All interactive functions remain available.

If you save the list 'normally' (using menu path AQL-6), then no interactive functions are available in the saved list.