Understanding Restrictions on Tree Performance Options

In certain cases, PS/nVision may override the specified tree SQL technique. The dynamic selector technique is not used when a field has tree criteria in multiple dimensions (for example, both row and column), or when a field has the same tree criteria in multiple places (for example, in both sheet level and row level). This can also happen at DrillDown time if criteria for a field are inherited from multiple dimensions (for example, the scope and column) of the parent report. With the dynamic selector technique unavailable, PS/nVision uses either the suppress join technique (if requested and if feasible) or the static selector technique.

You cannot select the single value option with static selectors because the static selector remains unchanged until the tree changes. However, the addition of single values to the fact table, which the tree detail is based on, doesn’t affect a tree change. The single-value options exist for dynamic selectors and for enabling you to control the syntax used with ranged selectors.

PS/nVision ignores the suppress join technique if specified for a tree with summary ChartField node criteria. Summary ChartField nodes, or detail values in summary trees, are tree nodes from a detail tree rather than values from a database field. In addition, PS/nVision does not support translation of summary ChartField nodes when drilling down to the detail ledger, so we recommend that you use the summary tree criteria.

The suppress join technique is available for reports based on summary trees, as long as tree node information is not needed to group the result. It may be possible to recode some reports that use detail value criteria for summary trees for performance reasons.

These performance-enhancement techniques are not used when retrieving labels (such as account descriptions). Labels for detail fields associated with tree criteria are retrieved using static selectors. The SQL code used to retrieve labels is defined at a different level from the SQL used to retrieve amounts, so it isn’t possible to use the same dynamic selectors for both.

Because criteria from multiple rows and columns are combined with the instance (scope) criteria in a single Select statement, SQL statements generated by PS/nVision can be long and complex. While current releases of PS/nVision no longer enforce a statement size limit, every database platform has a maximum statement size, and even statements shorter than the maximum may be inefficient. You control statement size through judicious use of the performance options.

Here are the common causes of oversized SQL statements:

• Use of the suppress join technique on a tree (or trees) from which nodes representing too many detail ranges are requested.

  Suppressing a join can be very useful, but is recommended only when criteria from a given tree require a relatively short list of detail values or ranges.

• Use of static selectors with a very long list of nodes.

  PS/nVision combines node number ranges for sibling nodes where possible, so it takes lots of nodes to exceed the limit. Use of dynamic selectors makes the SQL much shorter.

• An extremely long list of detail values.

  The messages that indicate a statement is too long vary from platform to platform. For statements made long by tree criteria, the most successful solution is generally to use the dynamic selectors technique on one or more of the trees involved.