Excel Merge Template Functions

Function Description Syntax Parameters Example
If A conditional statement. When condition is true, the IfTrue expression is evaluated; otherwise, the IfFalse expression is. <#if(Condition; IfTrue; IfFalse)> NA <if(<#value>=1;One;Not One)> writes “One” if the report variable “Value” is 1, and “Not One” if not.
List Returns a string with all values from a table one after the other, separated by a delimiter. If the table has only one record, use <#List(table.field)> to get the value of the only record without having to define any __table__ named range. <#List(dataset name and column)>

or

<#List(dataset name; list separator; agg expression; filter)>

  • Dataset name (and column): Name of the dataset in which the values are returned as a list. Note that this dataset doesn't need to be inside any named range, since all of its records are used anyway. If "agg expression" is present, column name is not needed, as the columns to aggregate are taken from the expression. If not present, include the column in which to aggregate.
  • List separator (optional): If not present, defaults to a single space as the character that separates the elements in the list. To use a semicolon, write it in quotes (";") so it is not considered a parameter separator.
  • Agg expression (optional): An expression that is applied to every record in the dataset (any Excel function is valid here, and any combination of Excel functions can be used). Null values are ignored and not added to the list. If not present, the values of the column specified in "dataset name and column" are used.
  • Filter (optional): If present, it should be an expression that returns true or false. Any Excel formula is valid. Only those records where the filter value is true are used in the aggregate.
  • <#List(Employees.Lastname)> returns a string like "Smith Brown Perez". Because a separator wasn't specified, a single space is used. If Employees has only one record, you can use this to avoid defining an "__employees__" named range.
  • <#list(employees.lastname;, )> returns a string like "Smith, Brown, Perez".
  • <#list(employees;"; "; <#employees.firstname> & " " & <#employees.lastname>)> returns a string like "John Smith; Carl Brown; Jorge Perez". Note that using ";" as a list separator requires writing it inside quotes.
Lookup Searches for a field description on another table. <#lookup(table name; search key names; search key values ;result field)>
  • Table name: Master table that contains the value.
  • Search key names: A list of columns containing the search key on the master table. It will normally be just one column, but if you need to search by more than one, you can separate column names with a comma (“,”).
  • Result field: The field of Table name you want to display.
If you keep a CustomerId on an Orders table and the Customer data on a Customers table, you can use the following to output the real customer name for an order:

<#lookup(Customers;CustomerId;<#Orders.CustomerId>;CustomerName)>

Switch Compares SwitchValue against value1, value2, etc., in order. If SwitchValue is equal to any of the value_n, then result_n is returned. You can provide a default value as the last parameter. If no value matches SwitchValue, and you have a default parameter, then the default is returned. The default is inferred from the number of arguments: An odd number of arguments (3, 5, 7...) don't have a default value. If the number of arguments is even, then the last parameter is the default. <#switch(SwitchValue; value1; result1; value2; result2...[default])> NA
  • <#switch(<#value>;1;One;2;Two)> writes “One” if the report variable “Value” is 1, "Two" if value is 2, and #N/A! if the value isn't 1 or 2.
  • <switch(<#value>;1;One;2;Two;Something else)> writes “One” if the report variable “Value” is 1, "Two" if value is 2, and "Something else" if the value isn't 1 or 2.