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.


metricFormula: (ACTUAL - BUDGET) / BUDGET


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.  



Sets the fromDate and the toDate.  Date modifiers can be absolute, ie SUBJECT[date=’1 Jan 2011’] or they can be relative, ie SUBJECT[date=’SOFY’]. When using a relative date modifier, the following codes can be used:

SOFY - start of the financial year of the date currently in use

EOFY - end of the financial year of the date currently in use

FD - the first date for which data exists for the subject

LD - the last date for which data exists for the subject

RD - the report date

DATE - the date currently in use

When using a date modifier, you can also specify a relative amount to increase or decrease the date by.  This can be done in months or years.

To add one month to the current date: SUBJECT[date=’DATE+1M’]

To get the end of the last financial year: SUBJECT[date=’EOFY-1Y’]


Sets the fromDate.  The value can be absolute or relative as described for the date modifier.


Sets the toDate.  The value can be absolute or relative as described for the date modifier.


Sets the metric to use for this subject.  Ie, SUBJECT[metric=’NEWMETRIC’].  This is the same as writing SUBJECT[NEWMETRIC]


Sets the subjectId to use for the query.

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.

subjectFormula: CITY

metricFormula: POPULATION - POPULATION[date=’DATE-1M’]

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.



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.

<moxydoc:drilldown report=’MyDrilldown’ fromDate=”{som($date)}” toDate=”{eom($date)}” />

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.

<moxydoc:condition test=”$showSummary = 1” />

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

Click to see Comments

Help comments powered by Disqus