Formula Properties

Double-click on the Formula object to open its properties page .

Figure 1 - Example of a Formula Properties sheet with Type set to Operator

By default, the Formula object Type is set to Operators. Note that if you change the Type to Expression, some of the properties available in the sheet will change, as shown below .

Figure 2 - Example of a Formula Properties sheet with Type set to Expression

Type

Select the type of filter you wish to use. Note that the properties available on the sheet will differ depending on the selection you make here. The options are:

• Operators – the formula uses the operator selected.
• Operator - select one of the mathematical operators listed in the drop-down.
• Reference Type - when you specify the arguments of the formula, you can either define the left and right arguments by columns/rows relative to the formula column/row specified (negative numbers mean before, positive after), or by counting from the start or end row in the table.
• Left/Right Argument - define the left and right Arguments, based on the Reference Type selected. Negative numbers indicate a number of columns or rows in front of the formula; positive numbers indicate a number of columns or rows after the formula
• Expression – the formula uses the expression you type into the Expression field that is introduced when this option is selected. Here you must use the same expression language as for Conditional Formatting (go to Expressions for more information). The Expression field has an associated Help button - click this for further information.

Note: Formula expressions always use the non-rounded values of a cell.

Percent

Check the Percent box if you wish the values calculated by the formula you are designing to be presented as percentage values rather than plain numerical values.

Important
The Percent format multiplies the value in the cell by 100 and displays the result with the % symbol. The result will not necessarily be an accurate calculation of, for example, the actual percentage difference of the two Argument columns.

Priority

If formula headers are present in both the rows and columns of the table, then the priority field is used to control the calculation order of these formulae in intersecting cells. When there are intersecting cells, then the formula with the highest priority value (i.e. the largest number) will be used as the calculation for the intersection. In the case where the priority fields are equal in formula headers when intersecting cells occur, an error will be generated. Where there no intersecting cells for formula headers, then the priority can remain unchanged from the default value of zero.

Filter

You can hide rows based on the result of the calculation in a formula. For example, in a table where a gap is calculated, you might want to show just the items where there is a negative gap. To do this, use the Filter settings on the formula object .

Figure 3 - The Filter settings on the Formula object

The Comparison Operator options are: less than, less than or equal to, greater than and greater than or equal to. Enter any numerical value in the Value field. Using the settings in the figure above, the result will be a table displaying only negative gaps - the filter will remove all items with a positive gap. A typical scenario here could be that you wish to display the elements with a negative gap in one table and those with a positive gap in another.

Note: You can use the same functions and keywords in filters as those defined for expressions in formulae(go to Expressions for more information).

Label

By default, the formula column/row header will be given the name of the selected operator, or will be left blank if Expression is selected. Type text into the Label property to give the header a name other than the operator/blank. A text field will be displayed for each language selected for the report. This label can also be set globally for the report in Custom Texts (go to Custom Texts for more information).

Decimals

Use this field to specify the number of decimal places displayed. Enter a whole, positive numeric value (integers greater than or equal to 0) in this field. This functionality is the same as that in the general Table Properties .

Distributions

The Distributions define the type of numerical presentation used in the Formula column. Check the Distributions Enabled box to open the Distributions properties list .

Figure 4 - The Distributions properties

Check the desired property boxes to select the distributions you wish the table to use. The distributions are as follows:

• Count - displays the numerical value in the cell.
• Horizontal Percents - displays the percent values in a row so that they will be easy to compare by browsing horizontally through the table.
• Vertical Percents - displays the percent values in a vertical column so that they will be easy to compare by browsing vertically through the table.
• Use Innermost Totals as Base - [available when Horizontal and/or Vertical Percents are selected] The default behavior for calculating percentages is based on the overall total for the table. Check this option to obtain percentages calculated for the sub-totals of the nested questions in the specific columns or rows.
• Additional Statistic - select from the drop-down list as required, an additional statistic to be displayed. The options are:
• None - the field is empty.
• Affinity - affinity index statistic is displayed.
• Table Percent - the field displays the percentage value of the count in each cell against the total count for the entire table.
• Chi Square - the field displays the Chi Square statistic in the cell (go to Chi-Square Significance Statistic for more information).
• Hide Percent Sign - removes the % sign from the fields.

Number Formatting

This property controls how the numbers in the table are to be displayed. Expand the Number Formatting property, then select the desired format. The options are:

• Default - the numbers are displayed without any formatting.
• Currency - if you wish to display the numbers as a currency, select this option then specify the desired currency symbol. The numbers will then be displayed as monetary units, with the symbol in front.
• Time Span - select this option to specify that the numbers represent units of time. You can then also specify what the unit is to be and how the numbers are to be interpreted; Days, hours, minutes, seconds or milliseconds. The numbers will always be displayed in the format HH:MM:SS,M.

Hide Data

If you check this box, this formula column is hidden from the table after the formula is calculated. This setting is useful if you would like to use some values from a formula calculation, but not include the actual values in the table.

In the table designer, you can choose see the hidden data by selecting Show Hidden Data.

Hide Header

Check this box to cause the formula header cell to be collapsed in the table if possible. This setting is useful if you add a header only to filter a column.