Using the formula builder

Last updated:
November 26, 2023

Accessing the formula builder

The Formula Builder is available from several places in Spotlight Sustain:

  • From within the Formula Gallery
  • When working on a standard or custom chart
  • When creating a KPI

Creating your formula

Note: When you create a new formula from within a chart, a default acount appears as a placeholder. If you don't want to use this, to remove it simply click and drag it into the removal area below the edit box.

  1. To add a new account to the formula, click Add Account.
  1. You can now choose whether you want to select a single account or multiple accounts (you can add more accounts later).

    Select an individual account - Use this option to add a single account to the formula.
    Select a group of accounts using a report code - Use this option to select all accounts which share a specific report code.
    Select a group of accounts using the display name - Use this option to select all accounts which share the same display name in the Add Data tab.

    Select the option you want, then click Next.
  2. Choose the account, group of accounts or display name you need, then click Next.
  3. Select the metric you want to use, then click Next.  The following options are available:

    Actual - accrual data
    Target - target data

  4. Select the date option you want to use, then click Save.  The following options are available:

    Standard dates - Displays data based on the X axis date range. For most charts, this will show monthly values for the current financial year.
    Prior period - Displays data for -n prior periods based on X axis date. E.g. Entering '-12' will show the data 12 months ago (last year). Use this option for displaying prior year data.
    Cumulative results - Show a cumulative total, either from the beginning of the financial year or from a set number of months ago.

  5. If you're creating a formula using more than one report code, you can use the symbols and add further accounts. If required, to rearrange a symbol or account you can click and drag it within a formula, or drag it to the removal box to remove it completely.  The formula works on a BEDMAS basis - please use the brackets to ensure your formulas are correct.

  1. Once you're happy with your formula, click Save. This updates the chart with the data you want to show.  Alternatively, if you'd like to save your formula for use in other charts, click Save As.  For further information about this option, please refer to the next section, Add your formula to the Formula Gallery.
  2. You can show a maximum of five different series on custom charts. To add another series, open the drop-down for the series you want, and repeat the process to build your next formula. Once you've built your additional formula, click Show series and select the Show check box.

Add your formula to the Formula Gallery

When you create a formula from within a chart or the Formula Gallery, if you want to use it again in future you can save it to the Formula Gallery.  You can choose to make your new formula available to all organisations or only a single one, and to use your own formula instead of a default one.

  1. Use the formula builder to create the required formula, then click Save As.
  2. From the Available to drop-down list, choose Organisation.  If you choose Practice, the formula is available to the whole practice.
  3. Enter a name for your formula.  

    Note: If you enter a name already in use for a default formula, your own formula takes precedence in this organisation. If you enter a name already in use for another custom formula, the existing one is replaced.
  4. If required, enter formula tags.
  5. To save the formula, click Save.

Your formula is now available to use in charts, text panels, and the Executive Summary page.

Spotlight Sustain