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
See also
Example of the product difference test for an example that uses column bases in tables with hide rules.
See also