FormulaString Property

• A formula is an expression consisting of one or more terms separated with binary and/or unary operators. They do not start with an equals sign (=). Instead, they just start with the first term.
• Terms in the formula can be numeric constants, string literals, function invocations, references, or parentheses surrounding an entire expression.
• Numeric constants can be of the form 1234.567, where either the whole number or fractional portion can be omitted, such as 5 or .71
• String literals are text surrounded by double quotes or single quotes, such as "some text" or 'single quoted text'. If the character used for surrounding the literal needs to be used in the text, it can be escaped with the backslash (\) character.
• Function invocations are function names followed by zero or more comma-separated arguments which are surrounded by parentheses, such as SUM(1, 2, 3). The function names are case-insensitive and they consist of one or more letters, numbers, or underscores and cannot start with a number. The function name can refer to a built-in function or a custom function registered with the calculation manager. The arguments of the function can be expressions or range references, which are described below.
• References are surrounded by square brackets and can refer to controls in the calculation network, logical descendant references in a control (such as bands and columns in a grid), and custom named references added to the calculation manager which could have other formulas. For example, PI could be added to have a formula of "3.14". Then other formulas can reference it by using [PI].
  • Reference names are case-insensitive and can be relative or absolute. When using an absolute reference the name must start with //, such as [//PI]. This will allow access to all root level references, such as controls and named references.
  • For controls supporting a hierarchy of references, such as a grid, the forward slash (/) can be used to descend into child references. For example, [//grid1/Customers/Name] could be used to access the Name column of the Customers band in a grid named grid1. Relative references can also contain two dots (..) to access a parent level in a hierarchy, similar to a relative directory path. So if a formula in a cell in a grid used [../Column1], this would access the Column1 cell of a parent row owning the child band island.
  • Parentheses can also follow names in the reference to access absolute or relative items in the reference path. For example [Total(-1)] in a grid would access the Total cell in the previous row, or [//grid1/Customers/Name(3)] would access the name of the customer in the 4^th row. It is up to the control to determine how to interpret the relative and absolute references, and it may even be configurable in each control. For example, the grid may allow options to make absolute indexes mean visible indexes or original indexes in the data source.
  • An asterisk can be used to access all values across a collection. For example, [Total(-1)] might refer to a total in the previous row whereas [Total(*)] would refer to totals in all rows.
  • Values or key-value pairs can be used in the parentheses to more easily identify references, as in [//grid1/State(NY)/City(Albany)/Customers(CustomerID=ABCD)/Total]. These can be comma separated in the parentheses to specify multiple conditions.
  • For some references, parentheses may be required to access them. For example, summaries in a grid may need to be accessed with parentheses to distinguish them from normal bands and columns: [//grid1/GrandTotal()].
  • If a reference name in a formula contains one of the following special characters, it must be escaped with a backslash (\). The special characters are: \ / [ ] ( ) " , =
• Range references can only be used as function arguments and they reference one or more ranges. It is up to the owning control to determine how the endpoints of the range describe the full set of references in that range, but the syntax is as follows: two references separated by two dots (..) or a colon (:). For example, [Orders(0)]:[Orders(3)] would encompass the first four orders in a grid perhaps. In the context of range references, the asterisk (*) has a different meaning and refers to the last item in a collection. This might be the last item in the current child band island of a grid perhaps. So [Orders(1)]..[Orders(*)] would refer to all orders except the first. There are some special cases that may be applicable to some controls: [Order(0)]:[Orders] would be a range of the first item to the 'current' item. In the context of a grid, the 'current' item might be the value in the Orders column that is in the same row as the target cell for which the formula is being evaluated. Similarly, [Orders]:[Orders(*)] can be used to get a range from the current item to the last item. This might be the last order in the current child band island of a grid perhaps.
• Binary operators can be used to separate terms and they are + (add), & (concatenate), -, *, /, and ^ (exponent).
• The prefix unary operators + and - can also be used before an operand.
• The percent operator, %, is a postfix unary operator and results in 1/100^th of the operand's value.
• Relational operators produce a boolean result and are <, >, <=, >=, =, and != (or alternatively <>).

Target Platforms: Windows 10, Windows 8.1, Windows 8, Windows 7, Windows Server 2012, Windows 7, Windows Vista SP1 or later, Windows XP SP3, Windows Server 2008 (Server Core not supported), Windows Server 2008 R2 (Server Core supported with SP1 or later), Windows Server 2003 SP2

Reference

IUltraCalcFormula Interface
IUltraCalcFormula Members