Defining hide rules based on a reference to a single value element in tables with row or column base cell items

If you define a hide rule with an ElementRef parameter referring to a single-value element (for example, a mean) you must make the CellItemRef parameter refer to the first cell item that is not a Row Base, Column Base, Unweighted Row Base, or Unweighted Column Base. A single-value element is one such as mean, standard deviation, minimum p-value, etc, where the single value overrides the cell items that appear on the table.

For example, suppose that you create a default hide rule to hide columns for which the mean age is less than 30:

Set Rule = TableDoc.Default.Rules.AddNew()

Rule.Type = 0 ' hide

Rule.Target = 0 ' row

Rule.CellItemRef = 0 ' if cellitem 0

Rule.Operator = 0 ' is less than

Rule.Value = 30 ' 30

Rule.ElementRef = "age{mean}" ' for row mean()

Suppose that you also change your tables to include the following cell items:

▪column base

▪counts

▪column percentages

The single value element (in this case, mean) is always placed in the first non-base cell item, in this case, counts. However, the default hide rule refers to cell item 0. In a table with the above cell items, this is the column base cell item, not the counts cell item, so will not give the correct results.

To display the table correctly, change the CellItemRef parameter to refer to cell item 1 (where counting starts at 0), the counts cell item.

With Tables.AddNew("Table19", "interest * age{.., mean()}", _

"Interest by age with base as first cell item, hide rows _

where mean is less than 30")

' Change the cell item used in the hide rule to first non-base

cell item (counts)

.Rules[0].CellItemRef = 1

End With

"Interest by age with base as first cell item, hide rows _

where mean is less than 30")

' Change the cell item used in the hide rule to first non-base

cell item (counts)

.Rules[0].CellItemRef = 1

End With

See also Example of the product difference test for an example that uses column bases in tables with hide rules.

See also