Specifying Data Model Values

Use the Specify Data Model Values page (PTPG_WIZ_MODEL) to define the column type and aggregate functions for the selected data model.

Navigation:

Access the Specify Data Model Values page by selecting the Next button on the Select Data Source page.

This example illustrates the fields and controls on the Specify Data Model Values page. Definitions for the fields and controls appear following the example.

 Specify Data Model Values page

Field or Control

Description

Column Label

(Optional) Specify the labels for columns (dimensions/facts). The labels apply to grids and charts.

Note: Column labels must be unique. No two data source columns can have the same label.

Aggregate Label

(Optional) Enter the aggregate labels for the columns. These aggregate labels will be:

  • Populated in the Y-axis field in the Specify Data Model Values page, Chart Options section and in the User Charting Options page.

  • Displayed as the titles of the columns in the grid.

  • Displayed as the title of the Y-axis in the chart.

  • Populated in the Y-axis field in the User Charting Options page when you update the chart options.

For example, the label of the Unit Cost column is set to Total Cost, the label of the Sales column is set to Total Sales, and the label of the Prd Sales column is set to Total Prd Sales.

  • In the Specify Data Model Options page, Chart Options section, if the Unit Cost column is selected as the Y-axis, then the value in the Y-axis label is populated as Total Cost.

  • In the chart, the title of the Y-axis is Total Cost.

  • In the grid, the titles of the columns are Total Cost, Total Sales, and Total Prd Sales.

The behavior of the Aggregate Label field is applied to both classic and fluid views.

Column Type

Define the axis, value, or display members for a column.

Select the Display option to enable the column to appear in the detailed-data view of the grid and the chart.

Note: At least one Axis and one Value member are required. You can also select this column as a related action parameter for a Pivot Grid model in the detailed-data view.

If the PSQuery formula is based on an expression and that expression is used in the Pivot Grid model, then:

  • The formula-based expression can be used only as a column value and no aggregate function should be applied if the aggregation functions are derived from the other columns which make up the expression.

    You can select the None option in the Aggregate column for the expression to avoid multiple aggregation on the column.

  • The formula-based expression cannot be used as a column axis.

  • The formula-based expression should be comprised of only other column values. For example, (ValueA + ValueB/100) or ((ValueA + ValueB)/ValueA)*100.

Total

Select which Axis members have the Total (All) attribute enabled.

Note: This column is available only for the data source column with column type Axis.

Aggregate

Define the aggregate functions for the Value type members. Available options are Average, Count, Count Distinct, Maximum, Minimum, Sum, and None.

Note: This column is only available for the data source column with column type Value.

Value members of type Number and Signed Number can be associated with any of the aggregate functions. Value members of any other type can only be associated with the Count aggregate function.

Count or Count Distinct aggregates are available for the fact fields that are set to character.

If the PSQuery expression is based on a formula and that expression is used in the Pivot Grid model, then the type of the column should be set to Value, and the Aggregate value should be set to None to avoid multiple aggregations on the column.

Total Name

(Optional) Specify the total names, which override the default All string.

Editable Facet

Select to enable the data source columns as editable facets (prompts) in fluid view.

See Configuring Facets and Filters.

Next

Click this button to advance the wizard to the next page.

The Select Query Prompt Values region is only available when the selected query has prompts attached.

This example illustrates the fields and controls on the Select Query Prompt Values section. You can find definitions for the fields and controls later on this page.

 Select Query Prompt Values

Field or Control

Description

Select Query Prompt Values

Enter the default values for the PSQuery runtime prompts.

Note: The default value in the Select Query Prompt Values region is blank and you are able to define your prompt values. If the required prompt fields are blank or if the format of data entered is incorrect, Pivot Grid Wizard displays a validation error.

Configure Visible Prompts

Field or Control

Description

Visible Prompt

Select to make the query prompts visible to the viewer.

If you do not make any of the prompts visible then, the Prompts option will not appear in the Options Menu in Pivot Grid Viewer.

Show Operator

Select to show the assigned operators for a prompt value.

See Displaying Operators on Editable Facets

Note: Show Operators is only supported in component based pivot grid models.

This example illustrates the fields and controls on the Tree Options section. You can find definitions for the fields and controls later on this page.

 Tree Options section

Field or Control

Description

Attach Tree

Click to access the Attach Tree to Dimension window, where you can select a tree from a list of available trees and attach it to the dimension.

Note: Only Summer Detail Trees that match the corresponding Axis are listed.

Detach Tree

Click to detach a current PeopleSoft tree from the dimension.

Tree Name

Display the tree names that you have selected using the Attach Tree button.

Tree Node

This field is required.

The Tree Node is populated with the Root node of the selected tree. However, this value can be changed to point to any node in the tree. The hierarchical information for the dimension is shown from this node only.

This example illustrates the fields and controls on the Formatting Options section. You can find definitions for the fields and controls later on this page.

 Formatting Options section

Field or Control

Description

Currency Control Field

Select Currency field as the Currency Control Field. The decimal positions for the currency value are based on the currency control field. For example, two decimal positions appear for U.S. dollars or three decimal positions appear for the Brazilian real.

Note: The query should include the Currency field as a Data Source Column to associate it as the Currency Control Field.

These currency control fields can be associated with the columns set to Value and Display.

Show Currency Symbol

Select this option to display the currency symbol for the columns that are set to Value. If this option is selected:

  • The Value column is appended by the currency symbol, such as $, £, and so on.

  • The Y-axis columns will display the currency symbols next to the label of the axis.

  • When users hover over the charts, the symbols appear in the embedded labels to show the data points plotted on the chart.

Note: This option is accessible only when the Currency option is selected in the Currency Control Field.

Display 1000 Separator

Select to display the 1000 separator for the Value. For example, the value of ten thousand dollars appears as $10,000.00 and Product Count appears as 1,000.

Sequence

Click to access the Configure Column Axis Sequence page. Use this page to define order or sequence for values of axis fields.

Note: If no value is set in the Formatting Options tab, the system displays the currency values using the default settings, for example, 123456.78.

The alternate text in the chart and in the values on the Detail View displays the currency in the defined formats.

Defining Sequence for Values of Axis Fields

Click the Sequence link in the Formatting Options section to access the Configure Column Axis Sequence page. Use this page to define order or sequence for values of axis fields.

This example illustrates the fields and controls on the Configure Column Axis Sequence page. You can find definitions for the fields and controls later on this page.

 Configure Column Axis Sequence page

Field or Control

Description

Translate Field

Select this check box to use a field with translate values as the Field Name. Correspondingly, a drop-down list with translate values of the Translate Field appears in the Column Data field.

Field Name

This field is enabled only when the Translate Field check box is checked. Select a value for the Field Name to activate a dropdown list with translate values of the Translate Field in the Column Data field.

Seq

Define order or sequence for values of axis fields.

Column Data

Enter data for the axis field.

The Column Data field can either be a dropdown list with translate values of the Translate Field if the Translate Field is checked and the Field Name is provided, or a text box where data for axis field can be entered.