| 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.
|