Matrix Layout Components

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 Understanding Ledger-Based Matrix Layouts, Defining Ledger Criteria.

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.

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