Equation Editor Reference

Report Designer Online Edition (the 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 Windward built-in functions are implemented in Windward products rather than MS Office.

Windward 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 Designer and Report 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 Report 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 Windward variables, which are created by some Tags and [Input Parameters]. Windward 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 Windward 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, Windward gives you the ability to program custom functions to use in Windward tags

Equations in Excel

Microsoft Excel works differently. In addition to using Windward's equations and functions within Tags in Excel, you can also use some Excel native functions and equations with Windward 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 Windward 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 Windward.

If there is a function or equation you need for your report calculations that is not provided by either Excel or Windward, then you can create your own custom function within Windward (How Do I Create a Custom Windward 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 Windward 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 Windward 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:

  1. The equation you've constructed so far
  2. A field(s) for entering one or more argument values, as applicable
  3. When a data set returned by a select statement can be used as an argument value, a Select Wizard button will appear
  4. A description of the function
  5. A description of the function's values
  6. The result of the equation (which is updated in real time)