Equation Editor Reference
Fluent Designer has built-in functions for the Tags in your templates that can be used to construct equations or formulas. These built-in functions are different than the functions and macros native to MS Office, in that the Fluent built-in functions are implemented in Fluent products rather than MS Office.
Fluent products support equations and functions for Out Tags in Microsoft Office Word, Excel, and PowerPoint templates. This allows you more options in manipulating your data directly in the Fluent Designer and Engines.
For example, you can:
- Work with more complex mathematical functions, like taking the the square root of a number
- Add or subtract from a date to adjust for time zones
- Easily add a time stamp that is set when the report is generated
- Have your equation evaluated as you type
- Import custom functions that will appear in the Fluent Designer interface
About Equations
An equation is composed of one or more functions, and returns a result. There are different kinds of functions that return different types of results, such as: arithmetic functions that return numbers; datetime functions that return dates; and logical functions that return the Boolean values true or false.
An equation always begins with "=" in the Query Pane of a Tag.
Here are some examples of simple equations:
- An arithmetic equation that returns 5 when evaluated: =2 + 3
- A logical equation that returns true since the comparison is case sensitive: ='A' != 'a'
Some Common Operators and Operands
You can write equations using common operators and operands. In the equation "=2 + 3", "+" is the operator, while "2" and "3" are the operands.
Operands can be constants, as shown above, or Fluent variables, which are created by some Tags and Input Parameters. Fluent variables are referenced by surrounding their names with "${" and "}". For example, a ForEach Tag variable named "Total" in the var property of the Tag would be referenced in equations as "${Total}". Before the equation containing "${Total}" is evaluated, the value of "${Total}" is substituted for its name, then the equation's result is returned.
Equations in Word and PowerPoint
Keep in mind that Microsoft Word and PowerPoint do not have the ability to use equations and functions within the program natively (only Excel has its own set of equations and functions). Microsoft Word and PowerPoint do have an Equation button in the Symbols section of the Insert tab. However, this just gives the ability to write equations that appear in multi-line formats so they display cleanly in the document. These equations do not evaluate or produce a result.
This is why Fluent built a library of equations to use in Tags. Often an equation is created in an Out Tag and displays the evaluated result when output is generated from the report template. Also, Fluent gives you the ability to program custom functions to use in Fluent tags
Equations in Excel
Microsoft Excel works differently. In addition to using Fluent's equations and functions within Tags in Excel, you can also use some Excel native functions and equations with Fluent variable references. However, this special feature doesn't support every function provided by Excel.
An example of this is when you use the native Excel function SUM() to point to a cell that contains an Out Tag. Let's say you have an Out Tag in a ForEach Tag loop in cell B1. That Out Tag returns a result of a subtotal each time the ForEach Tag loop runs. If the loop runs five times, then Fluent products (because updating the Excel SUM() function is supported) will produce output that updates the original native Excel function SUM(B1) to SUM(B1:B6). This is because the cell referred to in the SUM() function increased from a single cell to a range of five cells B1 to B6. The result is that since the SUM() function was updated it will display the proper sum of all five subtotals generated from the Out Tags.
Any Excel function or formula in a Tag with a reference(s) to a cell(s) in a ForEach Tag loop (as in the example above) is supported. Cell references outside a ForEach Tag loop will not have their formulas or functions updated with values and/or cell ranges by Fluent.
If there is a function or equation you need for your report calculations that is not provided by either Excel or Fluent, then you can create your own custom function within Fluent (How Do I Create a Custom Fluent Function for the .NET Engine?).
Using a Data Set in an Equation
If you want to include a data set returned by a SQL, XPath or JsonPath select statement into an equation, you must use the DATA() function. Pass the select statement, enclosed in double quotation marks, as the argument to the DATA() function, and use that in the equation, e.g.
=DATA("select some\_value from some\_table") = 5
This equation executes the select statement which returns some value and that value is used in the comparison.
This is important to remember when working with functions that have a SQL or XPath equivalent, like SUM(). =SUM(data("<SQL or XPath select statement>")) is a Fluent equation, because it begins with "=". It therefore requires use of the DATA("select") wrapper for your select. The statements SUM(<xpath expression>) and SELECT SUM(<column>) FROM <table> use the built-in XPath and SQL SUM() functions, respectively, and are not Fluent equations. This means they are treated as select statements, not as equations.
Equation Editor Interface
There are two ways to bring up the Equation Editor interface:
- Double-click on a Tag to bring up the Tag Editor, then click on the "Equation" button
- Select a Tag, then click on the "Equation" button on the AutoTag Manager ribbon
Insert Function Window
After clicking on the "Equation" button, the Insert Function window appears:
Functions Arguments Window
After selecting a function and clicking on "Ok", the Function Arguments window appears.
Use the Function Arguments window to enter values for the arguments to the function you selected. The Function Arguments window includes:
- The equation you've constructed so far
- A field(s) for entering one or more argument values, as applicable
- When a data set returned by a select statement can be used as an argument value, a Select Wizard button will appear
- A description of the function
- A description of the function's values
- The result of the equation (which is updated in real time)
