Hide rules based on cell items
Sample script file: HideRules.mrs
This example script is based on the Museum XML sample data set. See
Running the sample table scripts for more information.
By default, the cell item used to test the hide rule is the first cell item that appears in the table. In the examples shown in the other topics in this section, this is the Counts cell item. If required, you can specify a different cell item to use in the table. If you add new cell items or change the order of cell items, ensure that you refer to the correct cell item when specifying a hide rule. The first cell item in the table is specified as cell item 0, not 1.
For example, suppose that you are using the cell items counts, column percents, and a sum based on the dinosaurs variable:
With TableDoc.Default.CellItems
.Clear() .AddNew(0) ' counts
.AddNew(1) ' column percents
.AddNew(5, 0, "dinosaurs") ' sum(dinosaurs)
End With
A table with these cell items, and with a hide rule based on the default cell item 0, will base the hide rule on the value of counts.
With Tables.AddNew("Table17", "interest * age{.., mean()}", _
"Interest by age with three cell items, hide rule _
on cell item 0 (counts)")
Set Rule = .Rules.AddNew()
Rule.Type = 0 ' hide
Rule.Target = 0 ' row
Rule.CellItemRef = 0 ' if cellitem 0 (counts)
Rule.Operator = 0 ' is less than
Rule.Value = 30 ' 30
End With
Three rows with counts of 30 or over in the base column are displayed, as the counts must be less than 30 in all columns for the row to be hidden.
Alternatively, you can base the hide rule on column percentages, by specifying cell item 1:
With Tables.AddNew("Table18", "interest * age{.., mean()}", _
"Interest by age with three cell items, hide _
rule on cell item 1 (column percents)")
Set Rule = .Rules.AddNew()
Rule.Type = 0 ' hide
Rule.Target = 0 ' row
Rule.CellItemRef = 1 ' if cellitem 1 (column percents)
Rule.Operator = 0 ' is less than
Rule.Value = 30 ' 30
End With
This hides rows in which all columns have column percentages of less than 30:
You could also base the hide rule on the sum(dinosaurs) cell item.
See also
