Professional > Table scripting > Getting started > Handling zero values and setting hide rules
 
Handling zero values and setting hide rules
Sample script file: ZeroValues.mrs
In Creating a frequency table and defining cell contents, we saw that the default character for zero percentage values is a hyphen (-). You can define six separate special characters to be displayed instead of 0 for zero counts, percentages, and other cell contents, and for counts, percentages, and other cell contents values that are rounded to zero, respectively. You can use any string for these characters and you define them using the table properties. You can define these characters for individual tables or as defaults to apply to all new tables.
You can also hide rows and columns in which all of the values are zero by defining hide rules or manually removing the rows and columns from the table.
The examples in this topic assume that you have already created the Table Document object and loaded the Museum sample data set as shown in Creating a simple table of Age by Gender. The examples in this topic create tables using the education and before variables, which both have a Not answered category that was not selected by any respondents.
First let's create a table that shows zero values as asterisks:
Dim Table1
Set Table1 = TableDoc.Tables.AddNew("ShowAsterisks", "education * before", _
"Show zero values as asterisks")
Table1.Properties["ZeroCountSymbol"] = "*"
Table1.Properties["ZeroPercentSymbol"] = "**"
Notice that the last two lines set the table's ZeroCountSymbol and ZeroPercentSymbol properties to * and **, respectively. This means any true zero count values in this table will be displayed as a single asterisk and any true zero percentage values in this table will be displayed as two asterisks.
Table showing zero values as asterisks
If you want to use these symbols in all your tables, you could set the default properties rather than the properties on each individual table. Changing the default properties does not affect tables that have already been created. This means that if we want the new default setting to apply to our table, we need to put the line that sets the defaults before the line that creates the table, like this:
TableDoc.Default.Properties["ZeroCountSymbol"] = "*" TableDoc.Default.Properties["ZeroPercentSymbol"] = "**"

Set Table1 = TableDoc.Tables.AddNew("ShowAsterisks",_
    "education * before", "Show zero values as asterisks")
You can set a different display value for counts and percentages that are rounded to zero using the RoundZeroCountSymbol and RoundZeroPercentSymbol properties. The default value for these properties is an asterisk. For a full list of other similar properties that you can set on individual tables or as table document defaults, see Table properties.
Here is the table produced by the script:
Table with zero rows and columns hidden
The next table has two hide rules, to hide rows and columns in which all of the values are zero.
Dim Table2

Set Table2 = TableDoc.Tables.AddNew("HideZeros", "education * before", _
 "Hide zero rows and columns")
Table2.Rules.AddNew()
Table2.Rules.AddNew(, 1) ' rtColumn = 1
Here is the table produced by the script:
Notice that details of the hide rules are shown in a footer. See Annotations for more information.
Table with empty elements removed
Instead of defining hide rules to hide zero values, you can set up hide rules to hide values greater or less than a certain value. In addition, you can set up default hide rules to apply to all new tables.
An alternative method of hiding rows or columns is by explicitly removing them from the axis. For example, the following example actually removes from the table the row and column for the two Not answered categories:
Dim Table3

Set Table3 = TableDoc.Tables.AddNew("RemoveEmptyElements",
    "education * before", "Remove empty elements")
Table3.Axes["Side"].SubAxes[0].Elements.Remove("Not_answered")
Table3.Axes["Top"].SubAxes[0].Elements.Remove("Not_answered")
Here is the table produced by the script:
Requirements
UNICOM Intelligence Reporter
Next
Concatenation and nesting
See also
Getting started