Details for data source column types

This topic provides details for the data source column types.

Overview of data source column type

Adding columns to the Query Definition window relies on the Data Source Column window. The fields of the Data Source Column window vary, depending on the column type selected.

See the associated details for the following column types:

Text, Date, or Number, see Text–based column types details.
Hyperlink, see Hyperlink column type details.
Currency, see Currency column type details.
Summary Condition/Exception, see Summary Condition/Exception column type details.

Text–based column types details

Table 2-2. Text–based column types field descriptions
Field Name (*Required)	Description
*Column Name	Specify the name of the column. Type any alphanumeric string.
*Column Type	Select one of the following: Text Date Number Summary Condition/Exception
Description	Specify a brief description of the column. Type any alphanumeric string.
*Column	Specify the database column name used for the column. Choices are limited to the column names available on your PPM instance. Note: The type of the database column specified should match the Column Type selected. If the types do not match, sorting of this column in the portlet may not behave as expected (sorting is based on the type of the database column). For example, if you select a Column Type of Number, but the type of the database column is text, sorting of this column in the portlet is based on text rather than numbers. Use a function, such as `to_number,` to change the database column type to match the selected Column Type.

Hyperlink column type details

Figure 2-8. Hyperlink column type in the Data Source Column window

Table 2-3. Hyperlink column type field description
Field Name (*Required)	Description
*Column Name	Specify the name of the column. Type any alphanumeric string.
*Column Type	Select Hyperlink.
Description	Specify a brief description of the column. Type any alphanumeric string.
*Hyperlink Type	Select the type of hyperlink to create from this column. Choices include: Request Detail Page Package Detail Page Task Detail Page Project Detail Page Project Overview Page Custom Hyperlink When Custom Hyperlink is selected, the Hyperlink field becomes editable and required. Otherwise, the Hyperlink field is read-only and displays a preview of the link that is created (for example, `/itg/crt/RequestDetail.jsp?REQUEST_ID= [DSH.PRT.ENTITY_ID]`).
*Entity ID Column	Specify the SQL expression used to make a column in the final query. This column should contain values which are Request IDs (such as, Request Detail Page type Hyperlinks). In the case of the Custom Hyperlink, the values from this column are used to resolve the token `PRT.ENTITY_ID`, which can be used in the text of the hyperlink.
*Hyperlink	Specify the actual hyperlink text, which should be of one of the following forms: `<`PPM_Home`>/some/page` `http://machine.server.com/page.html` The valid tokens are `DSH.PRT.ENTITY_ID`, `DSH.PRT.ENTITY_ID.TO_URL`, and `SYS.USERNAME`.

Currency column type details

Figure 2-9. Currency column type in the Data Source Column window

Table 2-4. Currency column type field descriptions
Field Name (*Required)	Description
*Column Name	Specify the name of the column. Type any alphanumeric string.
*Column Type	Select Currency.
Description	Specify a brief description of the column. Type any alphanumeric string.
*Base Value Column	Specify the column (that displays in the currency column) whose values are costs in the base currency. These values are displayed if the user has configured their PPM Dashboard to show costs in the system's base currency. For this column to sort correctly, it must be a number. If necessary, use the function `to_number` to change the value to a number.
Currency ID Column	Specify the column whose values are the ID of the local currency. This value is never displayed; however, it is used to determine how the local currency value (see Local Value Column)is formatted.
Local Value Column	Specify the column (that displays in the currency column) whose values are costs in the local currency. These values are displayed (and formatted appropriately for the specified currency) if the user has configured their PPM Dashboard to show costs in the local currency. That is, the user must select Local Currency from the Dashboard Cost Display section from the Edit My Profile page (Open > Administration > Edit My Profile). For this column to sort correctly, it must be a number. If necessary, use the function `to_number` to change the value to a number.

Summary Condition/Exception column type details

A field of the Summary Condition/Exception column type lets you have the SQL return a set of predefined values. Each of the values represents a path of an image. When this column type is used in a list portlet, or in the Self-Service portlet Grid view, the corresponding images are rendered. However, when exporting such portlet data to Excel, the values, or the paths of the images are exported.

For descriptions of the Summary Condition/Exception column type fields, see Table 2-2. Text–based column types field descriptions.

The following table lists the values that are supported for the Summary Condition/Exception column type, and the images that each value corresponds with.

Any other value not listed below is ignored. Instead, an empty value is returned.

Value returned in SQL	Path of the image	Image when rendered
RED	`/itg/images/pm/Red.gif`
YELLOW	`/itg/images/pm/Yellow.gif`
GREEN	`/itg/images/pm/Green.gif`
BLUE	`/itg/web/knta/drv/images/Blue.gif`
PURPLE	`/itg/web/knta/drv/images/Purple.gif`
Y	/`itg/web/knta/drv/images/Exception.gif`