Enterprise PeopleTools 8.51 PeopleBook: PS/nVision

Creating Matrix Layouts

This chapter provides an overview of matrix layouts and discusses how to:

Create matrix layouts.
Define matrix layout criteria.
Define query or ledger criteria.
Apply filter criteria.
Use label criteria.
Add variable criteria.
Define string criteria.

Understanding Matrix Layouts

Typically, the PeopleSoft data you report on is in one or more large tables with lots of details—usually ledgers. Using a PS/nVision matrix layout, you can fashion that raw data into a summarized form.

Matrix layouts have data selection criteria associated with columns and rows in the spreadsheet, creating a criteria matrix. The data retrieved for an individual cell is determined by combining the criteria for its column and row.

Example

To illustrate a matrix layout, assume that a table in the database appears as follows:

Office	Product	Sales
CHICAGO	PAPER	1,000
BOSTON	SODA	2,000
BOSTON	BOXES	1,200
TORONTO	PAPER	1,500
VANCOUVER	BOXES	5,000
COPENHAGEN	PAPER	2,000
PARIS	SODA	1,200
TOKYO	PAPER	4,000
SINGAPORE	SODA	1,000
SINGAPORE	PAPER	2,000

A table like this one could become very large in a typical business—too large to tell the manager of sales how the enterprise is doing. From this very large and detailed table, PS/nVision can build a report that summarizes sales by region and product category, with the option to break these down into offices and individual products.

Assuming that the company defines a tree that groups offices into a hierarchy of sales districts, countries, and international regions, we could use that tree to define rows of a PS/nVision report, with one row for each region. Similarly, we could use a tree of products to put different types of products—office supplies, consumer products, and so on—into separate columns. The following table shows how the summarized report might look:

Region	Beverages	Office Supplies
Asia-Pacific	1,000	6,000
North America	2,000	8,700
Western Europe	1,200	2,000

Common Elements Used in This Chapter

Navigate buttons	Use these buttons in the PeopleSoft nVision Layout Definition dialog box to select a row, column, or cell.
Retain Contents	If you clicked the Apply button in the Layout Definition dialog box and you want to reuse all or part of the criteria you just applied, select this option. It preserves all the dialog box information when you navigate to a new cell selection.

Matrix Layout Components

To create a matrix-based report, you define intersecting criteria in the layout. For example, the Beverages column in the example displays sales data where the product type is a beverage. The row criterion limits the displayed data even further. The first row displays only the data for the Asia Pacific region. The second row displays only the data for the North America region, and so on.

You can specify data selection criteria at the level of the entire spreadsheet, or at the level of a row, column, or individual cell. Generally, you specify criteria at the highest applicable level to avoid repeating criteria at the lower levels. If you have criteria that apply to the entire worksheet, (ledgers and TimeSpans are common global criteria), you specify them at the worksheet level (cell A1). If you have criteria that apply to a row, you enter the criteria in column A for that row. You enter column criteria in Row 1 in the applicable column, and you enter criteria that are unique to a single cell in that cell only.

Note. Cell criteria affect the efficiency of the report, so you should use them only when necessary.

nPlosion

If a field criterion uses a tree node value, nPlosion automatically adds rows or columns that contain the detail values found under that tree node in the format you specify in your layout. This creates a group of rows or columns that can be summarized—showing just the tree node value—or expanded to show the detail values and the summarized values.

You can also use nPlosion to show detailed TimeSpan information.

TimeSpan

A TimeSpan limits query or ledger results to those from a particular time period. Although you can use TimeSpans in a query-based matrix layout, they are more commonly used in ledger-based layouts.

SetIDs and Business Units

PS/nVision uses the setID you specify when creating a matrix layout to determine available choices when you define layout criteria. If the Use Business Unit in nVision option is clear, you can omit the setID, but it is recommended that you specify one. If you enter one, it must be valid. To set the Use Business Unit in nVision option, select PeopleTools, Utilities, Administration, PeopleTools Options.

Effective Dates

When you define a matrix layout, you must always enter an effective date. Like a setID, this controls available values for defining criteria. For some criteria, you can override the global effective date.

See Also

Defining Query or Ledger Criteria

Using TimeSpans

Defining nPlosion Criteria

Criteria Types

Six types of matrix-layout criteria are available:

Query criteria

These criteria retrieve an aggregate results column from a PeopleSoft query. The criteria act as data sources for the selected cells.
Ledger criteria

Using ledger criteria is an alternative to using query criteria as a data source. While you can use both ledger and query criteria in the same layout, typically you use one or the other.

Note. You select either Query or Ledger criteria on the PeopleSoft nVision Layout Definition – Source dialog box.
Label criteria

These criteria retrieve descriptive field values from either tree nodes or detail values. Like queries and ledgers, these criteria also act as data sources—although the data is always textual rather than numeric.
Filter criteria

These criteria identify specific detail values from PeopleSoft trees. They act to limit query, ledger, and label criteria.
Variable criteria

Because you can generate many different report instances from one layout—using report scopes—hard-coded text is not an effective way of labeling a layout. Using PS/nVision variables, you can display information that is specific to each report request and report instance (for example, scope instance number, reporting period, and so on).
String criteria

You can include strings from the PeopleTools Strings table in a matrix layout. These strings are language-sensitive and are automatically translated into a user’s selected language when the report runs.

There are four kinds of criteria—query, ledger, filter, and label—that you can combine with other criteria to retrieve specific values. In fact, two of these types—filter and label—return nothing when used alone. You must combine them with another criteria type to retrieve and display any data. You can only use the two remaining criteria types—variable and string—alone. You can use the criteria types in the following ways:

Query or ledger only

When you use only a query or only a ledger as criteria, the retrieved data for the specified cell is the same as if you ran the query externally. All values for the specified column are aggregated and displayed.
Query/ledger with field (Field selected as filter criteria)

This is the most commonly used criteria combination. It enables you to limit the values retrieved from the data source (the query or ledger criteria). Essentially, the filter criteria act as a SQL Where clause, limiting the data source results to the rows in which a particular field is found to have the values you specify. You can specify more than one field or field value, in which case the cell displays the combined value of all the query or ledger results that match the filter criteria.
Label with field

As with query and ledger criteria, filter criteria act as a SQL Where clause to limit label criteria to specific values. You use this combination to retrieve descriptive data to identify rows or columns in a report. If you specify more than one field value in this criteria combination, however, the results are not combined. Instead, only one value (label) appears.
Variable only

You can only define variable criteria at the cell level—one variable per cell—and only for cells containing no other criteria.
String only

You can only define string criteria at the cell level—one string per cell—and only for cells containing no other criteria.

In addition to the rules defined above, criteria must not return values for an infinite number of cells. For example, you can define query criteria alone at the cell level because the results are displayed in just one cell. But query criteria in a column with no intersecting filter criteria would—if they were allowed to—return cell after cell of the same value, throughout the entire column. The same situation would occur if you defined criteria at the worksheet level and intersected them with criteria in a row or column.

PS/nVision does not prevent you from defining your criteria in this way, but it does not return any data for these situations. To define criteria at the worksheet level, define the intersecting criteria at the cell level, ensuring data retrieval for a finite number of cells.

For combined criteria, valid level combinations are worksheet and cell, row and cell, column and cell, cell and cell, and row and column.

See Also

Creating Ledger-Based Matrix Layouts

Criteria Inheritance Rules

Cells specified by intersection or by single-cell criteria inherit their selection criteria according to the following rules:

Criteria defined at the worksheet level specify defaults for the entire worksheet. Criteria at the worksheet level are combined with criteria for columns, rows, and cells, except where the row, column, or cell criteria give a different value for the same criterion. In this case, the worksheet criteria are overridden.
For the intersection of row and column criteria, criteria are combined where possible. For example, if a row with vendor ID filter criteria intersects with a column using query criteria, both criteria determine the resulting cell value. But if a row and column conflict—for example, if both specify a training location—the row overrides the column criteria.

However, there is a distinction between filter criteria (for example, Product tree nodes), and criteria types limited to one source, such as query or ledger data source, TimeSpan, and reversal. Data source, TimeSpan and reversal criteria follow the override rules, but filter criteria are added as you go through the sheets, columns, and rows (but not cells). Filter criteria accumulate through this process without regard for the fields they reference.
A cell can inherit other criteria, such as ledger or TimeSpan, from the sheet, column, and row levels. A cell’s individual criteria, such as a ledger, overrides anything inherited from another level. However, because a cell might need to exclude criteria for a field, PS/nVision ignores inherited filter criteria at the cell level.
After the above rules have been applied to constructing a query to retrieve data for a group of rows and columns, any instance criteria (from either a scope or the parent instance of a DrillDown) are applied. These do not replace filter criteria from the layout; the instance criteria are added to the other filter criteria (either sheet, column, row, or cell).

The following illustration summarizes inheritance rules for non-filter criteria.

Inheritance rules for non-filter criteria

Criteria Usage

The following tables show the usage for each criteria type at each level.

Query Criteria

Level	Used at this level?	Used alone?	Combined with other criteria?	Restrictions
Cell	Yes	Yes	Filter	When used alone, filter criteria return query column totals. When combined, the filter criteria must be defined in same cell.
Row	Yes	No	Filter	Filter criteria can be defined in cells or intersecting columns.
Column	Yes	No	Filter	Filter criteria can be defined in cells or intersecting rows.
Worksheet	Yes	No	Filter	Filter criteria can be defined in cells only.

Ledger Criteria

Level	Used at this level?	Used alone?	Combined with other criteria?	Restrictions
Cell	Yes	Yes	Filter	When used alone, ledger criteria return ledger amount column totals. When combined, the filter criteria must be defined in same cell.
Row	Yes	No	Filter	Filter criteria can be defined in cells or intersecting columns.
Column	Yes	No	Filter	Filter criteria can be defined in cells or intersecting rows.
Worksheet	Yes	No	Filter	Filter criteria can be defined in cells only.

Label Criteria

Level	Used at this level?	Used alone?	Combined with other criteria?	Restrictions
Cell	No	NA	NA	NA
Row	Yes	No	Filter	Filter criteria can be defined in cells or intersecting columns.
Column	Yes	No	Filter	Filter criteria can be defined in cells or intersecting rows.
Worksheet	No	NA	NA	NA

Filter Criteria

Level	Used at this level?	Used alone?	Combined with other criteria?	Restrictions
Cell	Yes	No	Query, Ledger, and Label.	Query/ledger criteria can be defined in same cell, intersecting rows or columns, or the entire worksheet. Label criteria can be defined in intersecting rows or columns.
Row	Yes	No	Query, Ledger, and Label.	Query, ledger, and label criteria can be defined in intersecting columns.
Column	Yes	No	Query, Ledger, and Label.	Query, ledger, and label criteria can be defined in intersecting rows.
Worksheet	Yes	NA	NA	NA

Variable Criteria

Level	Used at this level?	Used alone?	Combined with other criteria?	Restrictions
Cell	Yes	Yes	None	Variables can only be used alone and at the Cell level.
Row	No	NA	NA	NA
Column	No	NA	NA	NA
Worksheet	No	NA	NA	NA

String Criteria

Level	Used at this level?	Used alone?	Combined with other criteria?	Restrictions
Cell	Yes	Yes	None	Strings can only be used alone and at the Cell level.
Row	No	NA	NA	NA
Column	No	NA	NA	NA
Worksheet	No	NA	NA	NA

Creating Matrix Layouts

To create a matrix layout in PS/nVision:

Create a new layout by selecting nVision, New Layout from the nVision menu.
Enter a name for your layout.

It is saved as an XNV file.
Enter descriptive titles as appropriate in the rows and columns that you plan to use.

These titles are not required, but they can be helpful to mark the rows and columns for which you will define criteria.
Select nVision, Layout Definition to display the PeopleSoft nVision Layout Options dialog box.
Define your layout as a matrix layout and define prompting options.
Click the OK button.
Open a criteria row and column by selecting nVision, Options, Show Row and Column Criteria.
Place your cursor in the cell where you want to add criteria.
Double-click the cell or select nVision, Layout Definition to display the PeopleSoft nVision Layout Definition dialog box.
Add criteria to the layout.

The type of criteria you define determine the tabs on the PeopleSoft nVision Layout Definition dialog box you use. Add worksheet-level criteria first. This always is cell A1.

To add row criteria, place your cursor on column A, rows 2 through xxx.

To add column criteria, place your cursor on row 1, columns B through xxx.

To add cell criteria, place your cursor anywhere within the spreadsheet that you want the result to appear.

See Defining Matrix Layout Criteria.

Note. As you create a matrix layout, occasionally run a report request based on the layout to ensure that the layout will work as you expect.

See Also

Creating Layouts

Defining Matrix Layout Criteria

This section provides an overview of the process of defining layout criteria. For details about implementing different criteria types, see the sections that follow.

To define matrix layout criteria in PS/nVision:

With a nVision layout open, select nVision, Layout Definition from the nVision menu.
From the Layout Definition dialog box, select a layout column to map to a query column.
Select the Source tab.

In most cases, you select the Source tab first to define worksheet-level criteria (cell A1).

Define the data source and general TimeSpan for the layout. Applying criteria at the worksheet level helps make the report run more efficiently because you do not have to restate the general criteria in the rows, columns, or cells. You can assign any criteria at the worksheet level that you want (including data from the Filter tab). Whatever criteria you assign in cell A1 can be overridden in the row, column, or cell criteria.

A restriction exists on returning infinite cell values. If you define query or ledger criteria at the worksheet level, you can only display results by using cell-level intersections. Row, column, or worksheet-level intersecting criteria are ignored.

Note. The Ledger Type option is not available if you do not use PeopleSoft Financials products.
Select the Filter tab.

Use the options on this tab to establish criteria based on ChartFields and to define nPlosion for the ChartFields you selected. This tab is used for defining row, column, or cell criteria.

Note. Based on the current cell selection and layout criteria previously applied, some buttons on the Filter tab may not be available.
Define nPlosion defaults.

Setting nPlosion defaults can save time when defining row and column criteria. You set these defaults using the PeopleSoft nVision Layout Options dialog box (nVision, Layout Options).
Define column, row, and cell criteria—in that order.

Use the Navigate buttons to select each column, row, or cell. Then, apply criteria types by selecting the appropriate tabs:
- At the column level—with no previously defined criteria—use the Summary and Label tabs.
- At the row level—with no previously defined criteria—use the Summary, Source, Filter, and Label tabs.
- At the cell level—with no previously defined criteria—use the Summary, Source, Filter, Label, Variable, and String tabs.
By defining worksheet, column, row, and cell criteria—in that order—you define layout criteria in ascending order of precedence. At the cell level, any criteria you enter override conflicting criteria defined at higher levels. Therefore, if you have defined column-level query criteria and you define a different query for a cell in that column, then the query criteria for the cell overrides the column criteria. Remember that if you define filter criteria at the cell level, they are combined with other filter criteria defined at higher levels.
Define other layout features.

You may want to provide additional text and Microsoft Excel formulas to the report layout. Now is also a good time to specify the fonts and formatting that you want to apply to any report instances based on this layout.

To enter cell information manually, you must close the PeopleSoft nVision Layout Definition dialog box.
Save the new layout.

Click the Save button on the Microsoft Excel toolbar or select File, Save from the nVision menu to save the layout with the name you established earlier.
Run a test report.

You should run a report request based on this layout to verify that the report layout works properly. When you make the report request, PS/nVision should populate a report instance with data from your PeopleSoft database.

See Also

Specifying a Layout Definition

Using nPlosion

Creating Report Requests

Defining Query or Ledger Criteria

This section describes how to:

Select ledger criteria.
Select query criteria.

Selecting Ledger Criteria

To select ledger criteria in PS/nVision:

With a nVision layout open, select nVision, Layout Definition from the nVision menu.
Select the Source tab on the PeopleSoft nVision Layout Definition dialog box.
Select Ledger from the drop-down list.

The PeopleSoft nVision Layout Definition – Source dialog box appears.

Like query criteria, you can apply ledger criteria at the worksheet, column, row, or cell level.

This is an example of the PeopleSoft nVision Layout Definition – Source dialog box:

Ledger Name

Select the ledger to use.

Ledger Amount Column

Click the Get List button and select an amount column from the list.

TimeSpans

Select a TimeSpan to limit the amount of information returned in your report.

Reverse Sign

Select this option to change the sign of the amounts returned from the database.

See Also

Creating Ledger-Based Matrix Layouts

Choosing Query Criteria

You use a query created with PeopleSoft Query to specify data to be returned to the matrix. Query criteria specify both a query and a query column. You can select only columns that are the result of a SQL aggregate function, such as Sum or Count.

Note. The aggregate function must return a numeric value for nVision to process this value properly.

This is an example of the PeopleSoft nVision Layout Definition – Source dialog box:

To define query criteria in PS/nVision:

With a nVision layout open, select nVision, Layout Definition from the nVision menu.
From the PeopleSoft nVision Layout Definition dialog box, select the row, column, or cell that you want to apply query criteria.
Click the Source tab to view the query criteria options.

The Source tab of the PeopleSoft nVision Layout Definition dialog box appears. Use it to specify the aggregate query column that you want to use as a data source.
Select the Query option from the Type drop-down list box.

You can also select the None option.
Specify the query name and click the Get List button.

A list of aggregate columns appear in the Query Result Column drop-down list box.
Select an aggregate column from the list.

If the query does not contain any aggregate columns, there is (no entries) in the drop-down list box.

An aggregate query column is either:
- A data column with an aggregate function (such as Sum or Count).
- An expression containing an aggregate function, with the Aggregate option selected.
Optionally, select a TimeSpan to limit the query data.
To have columns or rows containing TimeSpan details automatically inserted, select nPlode Time Spans.
To reverse the sign of the amounts returned from the database, select the Reverse Sign button.

For example, you might want to see revenue reported as a positive number. This value is normally set for query criteria at the cell, row, or column level rather than at the worksheet level.
Click the Apply button to save your changes and define query criteria for a different group of cells, or click the OK button to save your changes and close the dialog box.

See Also

Using TimeSpans

Using nPlosion

Working with Aggregate Functions

Applying Filter Criteria

This section provides an overview of filter criteria and discusses how to:

Select filter criteria options.
Add field and tree node values to filter criteria.
Add field and detail values to filter criteria.
Add criteria values.
Remove fields and values from criteria.

Understanding Filter Criteria

Filter criteria comprise field or dimension criteria combined with query or ledger criteria. They display a value from the query column results. You can combine filter criteria with label criteria to display a tree node or detail value as a descriptive label. Using nPlosion, one row or column of filter criteria can generate multiple detail rows or columns.

You can apply filter criteria at the column, row, or cell level.

Filter criteria consist of one or more tree detail values. If you select more than one value for your filter criteria, the total of all specified values is used to limit intersecting query or ledger criteria, and the results appear as one consolidated value. However, where multi-value filter criteria intersect with label criteria, the label values cannot be combined. Only the first label retrieved appears.

You can accept tree effective date when you define criteria. This option enables you to have multiple trees in the layout with different effective dates, and you can compare the results of the reports based on different effective dates. In addition, when you run a report request, you can specify a new effective date that overrides effective date of all trees if effective date was specified in the layout.

Note. Accept tree effective date at the time of criteria definition is available only in nVision windows client.

When defining filter criteria, you can use detail values summarized under particular tree nodes, or detail values from value tables. If a value table is entered in filter criteria, it will be used for prompting and nPlosion labeling. During nPlosion, if some field values do not exist in the value table or if nVision cannot find corresponding labels in the value table for some field values, nVision will not include the amount for those values in the nPloded result.

Note. If the value table specified for nPloded labels has an incomplete set of values, the total may differ from a non-nPloded total.

See Also

Using DrillDown

Creating Report Requests

Selecting Filter Criteria Options

Use the PeopleSoft nVision Filter Criteria dialog box to add, modify, and delete the nodes from the criteria.

To define filter criteria in Windows client:

Open nVision client by selecting Start, Program, nVision.
Open any existing layout or create a new one.
If you create a new layout, select the Matrix Layout Sheet option, define required values, and click the OK button.
Select nVision, Layout Definition from the menu.

The PeopleSoft nVision Layout Definition dialog box appears.
Select the Filter tab, and click the Add button.

The PeopleSoft nVision Filter Criteria dialog box appears.

Note. The PeopleSoft nVision Filter Criteria dialog box can only be opened in nVision's Windows client.

This is an example of the PeopleSoft nVision Layout Definition - Filter dialog box:

This is an example of the PeopleSoft nVision Filter Criteria dialog box, which enables you to select the fields and field values to use as filter criteria:

Field

The text box to the right of the Field option displays the field that will be used as criteria. It displays value for only one field at a time—whichever field is selected in the upper text box.

You add fields to this list by clicking the Search (?) button and selecting field names. To select from a list of fields, type in a partial name before clicking the Search button.

Filter Options

Use the Filter Options section to specify the source of the field values that you want to add to the filter criteria: Selected Tree Nodes, Selected Detail Values, or All Detail Values.

If your database contains ledgers, the Selected Summary ChartField Nodes option appears. If the Selected Summary ChartField Nodes option is selected, the Tree fields appear prompting you to select a tree from which you select node values to use as criteria. You can select any tree that uses the specified field for its node values and is defined for the setID and effective date associated with the layout.

Business Unit Keyed Tree

Select this option if your criteria is based on a business unit keyed tree.

Tree/Hierarchy

Displays the name of the tree.

As of Date

Optionally, enter the As of Date values for the tree.

If the As of Date value is earlier than the effective date of the tree or is invalid, an error message appears when you save the criteria.

See Also

Using Filter Criteria

Using Summary ChartField Nodes

Multiple Scope Fields

Creating Requests

Adding Field and Tree Node Values to Filter Criteria

To access the PeopleSoft nVision Tree Nodes dialog box:

Open nVision client.
Open any existing layout or create a new one.
If you create a new layout, select the Matrix Layout Sheet option, define required values, and click the OK button.
Select nVision, Layout Definition from the menu.

The PeopleSoft nVision Layout Definition dialog box appears.
Select the Filter tab, and click the Add button.

The PeopleSoft nVision Filter Criteria dialog box appears.
Click the OK button.

The PeopleSoft nVision Tree Nodes dialog box appears.

This is an example of the PeopleSoft nVision Tree Nodes dialog box:

To add a field and tree node values to filter criteria in PS/nVision:

Open any existing layout or create a new one.
Select nVision, Layout Definition from the menu.
Select the Filter tab, and click the Add button.

The PeopleSoft nVision Filter Criteria dialog box appears.
In the PeopleSoft nVision Filter Criteria dialog box, click the prompt button to the right of the Field option.
Select a field name and click the OK button.
Enter a partial tree name in the Tree/Hierarchy field and click the prompt button.
Select a tree and click the OK button.

The Tree Nodes dialog box appears.
If you know the exact node you want to add, enter it in the Tree Node field and click the OK button.

The Tree Nodes dialog box closes and you are returned to the PeopleSoft nVision Layout Definition – Filter Criteria dialog box, which has an added node in the list box.
Optionally, apply a node list filter.

To limit node choices to a particular level, select a valid tree level.

To limit node choices to a particular tree branch, enter the tree node at the top of the branch.
Display the node list.

To see an alphabetical list of the tree nodes, click the By Name button. Use this button if you have specified a tree level. If you have also specified a tree node, it must be a valid node in the level or no nodes will appear in the list.

To see the hierarchical node structure of the tree—similar to what you would see in Tree Manager—click the By Position button. Use this button if you have specified a tree node. If you also specified a tree level, it is ignored.

Note. You can use the two list filtering buttons together to help you find the nodes you want. For example, you might first want to see nodes at a particular tree level, in alphabetical order, using the By Name button to find the higher-level node that you want. Then, you can specify that tree node and click the By Position button to see the portion of the tree headed by that node.
Highlight the nodes you want to add.
Click the OK button to add the highlighted nodes.

The Tree Nodes dialog box closes. You can now see the new node values listed in the tree control list box of the PeopleSoft nVision Layout Definition – Filter dialog box.

Adding Field and Detail Values to Filter Criteria

This is an example of the PeopleSoft nVision Criteria Values dialog box:

To add a field and detail values to filter criteria in PS/nVision:

Open any existing layout or create a new one.
Select nVision, Layout Definition from the menu.
Select the Filter tab, and click the Add button.

The PeopleSoft nVision Filter Criteria dialog box appears.
Enter a field name.
Select either the Selected Detail Values option or the All Detail Values option.

Note. If the field is a DrillDown child layout, you can select all detail values from a tree node. The All Detail Values option is used primarily with nPlosion and DrillDown features.
If you selected the All Detail Values option, enter a values table name.
Click the OK button.

If you selected the All Detail Values option, the Field Name dialog box closes and the PeopleSoft nVision Layout Definitions – Filter dialog box appears again. You can skip the rest of this procedure if you do not need to add specific values and you are using all of them.

If you select the Selected Detail Values option, the PeopleSoft nVision Criteria Values dialog box appears. You use this dialog box to specify the tree detail values you want to add to your filter criteria.
In the Qualifiers group box of the PeopleSoft nVision Criteria Values dialog box, specify a values table.
Optionally, enter a new effective date.

The default effective date is the value you specified when creating the layout definition.
Select the values to add.

If you know the value you want, enter it in the Enter Individual Value field and click the OK button. Otherwise, use the prompt button to display a list of detail values from the Values Table that you specified. You can select multiple values before clicking the Add to List button.
Select the Blank Value option—with the Enter Individual Value field empty—to include a null value.
Click the Add to List button.

Clicking the Add to List button adds a null value to the list, represented graphically by (None) in the Current Value List text box. The null value appears at the top of the list in the list box, but the actual null value is inserted at the bottom of the list on the PeopleSoft nVision Layout Definition – Filter dialog box.

You can change the order of a value by using the order selection controls on the PeopleSoft nVision Layout Definition – Filter dialog box.
Click the OK button to save your changes and to close the PeopleSoft nVision Criteria Values dialog box.
Click the OK button again to close the PeopleSoft nVision Layout Definition dialog box.

See Also

Using DrillDown

Adding Criteria Values

To add criteria values in PS/nVision:

Open any existing layout or create a new one.
Select nVision, Layout Definition from the menu.
Select the Filter tab, and click the Add button.
On the PeopleSoft nVision Filter Criteria dialog box, select a specified field.

If you have not specified any fields, you must do so now. During that procedure, you are prompted to add criteria values.
Click the Add button.
At the new dialog box, add your values.

Removing Fields and Values from Filter Criteria

In PS/nVision, to remove a field and its values from filter criteria:

Open any existing layout or create a new one.
Select nVision, Layout Definition from the menu.
Select the Filter tab, and click the Add button.
On the PeopleSoft nVision Filter Criteria dialog box, select the field to remove.
Click the Delete button.

In PS/nVision, to remove a field value from filter criteria:

Open any existing layout or create a new one.
Select nVision, Layout Definition from the menu.
Select the Filter tab, and click the Add button.
On the PeopleSoft nVision Filter Criteria dialog box, select the field value you want to remove.
Click the Delete button.

Using Label Criteria

You can use label criteria to add descriptive information that corresponds to filter criteria you have defined in the rows and columns of the layout. When you define label criteria, you specify a tree node name or a detail value as the label source. PS/nVision uses this information to generate row and column labels automatically at runtime from detail or tree node records in the database. When you run the report, the rows and columns appear with their labels. Report labels are particularly useful with nPloded rows and columns, because these are generated by PS/nVision, and you do not have a chance to label them yourself.

You can define label criteria at the row or column level. You cannot define them in any row or column that already contains other criteria types. To label filter criteria entered in the rows of the layout, specify the label criteria in an intersecting column; if you are labeling filter criteria columns, put the label criteria in a row.

This is an example of the PeopleSoft nVision Layout Definition – Label dialog box:

To define label criteria in PS/nVision:

Open any existing layout or create a new one.
Select nVision, Layout Definition from the menu.
In the PeopleSoft nVision Layout Definition dialog box, select the row or column to which you want to apply label criteria.
Select the Label tab.
Use the Retrieve Label group box to specify where you want the label to be retrieved from.

If you are labeling filter criteria that are based on detail table values, use the Field on Detail Value Table field to specify a label source. You should also use this field if you want to label the nPloded rows or columns of node-based filter criteria. Enter either the field name itself or a descriptive field from the detail value table, which is defined in the tree structure.

Note. If you are using fiscal year TimeSpans, you can also specify a special label for nPloded TimeSpans by entering ACCOUNTING_PERIOD in the Field on Detail Value Table field.

If you are labeling filter criteria based on tree node values, use the Field on Tree Node Table field to specify a label source. For detail or summary tree node values, enter either TREE_NODE or DESCR, as these are the only descriptive fields on the TREE_NODE_TBL (as delivered).

For node-oriented trees, enter either the field name itself or another descriptive field from the table that supplies the node values, as defined in the tree structure. For example, for filter criteria based on nodes in the DEPT_SECURITY tree, you might use DEPTID, DESCR, or SHORTDESCR.
Select your runtime options.

The Runtime Options group box is not visible until you enter a field name in the Retrieve Label group box.

If you select the Put labels in blank cells only option, the labels will not overwrite any text, PS/nVision variable, or strings that you have inserted in the layout.

If you have selected a column, you can also select the Resize column for labels option. This value automatically applies the Excel AutoFit command to the column at runtime, which makes the column as wide as the widest label.
Click the Apply button to save your changes and define label criteria for a different row or column.

Alternatively, click the OK button to save your changes and close the dialog box.

See Also

Understanding Types of Trees

Adding Variable Criteria

This section provides an overview of variable criteria and explains how to define the criteria.

Understanding Variable Criteria

You insert PS/nVision variables into the layout to display heading information that might change from report to report, or between report runs. For example, you could use a variable to automatically insert the report ID you specify in the Report Request dialog box, so you do not restrict this layout to a single purpose. Remember that your layout may be used with a scope that changes its contents, which could make a hard-coded title misleading.

You can define variable criteria at the cell level only—one variable per cell—and the variable must be the only element in that cell.

Note. Besides using PS/nVision variables in your layouts, you can use some of these variables in the Instance Controls section of the PeopleSoft nVision Report Request dialog box.

When inserting a variable into a cell, you select it from the Variable tab of the PeopleSoft nVision Layout Definition dialog box. Because there are many different variables to select from, the dialog box displays them by category. Tables describing the variables in each category follow.

Note. When you select a variable, you select its three-letter code. However, when a variable is used in a layout or report request, its code must be enclosed within percent signs (for example, %RID%). The following tables omit the percent signs.

Report Request Variables

Most of the values returned by these values are defined on the Report Request dialog box.

Variable	Returned Value	Sample Value	Remarks
DTS	Detail or Summary (nPlosion enabled or disabled)	S	Defined in the Report Request dialog box: S=Summary (nPlosion disabled) D=Detail (nPlosion enabled)
ICT	Instance Counter	1	Starts at 1 and is increased by increments of 1 for each additional instance.
IDN	Instance Directory Name	C:\USER\NVISION\INSTANCE	Full path. The Directory Template field defines the full path in the Report Request dialog box.
IFN	Instance Output File Name	<Various>.XLS	The File Template field defines the path in the Report Request dialog box. The .XLS extension is included.
LAN	Language Template	ENG	Defined in the Report Request dialog box.
LYN	Layout Name	<Various>	Defined in the Report Request dialog box. (Does not include the .XNV extension.)
OPC	User Class	ALLPANLS	Provided by the PeopleSoft security tables.
OPL	User Language	ENG	Provided by the PeopleSoft security tables.
OPR	User ID	WPS004	Provided by the PeopleSoft security tables.
RID	Report Name	<Various>	Defined in the Report Request dialog box.
RBK	Report Book Name
RTT	Report Title	<Various>	Defined in the Report Request dialog box.

Date and Time Period Variables

These values help you label layouts where different accounting periods are reported in each instance.

Variable	Name	Sample Value	Remarks
APA	Period Abbreviation	DEC
APN	Period Name	December
ASD	As of Reporting Date	2003-12-31	Defined in the Report Request dialog box.
AST	As of Tree Date	2004-01-01	Defined in the Report Request dialog box.
FY2	Year (YY)	04
FY4	Year (YYYY)	2004
PED	End Date of Current Period	2004-12-31
PER	Accounting Period	12

Scope-Related Variables

These values help you label layouts for which you have defined a report scope. A scope is used to define multiple instances of a report based on different field values. For example, you could produce an instance of an expense report for each department, or an operations summary for each business unit.

Variable	Name	Sample Value	Remarks
BUV	Business Unit Name	M04	Defined in the Report Request dialog box.
BUN	Business Unit Description	US1 Manufacturing
SCN	Scope Name	DEPARTMENT
SCD	Scope Description	Sales Departments
SFN	Scope Field Name	DEPTID
SFV	Scope Field Value	FINDEVELOP
SFD	Scope Field Description	Financial Development
STN	Scope Tree Name	FUNCROLLUP
STD	Scope Tree Description	Functional Organization
SLN	Scope Tree Level Name	DIVISION
SLD	Scope Tree Level Description	Instances for each division
DES	Scope Descriptive Field	FINDEVELOP	A user-defined variable that retrieves descriptive information from a field in either the detail value table or the tree node table.

See Also

Defining Report Scopes

Creating Requests

Defining Variable Criteria

This is an example of the PeopleSoft nVision Layout Definition – Variable dialog box:

To define variable criteria in nVision:

Open any existing layout or create a new one.
Select nVision, Layout Definition from the menu.
In the PeopleSoft nVision Layout Definition dialog box, select the cell to which you want to apply variable criteria.
Select the Variable tab.
Select the appropriate category, and then select a variable.
If you selected one of the Date and Time Periods variables, specify a ledger.

When you select the Date and Time Periods option and you click a variable in the Variables list, the Ledger field appears.
If you selected one of the Scope-Related variables, fill in the Scope Field field, if necessary.

When you select any Scope Related variable except SCN or SCD, the Scope Field text box appears to the right of the Variables field.

If the layout uses a scope that has multiple fields, use the Scope Field option to specify the scope field on which to base the variable you want to insert. For example, if you defined a scope using the Department and Product fields, and wanted a descriptive field from the Department table to appear on your report, you would enter DEPTID as the scope field.

Note. If you do not specify a scope field, the default value is the first field defined in the scope.
If you selected the DES variable, indicate where to retrieve the descriptive information.

The Descriptive Field (DES) variable is user-defined and retrieves text information from either the tree node table or detail value table associated with a field in the scope. For example, if your scope is based on DEPTID, and creates an instance for each tree node at the Division level, you can use variables to identify each instance with the division name and related information from the tree node.

Use the Field on Detail Value Table field to retrieve descriptive information from any text field on the detail values table that is associated with the scope field. For example, if you were generating instances of a report using a scope based on detail values of the Department field, you could enter the name of any descriptive field, such as the Manager_Name field, on the Department table, and the text contained in that field would appear on each department’s instance of the report.

Use the Field on Tree Node Table field to retrieve descriptive information from any text field on the tree node table (usually named TREE_NODE_TBL) when using a tree-based scope. For example, if you added a field for the manager responsible for each node in your tree, you could retrieve this information by specifying the field name, such as Mgr_Name, from the tree node table.
Click the Apply button to save your changes and define filter criteria for a different cell.

Alternatively, click the OK button to save your changes and close the dialog box.

Defining String Criteria

Layouts typically contain a considerable amount of constant text, such as the column headings “Last Year to Date” or “Current Budget.” With PS/nVision, you can build multilingual layouts where these text strings are replaced by specially formatted strings whose user-language equivalent is retrieved from a table in the database. These string names are somewhat like user-defined PS/nVision variables.

You insert string criteria into layout cells with the following format:

%.<name>,R<program>%

Name is the string name as described in the following table and program is the program ID group that contains the string definition. In this example

%.STDHDG_PAGE_NO,RSTDHDGTR%

the string name is STDHDG_PAGE_NO and the program ID is STDHDGTR.

When you select a string to insert, you can select from strings created specifically for use with PS/nVision—that is, strings with a program ID of NVISION. If you insert one of these strings, the program name does not appear in the string. For example:

%.DATE_LABEL%

This is an example of the PeopleSoft nVision Layout Definition – String dialog box:

To define string criteria in PS/nVision:

Open any existing layout or create a new one.
Select nVision, Layout Definition from the menu.
On the PeopleSoft nVision Layout Definition dialog box, select the cell to which you want to apply string criteria.
Select the String tab.
Clear the nVision Only String option if appropriate.

By default, the String tab displays only strings that were created for use with PS/nVision—those with a program ID of NVISION.

To select from all available strings, clear the nVision Only String option.
If the nVision Only String option is cleared, select a program ID.
Select the String ID of the string you want to insert.

You can select from the strings assigned to the program ID that you specified.

Note. If the nVision Only String option is selected, the program ID is NVISION.
Click the Apply button to save your changes and define string criteria for a different cell.

Alternatively, click the OK button to save your changes and close the dialog box.

Ledger Name	Select the ledger to use.
Ledger Amount Column	Click the Get List button and select an amount column from the list.
TimeSpans	Select a TimeSpan to limit the amount of information returned in your report.
Reverse Sign	Select this option to change the sign of the amounts returned from the database.

Field	The text box to the right of the Field option displays the field that will be used as criteria. It displays value for only one field at a time—whichever field is selected in the upper text box. You add fields to this list by clicking the Search (?) button and selecting field names. To select from a list of fields, type in a partial name before clicking the Search button.
Filter Options	Use the Filter Options section to specify the source of the field values that you want to add to the filter criteria: Selected Tree Nodes, Selected Detail Values, or All Detail Values. If your database contains ledgers, the Selected Summary ChartField Nodes option appears. If the Selected Summary ChartField Nodes option is selected, the Tree fields appear prompting you to select a tree from which you select node values to use as criteria. You can select any tree that uses the specified field for its node values and is defined for the setID and effective date associated with the layout.
Business Unit Keyed Tree	Select this option if your criteria is based on a business unit keyed tree.
Tree/Hierarchy	Displays the name of the tree.
As of Date	Optionally, enter the As of Date values for the tree. If the As of Date value is earlier than the effective date of the tree or is invalid, an error message appears when you save the criteria.