Whenever data is used in a report or a chart, an excel like formula can be used.  

When specifying data for a grid or chart, the subjectCode and metric are defined as two separate formulas.  This allows rows to define subjectCodes and columns to define the metric.

Subject and Metric Formulas

A subject formula defines just the subjects that will be used to retrieve a value while a metric formula defines just the metrics that will be used.  Normally, the metric formulas will take precedence over the subject however normal order of operations is maintained over both formulas.

Example 1

A simple subject formula and metric formula to calculate the amount owing on an invoice.

subjectFormula: INVOICE

metricFormula: TOTAL - AMOUNTPAID

Example 2

A combination of two formulas.

subjectFormula: REVENUE - (EXPENSES - INCOMETAX)

metricFormula: (ACTUAL - BUDGET) / BUDGET

Modifiers

When a formula is run, it will be constrained to running over a date range that is defined by the column or chart is in contained within and by filters based on any repeats that the formulas is contained within.  Modifiers allow a formula to change these constraints.  

Modifiers

Example 1

In this example, we assume the column defines a date range of a single month.  However, we want to display a column that shows the difference between that month and the previous month. We will use a modifier to get the value of the previous month.

Absolute Formulas

An absolute formula is used when the subject and metric formulas are not separate.  In this case, the formulas must specify both the subject and metric to use.  This is done by defining the metric in a modifier.

Example 1

This example shows how to display the NAME metric for the subjectCode CONTACT.

Variables

Formulas can refer to variables using a $ before the variable name.  Report options are available as variables and there are other variables available in specific circumstances, such as within conditions.

Example 1

This example assumes a report option with id “date” and shows how to define a drilldown that would pass through a fromDate and a toDate, where the from date is the start of the month that “date” falls into and toDate is the end of the month that “date” falls into.

Example 2

This example assumes a report option with id “showSummary” which is a checkbox and shows how to define a page condition that would only display the page if the checkbox was checked.

Using Formulas

When formulas are used in an attribute that expects a formula, then the formula is just written into the attribute.  For example, in the row attribute data=”SUBJECT1 + SUBJECT2”.  However, formulas can also be used in any attribute, or text by enclosing it in curly brackets {}.  For example, in the title attribute title=”{$customer} Invoices”.