SAP Query / The QuickViewer
Scope of Functions
In Release 4.6A, ABAP Query has been renamed SAP Query. All of the functions available to you in past releases remain. The new name does not bring any restriction of functionality with it. New functions include the graphical Query Painter , an additional maintenance component - the QuickViewer - and the introduction of graphical table join definition. In addition, several functions have been enhanced. These improvements are listed below.
- The Convert QuickView Function
- The Frame Width Option in Basic Lists
- Page Breaks for Statistics
- Interactive Multiple-Line Basic Lists
- Dynamic Currency Conversion Settings (Exchange Rate Types and Reference Currencies)
- The Test Function
- Restricting the Number of Database Accesses
- Restricting the Number of Ranked List Rankings Displayed
- Printing Sublists
The Query Painter
The graphical Query Painter provides you with an additional way to
define queries. You can continue to use the old query definition procedure if you so desire.
The new Query Painter allows you to define lists in WYSIWYG mode (what you see is what you get). In Release 4.6A, this new tool may only be used to create basic lists. Statistics and ranked lists must be defined according to the old procedure.
The graphical Query Painter uses several different controls. Thus certain technical preconditions (both hardware and software requirements) exist for its use. If your system does not meet these requirements, you may continue to use the classical query definition procedure. Use the function Settings -> Settings... to determine which query definition procedure you want to use. If your system fulfills the graphical Query Painter's technical requirements, then it is already selected as your standard setting.
The graphical Query Painter is called using the Basic list function in either transaction SQ00 or SQ01. User groups and functional areas need not be selected beforehand. However, in Release 4.6A, you must still define local fields on the Field selection screen as has been the case up until now.
The Query Painter is a powerful tool with numerous functions. There is only enough room in this release note to describe several of its more important functions. For more in-depth information, please refer to the corresponding documentation in the system.
The Query Painter consists of a screen divided into four windows that can be resized using your mouse. These fields contain:
- the fields available for use, organized in a tree (upper left)
- the list's layout (upper right)
- information about the list element currently selected (lower left)
- links to documentation, as well as any warnings or error messages that occur (lower right)
The window on the upper left-hand side of your screen displays a list of the fields currently at your disposal. These fields are displayed in a tree whose structure mirrors the structure of your data source. If your data source is a logical database, this tree's nodes are the nodes found in the logical database. If your data source is a table join, the tree's nodes represent the individual tables found in the join and their associated fields are displayed under them. This tree bears a strong resemblance to the tree displayed in functional area maintenance, however here only those fields that are part of the functional area are listed.
The fields' functional group assignments are no longer of importance in this view, and thus only represent a means of selecting fields during functional area maintenance.
Use the standard tree functions Expand and Collapse to select the fields you want to work with. Each field has two radio buttons assigned to it. Marking the first radio button selects the field and transfers it to the layout window (and thus automatically to the list). You can also select a field by simply double-clicking on the field name. The second radio button allows you to use the field as an additional selection criterion.
The window on the upper right-hand side of your screen displays the list's layout. Individual fields are represented by field values read from your data source to serve as sample data records. If the system is unable to read sample data from your data source simulated field values are inserted. This layout display represents the actual organizational structure of the list to be created complete with headers, colors, totals lines, and the same field order. With multiple-line hierarchical lists, several sample data records are read from the data source so that these lists' structure can be represented accurately.
The layout window also contains numerous toolbars that assist you to alter your list's layout and settings. These toolbars consist of a frame containing a header and in some cases additional elements (icons, for example).
You can drag and drop the objects found in the layout window to new positions. Elements for which this functionality is available can be identified by the pointer finger that appears when the cursor is positioned on them. A single click with the left mouse button frames the object in blue and displays the Move cursor (the rifle sight). Now you can reposition the element by depressing your left mouse button and dragging it to where a new point in the layout window. The element assumes its new position once you release mouse button (as long as the new position is valid). Drag and dropable elements include:
- all fields in the list layout
- toolbars as a whole
- fields that appear in toolbars (see below)
- icons within a toolbar
A single click on a header line, a column header, a footer, a control level, or on a sub-totals text makes the corresponding area ready for input in the list layout. Any texts you want to add can now be inserted directly.
You can also change some objects' widths (windows on the list layout screen, toolbars) by positioning your cursor on the right edge of the object and expanding or contracting it to the width you want while holding down the left mouse key.
Once the element has been selected (see above) and the blue frame appears, information about that object appears in the window on the lower left-hand side of your screen. With fields, their long text, technical name, and attributes within the list (output line, output position, display length, sort number, etc.) are displayed. You can make changes to these settings/information directly in the window. Choose Apply after making a change to ensure that your entries are saved.
If an element contained in your layout window is selected, you may also call a context menu for it by depressing the your right mouse button. This context menu allows you, among other things, to display a list's general settings or the settings of individual list lines that cannot be represented graphically. These settings can also be changed directly by making the appropriate new entry and then choosing Apply.
Multiple elements may not be selected at the same time.
The following is a list of several important steps in list definition.
Placing fields in the list
You can place a field in your list by double-clicking on it in the window on the upper left-hand part of your screen. The field is then added to the end of those fields already found in the list. If there is not enough space at end of the last list line, the field is placed in a new line. If you do not want an extra line added to your list, you may first increase the width of your list and then place the field where you want it manually.
Changing list width
Use the ruler found directly above the list on the layout screen to change its width. Select the list and then drag its right edge to the width you want after the blue frame appears.
Moving fields within the list
In order to be able to move a field within a list, it must first be selected. Then you can use your left mouse button to drag the field to the new position you want it at. The Query Painter has both an insert mode (standard) and an overwrite mode for dragging and dropping. In insert mode, the system clears a space for any field's that you move, that is, it inserts a new field and all other existing fields are moved to accommodate it. In overwrite mode, if this new field is moved to a position already occupied by another field it overlaps the pre-existing fields and a warning is displayed in the window on lower right. To guarantee that all overlapping windows are displayed on your list, use the function Organize fields left-justified found in the field's context menu (see above).
Changing the display length of a field
To change the display length of a field, first select the field and then drag its right edge to the width you want after the blue frame appears.
Deleting fields from the list
To delete a field from the list, first select the field and then drag it to the Trash can toolbar. The toolbar contains a trash can icon. Drag the left corner of your field to the trash can icon and drop it there. You may also select the trash can in the toolbar and then drag it to the field you want to delete.
Whenever a field is deleted using the Trash can, it is inserted in the trash can toobar where you may select it at a later date and drag it back onto the list.
You can use the trash can to delete other elements contained on the list layout screen or in other toolbars.
To sort according to a specific field, select the field and drag it to the Sort fields toolbar. If the toolbar contains more than one field, the sequence of the fields in the toolbar determines the sort sequence. The system sorts according to the initial field in the toolbar and then proceeds to the next one, and so forth. An icon is displayed after each field that indicates its sort direction (ascending or descending). Simply clicking on this icon reverses the sort direction.
You may select and move fields within the sort fields toobar if you want to change the sort sequence. To undo a sort, select the field in question in the toolbar and drag it to the trash can or drag the trash can to it.
If you sort a field in the manner described above, the system provides for a control level text and the use of sub-totals (see below). This should be apparent from the altered list layout. If you do not want to use a beginning of control level text, select the trash can and drag it to the line in the list containing the text.
If you want to total a numeric field, select the field and drag it to the Totaling fields toolbar. A sum total for the entire list and sub-totals for those sort levels that already exist are generated. The layout of the list is changed accordingly. To undo totaling for a specific field, select it in the toolbar and drag it to the trash or select the trash can and drag it to the field, either in the toolbar or on the list layout screen.
If you want to delete the sub-totals for a specific sort level, select the trash can and drag it to the line containing these sub-totals in the list layout. You must drop the trash can on the text found in the sub-totals line.
If you want specific fields to be counted, the procedure is basically same as described above, using the Counting fields toolbar instead. This toolbar is not normally visible in the layout window and must be switched on manually using the function Tools -> Counting fields on/off.
The lower left-hand window on the Query Painter screen usually contains terms found in the SAP Query documentation. These terms can be used as links to call the corresponding online documentation.
The QuickViewer is a new product that is actually a variant of the standard query maintenance transaction. It contains the following new features:
- The reports created using the QuickViewer are called QuickViews. They have the same functional properties as queries, but only basic lists may be defined.
- User groups are no longer necessary in the QuickViewer. All users have their own personal supply of QuickViews that cannot be passed between different users. You may, however, convert QuickViews to queries and then assign them to a user group.
- Functional areas are no longer a requirement when defining QuickViews. When defining a QuickView you must initially specify a data source. This data source may be a table, a database view, a table join, a logical database, or a functional area that already exists.
- The QuickViewer has two different modes for creating QuickViews Basis mode and Layout mode. In basis mode, only those fields you want to use in the report are selected (including sort fields and selection fields) and your report's graphical layout is determined by the tool you choose to display it in (Excel, ALV, as a list, etc.). In layout mode, the Query Painter described above is used to layout your list.
- The QuickViewer uses several different controls and can only be used if your system meets the appropriate hardware and software requirements.
Use transaction code SQVI to call the QuickViewer. You can also use the menu option System -> Services -> QuickViewer.
The initial screen of the QuickViewer is similar to the initial query maintenance screen. You will find an additional window at the left of your screen containing help texts. You may change the size of this window using your mouse. You can call existing QuickViews for processing by selecting a QuickView directly from the table or by entering its name in the appropriate input field. Then choose the function Change.
To create a new QuickView you enter a new name in the input field and choose Create . A dialog box appears where you must make the following entries:
- Title (long text) and remarks, if you so desire
- Type of data source (table/database view, table join, logical database, functional area)
- Name of the data source
At this point different kinds of entries may be expected from the user depending on the type of data source selected. Use the possible entries pushbutton for a list of possible data sources.
- Mode (basis mode or layout mode)
If you have chosen a table join as your data source, the join must be defined before you can begin with QuickView definition. During join definition, a control is made available to users allowing them to visualize the relationships between the individual tables. The join definition procedure will be described in depth below.
If you have chosen a functional area as your data source, the functional area must come from the standard area. Functional areas from the global query area may not be used in QuickView construction.
If you have chosen basis mode for defining you QuickView, a screen is
displayed containing a tab strip with three tabs where you can make all
of the necessary entries pertaining to the fields you want to display.
Here you determine which fields are to be displayed, which fields you
want to use as sort fields, and which fields should be used as selection fields. Each tab contains a table on the left with those
fields selected and a table on the right containing all fields available for use. The table on the left may contain additional
infomation about the fields selected depending on how the are to be used (display, sorting, selection).
To choose a field, select it in the table on the right and use the pushbutton Arrow pointing left to move it to the other table. To deselect a selected field, select it in the table on the left and use the pushbutton Arrow pointing right to move it back to the table on the right. You can select and move more than one field at a time. In addition, pushbuttons exist that allow you to move all of one table's fields to the other.
You can select a field in the selected field list on the left and use the pushbuttons to the left of this table to move the field up or down in the field sequence. This allows you to determine the display or sort sequence of these fields depending on which subscreen (tab) you are currently on. The sort subscreen also has pushbuttons allowing you to alter the sort direction (ascending or descending).
In addition, basis mode contains a selection field with dropdown box that allows you to choose which tool you want to display your list in (Excel, ABAP List Viewer, as a list, etc.).
If you want to design your list's layout yourself and choose layout mode instead, the graphical Query Painter described above appears instead of these tab strips.
Graphical Table Join Definition
From Release 4.6A, a new control is used during table join definition.
This control displays graphical representations of the tables you are
joining and the relationships between them. Table join definitions must
be in place before you maintain functional areas (transaction SQ02) or QuickViews (transaction SQVI).
This new control is always used in combination with QuickView maintenance; during functional area maintenance, you can still use the old join procedure. Use the menu option Settings -> Settings... to switch between the different definition procedures. If the corresponding technical requirements are fulfilled, graphical join definition is the standard setting.
Each table appears in its own window on the graphical table join
definition screen. These windows contain all of the fields in the table
and can be repositioned at will. A long text exists for each field. Key fields are indicated by a key icon.
To add a table to a join, use the Insert table function. A dialog box appears where you can enter the name of the new table. The table is subsequently displayed on the screen.
To delete a table from a join, close the table's dialog box (window) or use the Delete table function. When using the latter, a dialog box appears where you can enter the name of the table you want to delete.
The ON conditions joining the individual tables within a join are
represented by lines in the control. To create such a line, click on a
source field, depress your left mouse button and drag the cursor that
appears to a target field. If the connection is valid, a line appears between the two fields once you release your mouse button.
Place your cursor on the line and depress your right mouse button to display a context menu where you can specify what type of connection should exist between these two fields. Here you can both display and delete join conditions. If you choose to display join conditions, a dialog box appears where you can specify whether this join should be an inner join or a left outer join.
The function Suggest linking conditions allows users to define the same ON conditions between tables that are suggested in these tables' ABAP Dictionary definitions.
All of the existing rules and restrictions pertaining to join definition continue to be valid.
Query Maintenance Enhancements
The Convert QuickView Function
The new function Query -> Convert QuickView is available on the initial screen of the query maintenance transaction SQ01. This function allows you to convert a QuickView from any user into a query. QuickViews may, however, only be converted into standard area queries assigned to a specific user group.
When you call the Query -> Convert QuickView function, a dialog box apppears where you can specify the name and user of the QuickView you want to convert. Choose Continue . An input field appears on your screen where you can enter a name for the new query. The query's user group has already been set within the system.
If the QuickView was not created using a functional area, a new
functional area must be created from within the QuickView. You can enter the name of the functional area in another dialog box that
If the QuickView was created with a functional area, the input field does not appear, but the system checks to see if the functional area has been assigned to the user group currently set. If this is not the case, the query cannot be created.
Choose Continue to proceed with the conversion. After conversion you can edit the query and, if necessary, the functional area using the normal SAP Query tools at your disposal.
The Frame Width Option in Basic Lists
If you decided to frame a basic list, up until now the width of this
frame was automatically set by the system. From Release 4.6A you can enter your own frame width values.
In the Query Painter, frame width can be adjusted by increasing or decreasing the size of the ruler found above the list on the list layout screen. If you are not using the Query Painter, the frame width can be adjusted by making an entry in the appropriate field on the Basic list line structure screen. If nothing is entered in this field, the frame width is automatically adjusted by the system. If the width that you enter is wider that the width of the list, the system automatically corrects the value.
Page Breaks for Statistics
You can choose the option New page with sort fields when defining statistics. This option inserts a page break every time a new sort term (sort string) occurs. The same restrictions apply to the New page option that apply to the option Sub-totals. Please consult the online documentation for more information on both options.
Query Enhancements at Runtime
Interactive Multiple-Line Basic Lists
Up until now, all of the interactive functions in SAP Query (Excel, table display, interactive lists, graphics, etc.) could only be used with single-line basic lists, statistics, and ranked lists. From Release 4.6A, almost all multiple- line basic lists are interactive as well, that is they can be treated like single-line basic lists and use the functions described above. You may also call interactive functions directly without displaying the list.
In order to make this kind of interaction possible, the data displayed in the list must be inserted in a flat internal table. The line structure of this table is such that all fields of all lines in the basic list are inserted in this line one after the other. Each time a list line is output, the fields from the list line are inserted in their corresponding fields in the table line. A new line is attached to the internal table whenever all fields of the table line are filled or when you can see that the fields still missing for a table line no longer appear in the list.
For example, if a basic list with two lines (line 1 and line 2) has been defined, and line 1 can have an infinite number of line 2s, the following scenarios are possible:
- Exactly one line 2 belongs to each line 1 in the list.
In this case, exactly one line is inserted in the internal table and it contains the values of both lines.
- Multiple line 2s belong to each line 1 in the list.
In this case, multiple lines are inserted in the internal table where the fields found in line 1 are repeated and the fields found in line 2 are set anew for each table line.
- No 2s belong to the line 1s in the list.
In this case, exactly one line is inserted in the internal table and an initial value is inserted for the fields from line 2.
If the basic list contains more than two lines, the procedure described above is applied in multiple layers.
In general, all multiple-line basic lists that do not receive their data from a logical database are interactive. If the list retrieves data using a logical database, all fields used in the list have to have a single path in the database. The multiple-line basic lists that are not interactive are those that process fields from parallel tables.
Dynamic Currency Conversion Settings (Exchange Rate Types and Reference Currencies)
Up until now, when a statistic or ranked list containing a currency field existed in a query, an input parameter was inserted on the query's selection screen where you could enter the date on which currency conversion was to take place. From Release 4.6A two additional currency conversion parameters are available.
- Exchange Rate Type
In the past, the system used an average exchange rate (M) internally. Now you can enter an exchange rate type directly on the selection screen. All conversions are made using this exchange rate. This means that if numerous columns containing currency sums exist, they will all be converted with this exchange rate.
- Reference Currency
In the past, the system used reference currencies that were predefined for each column during query definition. Now you can enter reference currencies on the selection screen. All conversions are made using this currency. This means that if numerous columns containing currency sums exist, they will all be converted using this reference currency. If no reference currency is entered on the selection screen, the reference currency settings made during query definition remain valid, i.e. the system uses the same procedure as was used in the past.
The Test Function
You can now use the function Test to test query execution. This function allows you to limit the number of database accesses, making it useful for test purposes.
When you start the Test function, a dialog box appears where you can set the maximum number of data records you want to be read from the database. Each data record read counts as a database access. If multple tables are used during data selection by a logical database, all records of all tables involved in the selection process are counted. Once the maximum number of database accesses has been reached, data selection is terminated. You may also enter the name of a variant in this dialog box if you want to execute using a variant.
Restricting the Number of Database Accesses
From Release 4.6A, the number of database accesses for a query will be
automatically restricted by the system if no entry is made in the
appropriate field on the selection screen. This prevents queries from
being started accidentally and reading the entire dataset present in their functional areas.
The system checks all selection criteria and parameters on the selection screen to determine whether or not it must limit the number of database accesses itself. Exempt from the checks are:
- All standard parameters inserted on the selection screen by the query itself, such as currency conversion date or direct interaction specifications,
- All 'invisible' parameters, and
- All parameters that appear as a radio button or checkbox on the selection screen.
Whenever an entry has been made for at least one of the remaining selection criteria or parameters, the query is started without further ado. Otherwise, a dialog box is displayed asking you to set the maximum number of database accesses to be undertaken in the query, much the same as the one in the Test function.
If you are executing a query with variant or in the background, you cannot restrict the number of database accesses.
Restricting the Number of Ranked List Rankings Displayed
You can restrict the number of ranked entries you want to display in a ranked list during ranked list definition. Only the number of entries you specify will then be displayed in your ranked list. In the past, this process had the disadvantage that if several entries occured with the same ranked list criterium value, the sequence of these entries was random. Some entries were not displayed at all if the maximum number of rankings to be displayed was reached before they could all be output.
From Release 4.6A, the system assigns entries with the same ranked list
criterium value the same number in the ranked list (the initial
column). This number is, however, only displayed with the first entry that has that criterium value.
It can come to pass that more rankings are displayed than are allowed in the ranked list's definition. This happens when entries having the same ranked list criterium value occur at the end of the list. In this case, all entires with the same criterium value are always displayed.
If a query contains multiple sublists and you want to print from the online display, from Release 4.6A the system allows you to choose which sublists you want to print. A dialog box appears containing checkboxes for sublist selection.