Field Help for Query Definition: Result Fields and Query Results 
Field Help for Query Results Fields Page
This section provides help for the fields on the Query Results Fields page.
Field |
Description |
Total Table Width |
Indicates the width in pixels of all currently defined columns. This field can be used to determine whether column widths should be adjusted to fit a limited horizontal area, such as a Workbench channel. In a thin Workbench channel, the column widths should total no more than 230 pixels. In a wide Workbench channel, the total should be no more than 490 pixels. |
Result Columns |
Choose Add to add rows to the query results. Each row represents a column to be retrieved through the SELECT clause of the SQL statement and potentially displayed to the user. |
Field Help for the Query Results Dialog Box
The following sections provide field help for the Query Results dialog box.
Database Column Name
This field defines the entry that will be added to the SQL SELECT clause. That is, it names the column to be retrieved from the database. The value can be a column name or can leverage another SQL function, as in the following examples:
Database Column Name |
Usage |
|---|---|
T1.DISPLAY_NAME |
Simple column name |
607 AS CLASSID |
Static value for all rows |
UPPER(T1.DISPLAY_NAME) |
Column name with formatting |
T1.LAST_NAME <%+%> COALESCE(T1.FIRST_NAME,'') |
Concatenated values, with the FIRST_NAME column value not required |
COALESCE(T1.TOTAL_EXPECTED_PRICE, 0) |
Define a value to use for null data |
SUM(COALESCE(T1.TOTAL_EXPECTED_PRICE, 0)) |
SQL sum function |
CASE WHEN T1.WOMEN_BIZ=1 THEN 'cterms$cterm.yes' ELSE NULL END |
Return value depending on condition; Use CASE rather than DECODE. |
This Field is Not Displayed to Users
Select for the query to retrieve the column but not display it to users. A common use of this feature is to sort the query results on a value that is not displayed. For example, a displayed column might show the Supplier name in mixed case, while a second hidden column for the database column name UPPER(DISPLAY_NAME) could be included as basis for sorting to ensure that sorting ignored case.
Column ID
There are times when a column definition needs to be identified by a unique name rather than just by position. The Column ID field serves this purpose. It should not be confused with the Database Column Name, which is the SQL expression that will be included in the SELECT statement.
The form of a column ID should be TableAlias.COLUMN_NAME. (An identifier in this format is called a “table column name”.) This is important for the proper behavior of the dynamic selection of fields for display and filtering.
Examples include the following:
T1.OBJECTID
T1.MODIFIED_AT
T1.WORKBOOK_TEMPLATE_OBJECT_NAME
The Column ID is the same as the Database Column Name field when the database column name is a simple column name. For all other column definitions that do not correspond to a database column name, such as an expression that includes an SQL function or a concatenation operator, use a unique string as the Column ID, in UpperCamelCase format. For example, “UpperDisplayName” is an appropriate Column ID value for UPPER(T1.DISPLAY_NAME).
The Column ID value must be unique. For any column definitions that use the same database column twice (such as T1.DISPLAY_NAME and UPPER(T1.DISPLAY_NAME), the table column name of T1.DISPLAY_NAME can be used only once. The second use should use the UpperCamelCase convention, such as “UpperDisplayName”.
The upgrade to the release that introduced the Column ID initialized it to a copy of the UNIQUE_DOC_NAME value, which is just a string of digits. Whenever a query is edited and these cryptic Column IDs are seen, they should be replaced with meaningful values (a table column name or a string in UpperCamelCase format). Any query definition that supports the adding of dynamic fields must have those Column IDs replaced.
Column Header Resource
Select the column heading from the drop-down list. This is the system resource to be used to provide a locale-specific column heading in the display. This field is required for all columns, including hidden ones.
Hide Column Header
Select this checkbox if the column header should not be displayed to the user. It is recommended for columns that contain some action icons, like Delete or Edit. Note: A meaningful column header title should be provided in any case. It will be used for any alternative, non-visual renderer of the query results.
Column Type
Select the column type from the drop-down list. The column type may be simple or complex. Complex types require that multiple columns be created in a specific order.
The Column Type controls the presentation of the data. The following table describes the simple column types:
Column Type |
Usage |
|---|---|
Number |
Right-justified numeric value |
Date
If the Date, Time or Date/Time type is selected, then the returned column must be a full database date/time stamp. If the column, for example, is only a date value, then the string type must be used. End of the note. |
Right-justified year/month/day value (for example,12/31/2008) The database expression must be a TIMESTAMP. A Date column name typically has a "_DATE" suffix. Any time component (hours:minutes:seconds) in the value is ignored. No time zone adjustment is made. |
Date Time |
Right-justified Date/Time value (for example,12/31/2008 14:38:00) The database expression must be a TIMESTAMP. A DateTime column name typically has a "_DATETIME" suffix. An adjustment is made for the user's time zone. |
Time |
Right-justified hours/minutes/seconds value (for example,14:38:00) The database expression must be a TIMESTAMP. A DateTime column name typically has a "_TIME" suffix. Any date component (year/month/day) is ignored. No time zone adjustment is made. |
Boolean |
0 Displays 'false', 1 'true' |
String |
Left-justified text value |
String Resource |
Locale-specific value for this system resource, left justified |
Enumeration |
Translates numeric value to left-justified text value
If the Enumeration type is selected, then the name of the enumeration must also be supplied. End of the note. |
NoteIn addition to the simple column types, there are complex types used for displaying monetary amounts, quantities with units, object names, hypertext-linked data, and so on. Each of these types requires multiple columns, and the column types for each complex type must be included in the order described below.
Monetary Amounts: Use the Monetary Amount type for the amount column, followed immediately by the Monetary Currency type for the text name of the currency. The two values will be concatenated and displayed in a single right-justified column, for example, 123.45 USD.
Quantities With Units: Use the Quantity Amount type for the amount column, followed immediately by Quantity Unit Object ID for the Units Object ID column, and then immediately by the Quantity Unit Display Name for the name of the units (for example, Kg). The Quantity Amount and Quantity Unit Display Name will be concatenated and displayed in a single right-justified column, for example, 100 kg.
Object Names: This complex type is used to display the names of other system objects as hypertext links. To show an object name without a link, use the Display Name column as a simple text field. To show the object as a link, use the Object ID column, immediately followed by the Class ID column, and then the Display Name column. The Display Name will be displayed as a link, and clicking on the link will take the authorized user to the object. See any of the business object list queries for examples.
Alternatively, instead of the Display Name column type, the third column in the object name can be specified as an Icon Display Name. In this case, the column displays an icon that links to the object in the same way as the basic text display name link. This is a useful way to minimize the width of the object name column. The column should still reference the display name value, as this value is displayed as fly-over text on the icon. The system displays an object-specific icon when such icons are available, such as for Supplier and the Business Document classes, and uses a generic icon for other classes. The column width for an icon object reference should be 18 pixels, allowing for the 16 x 16 icon with border space. Use a relatively short display name for the column to keep the small column width.
Another alternative to the Display Name column type is the Display Name Resource. This is typically used for objects that have documents installed with the system, such as value list values and queries themselves. Such documents need display names that are appropriate for users in different locales.
Links: In addition to object names displayed as hypertext links, any returned data value can be displayed as a link. For example, to create drill-down queries for spend data, the spend category value can be displayed with a hypertext link to a more detailed query, including the category value as an input parameter to the second query. Use the URL column type for the displayed value, followed immediately by either the Internal Page or External Page type to provide the link value. Internal Page should be used for linking to other SAP® E-Sourcing pages and External Page for linking to other web sites or applications. Links can also be displayed as buttons, using the Link Image Source attribute on the result field defined as the URL column. Supply the URL to a GIF image file of the button in the specification for this column, and the link will be displayed as a button. See the Upcoming Auction Event Detail query for an example. For more information, see Query definition: generating a query date parameter in SQL.
Dimension Reference: A dimension reference is displayed only in spend analysis dimension picker queries. A selected dimension reference serves as a parameter value to some spend analysis reports. There are two display values, a unique dimension ID and a corresponding label (essentially a short description). One of these picker queries is All First Level Organizations. The Dimension Type column is a literal integer from the DimensionEnum, such as 4 for the Organization dimension. The Dimension Level represents the level of the hierarchy the reference is in, typically provided by a dimension level token. Dimension Value ID is the unique dimension ID, with Dimension Value Label serving as a short description. The Dimension URL Fragment column composes a link with the displayed label text to a drill-down query group which finds the children of the dimension ID. Dimension Parent ID is the unique dimension ID at the next higher level. For first-level IDs, the parent ID is null.
Repeating Column
Select this checkbox to indicate repeating columns for the pivot result set. Columns with this indicator in a pivot query will be expanded to multiple columns, each with one of the chart series label strings selected by the previous query in the report.
If this box is checked, the CHART_SERIES_LABEL token should not be used to indicate a repeating column. This token was used in an earlier implementation of the generation of sets of repeating columns, and is still supported.
Display Width (characters) and Display Width (pixels)
Enter the width of the table column using either characters or pixels.
The display width (typically provided in characters) is a hint to the UI of how much horizontal space to provide to a column. See other columns of the same type to get an idea of an appropriate width value. It has more influence on PDF than HTML, so test the export of the result set to PDF. The more columns there are in the result set, the smaller the width values should be.
Resolve Token(s) in Value
Checking this box indicates that the value of the string field in the query results might contain one or more tokens that must be resolved. The ARGn tokens are the primary use case. This is different from the normal token resolution, because tokens are normally resolved before the query is executed.
Convert Scale to
A selected number value will be rounded to the number of decimal digits which is entered in this field. Zero is often used to drop digits to the right of the decimal place when many values are being summed. (By default, any totals of the number values are done before rounding. Setting the system property, system.ResultSet.setScaleOnAddend, to TRUE reverses this behavior.)
Show Image
Some column types support the display of an image instead of or in addition to the display of the normal text value of the column. If this support is available for a column type, a drop-down list of image display options are provided. The options are:
Option |
Behavior |
|---|---|
None |
Display the usual text string - no image is shown. |
Image Only |
Display the image, with the text visible in a fly-over. |
Image with Text |
Display both the image and the text. |
Displaying both the image and the text is recommended when the only distinguishing characteristic among images on different rows is their color. Some shape difference or text difference is needed to provide sufficient meaning to each image.
Selecting an option to show an image for a String column will cause the string value to be interpreted as the path to an image file, leading to the image being displayed. The image file is sought in the class path first. If it is not found in a JAR in the class path, it is sought as an instance of a file attachment container. If the image cannot be found, only the string is displayed.
Selecting an option to show an image for an Enumeration or String Resource column indicates that an associated locale-specific image should be displayed. If an image is not available, only the string is displayed. The image to be displayed is found indirectly, through the resource ID which is associated with an enum value or which is returned as the value of the string resource. Internally, a suffix of ".image" is appended to the resource ID, and the resulting resource ID is sought. If it is found, its value is expected to be the path to an image file, such as "/signal/sphere_red.gif".
Selecting an option to show an image for a URL column will cause the string value to be interpreted as a resource ID with an associated locale-specific image which is to be displayed. This is the same behavior as for a String Resource column. The old behavior of providing a source image path in the column definition is still supported, which is to display the same image for each row. If both the image path is set and an option to show an image is selected, an image retrieved via a resource ID supersedes the static image.
If it is inconvenient to store an image in a JAR in the class path, it can be imported as an attachment in a file attachment container. The path name of the file attachment container is the path to the image file. The image should have a GIF or JPEG format so that it can be exported to PDF.
Conversion Unit (for Quantities with Units)
This field appears when you select Quantity Unit Display Name in the Column Type field. Select the unit of measure to which to convert the selected values.
This is a picker for a unit of measure to which the selected values will be converted, if a legitimate conversion is possible, from the selected unit to the conversion unit. The original quantities are displayed in one column, as usual, along with an additional column containing the converted quantity. If no conversion was possible, the additional column's value for that row will be empty.
Currency Conversion Source
This field appears when you select Monetary Currency in the Column Type field. Select the target currency from the following options:
None: No conversion is done.
User: The current user's currency is the target of the conversion.
Organization: The current user's organization's currency is the target of the conversion.
Selection: The target currency is selected from a drop-down list.
Conversion Unit (for Monetary Amounts)
This field appears when you select Selection in the Currency Conversion Source field. Select the target currency.
Total this field at bottom of table
Check to provide a total below the individual detailed result rows. This field can be used with Number, Monetary Amount or Quantity columns. A total can be provided for an amount or quantity column only if all values have the same unit, such as dollars. An additional row will be generated at the bottom of the results list with any calculated totals.
Default Display
This flag indicates whether this column will be displayed to the end user by default. The default flag is TRUE. If a column should not be displayed by default, deselect the checkbox. Only enough fields to fill the visible part of the UI page should be displayed by default.
Sorting
If the <%ORDERBY%> token has been used in the Query String, use the Sorting fields to define the SQL ORDER BY clause that will be generated for the query. If you want to sort, for example, by Last Name and then First Name, enter 1 in the Sort Order field for Last Name and 2 in the Sort Order field for First Name. Check Ascending if an ascending sort is desired.
BO Object Reference Column
To support application specific actions, the first result column of the Object Name complex type has to be for the corresponding BO class.