Designing Indexes - Combination Guidelines

PS/nVision endeavors to retrieve the data for each report instance with as few SELECTs as possible. It examines all row criteria to determine which can be combined, and does the same for column criteria. It then builds a Select statement to retrieve each intersection of a combined group of rows with a combined group of columns. You should understand the following built-in rules when designing indexes:

• Different ledgers cannot be combined.

• Different TimeSpans cannot be combined.

• nPloded rows or columns cannot be combined with non-nPloded rows or columns.

• To be combined, two or more rows or columns must have criteria for the same set of ChartFields, and each ChartField's criteria must be of the same type (selected tree nodes cannot be combined with selected detail values).

• If criteria for a ChartField are specified by tree node, they can only be combined if they use the same tree.

• If the combined rows or columns have identical criteria for a particular ChartField, the criteria are included in the "where" clause but no "group by" on that field is required.

  If different rows/columns in the group have different criteria, PS/nVision adds this field (or the corresponding tree node number) to the "group by" clause to retrieve a value for use in posting the answer set to the report.

• A single Select statement can retrieve amounts for multiple combined rows and columns.

• Different scope instances are retrieved with separate Select statements.