Mapping JavaScript to Excel

Interactive Reporting uses a translator to distinguish between the range types used to substitute a column reference in a computed item expression. There are three types of ranges:

Continuous range corresponding to the whole column (for example, Units)
Aggregate range – this is a continuous range that is broken by Break Totals (for example Units_Agg). The only purpose of this range is as a data source for aggregate functions. The translator uses Units in all kind of expressions that refer to the column, but it uses Units_Agg range for aggregate functions, for example, SUM(Units_Agg).
Continuous sub-range used in break total computation.
Individual cell references (for example, A1, B5)

The table below outlines how the JavaScript expression operations are mapped to Excel.

Table 2. JavaScript to Excel Mappings

JavaScript	MS Excel
*, /, <=, =>, -, + (arithmetical plus operator)	Uses the same symbols.
%	MOD() built-in function
!=	<>
==	=
+ (concatenation operator)	&
Exp1? Exp2: Exp3 (ternary operator)	IF(Exp1, Exp2, Exp3)
Comma operator (e.g. exp1, exp2, …, expN)	Replaced with the last expression. For example, (exp1, exp2, …, expN) -> is replaced with expN in resulting Excel formulas. Expressions should be of the same type (expression or string)
!	NOT() built-in function
&&	AND() built-in function
\|\|	OR() built-in function
Character literal (e.g. ‘a’)	Converts to string literal (for example, “a”)
Hexadecimal and octal number (e.g. 0x10, 010)	Converts to a decimal number (for example, 16, 8)
Logical constants (true and false)	Converts to Excel’s TRUE and FALSE
Expressions with ‘null’	Expressions with ‘null’ have limited support. Column values can be compared with null. Computed Items should not produce null values.
\n\t\r\a\b and \xHH in string literals. Example: “a\na”	Replaced with CHAR(x) in the output string.For example: “a” & CHAR(10) & “a”

The JavaScript’ ‘+’ operator can be applied to strings and denotes a string concatenation. Excel’s string concatenation operator is ‘&’. Interactive Reporting distinguishes between arithmetic ‘+’ and string ‘+’ by tracking the type of arguments in expressions. If one of the arguments is a string literal, a concatenation operator is used. The translator tracks the return type of Object Model functions and columns, so it can infer a final expression type. There are three supported types: numeric, string, and date.