Adding Formulas

Contents[Hide]

1. Overview

In Dundas BI, you apply formulas to data by writing small script-like expressions, much like you would in a desktop spreadsheet program. A formula expression can be a simple constant value, a mathematical expression involving measures, or include built-in formula functions such as AVG and TOP.

Although you can add formulas in the Metric Set Designer, this article focuses on the different ways to apply formulas within the Dashboard Designer.

2. Accessing the formula bar

Select a data visualization on the dashboard canvas, go to the toolbar, click Data Tools, and then click Add Formula.

Or, right-click (or long-tap) the data visualization and click Data Tools and then Add Formula from its context menu.

Add a formula to the selected data visualization
Add a formula to the selected data visualization

A formula bar appears below the main toolbar. The basic idea here is you enter your formula in the text box and then click apply. The output option lets you choose how you want to visualize the formula result/measure. There is also an advanced mode for entering complex, multi-line formula expressions.

Formula bar appears below the main toolbar
Formula bar appears below the main toolbar

Note that you can also add a formula from the Data Binding Panel. Go to the MEASURES section and click the click to add link.

Click to add a formula
Click to add a formula

Then go to the CALCULATED ELEMENTS section and click <Formula>. The formula bar will appear just like before.

Click <Formula>
Click <Formula>

3. Simple formulas

By default, the formula bar starts off in simple editing mode which allows you to enter a one-line expression without any return or if statements.

3.1. Constant value

The simplest formula expression you can have is a constant number value. As an example, consider a bar chart showing OrderQty as a measure by OrderDate.

Open the formula bar for this chart and click the output button. Select the Line chart option and then click Submit.

Choose the type of visualization for the formula results
Choose the type of visualization for the formula results

Notice that the icon for the output button changes to indicate a Line chart visualization.

Output button now indicates a Line chart type
Output button now indicates a Line chart type

Type a constant numeric value in the text box and click apply (or press the ENTER key). The text box is highlighted in blue and a checkmark appears on the right which indicates the formula syntax is correct.

The formula result is calculated and added to the chart as a Line series.

A constant value line series is added to the chart
A constant value line series is added to the chart

Open the Data Binding Panel for the chart to see the formula result has been added as a second measure to the underlying metric set (appears with an fx icon).

Formula result is added as a formula measure
Formula result is added as a formula measure

3.2. Placeholders

A formula expression can include one or more placeholders representing measure values. To insert a measure placeholder, type a dollar sign ($) character in the formula text box. You'll see a menu that lists available placeholders you can insert.

Insert a measure placeholder
Insert a measure placeholder

The inserted placeholder consists of the measure name enclosed between dollar sign characters (e.g., $OrderQty$).

Another way to insert a placeholder is to click a data point on the chart. A placeholder for the corresponding measure will be inserted without having to type anything.

Insert a measure placeholder by clicking on a data point
Insert a measure placeholder by clicking on a data point

3.3. Math expressions

You can use arithmetic operators within a formula expression as well as .NET Math functions such as Math.Floor() and Math.Abs().

Examples:

  • $OrderQty$ * 0.5
  • Math.Abs(-0.75 * $OrderQty$)

Multiply OrderQty by one-half
Multiply OrderQty by one-half

Use a Math method like Math.Abs()
Use a Math method like Math.Abs()

3.4. Functions

Dundas BI includes over 50 built-in formula functions which you can apply to your data. 

Type a letter in the formula text box and you'll see an auto-complete menu showing a list of matching functions you can choose from.

Choose a formula function
Choose a formula function

Once you've chosen a function (or typed it in manually along with the open bracket), a tooltip appears which shows the required and optional arguments for the function.

Tooltip for AVG/Mean function
Tooltip for AVG/Mean function

The first required argument for the AVG function is a measure placeholder which you can insert by typing a dollar sign character or clicking on a data point.

Apply the AVG function to OrderQty
Apply the AVG function to OrderQty

See Connect to OLAP data and apply a formula for a walkthrough on using formula functions with OLAP data.

4. Advanced mode

Click the Advanced button on the right of the formula bar to go into advanced editing mode. This button works like a toggle allowing you to flip between simple and advanced modes.

In advanced mode, you can enter a multi-line formula expression using .NET/C# syntax including if statements. The formula should return a value using the return statement, even if it is just a single line.

For example, a constant formula must be entered as:

return 40000;

In advanced mode, must use return statement
In advanced mode, must use return statement

Multi-line formula using if statement
Multi-line formula using if statement

5. Formula visualizations

Go to the toolbar, click Data Visualization, and then click Formula Visualization under the FORMULA category.

Add a formula visualization
Add a formula visualization

This opens the formula bar just like before except that when you click apply, the formula result is added as a new visualization instead of modifying an existing visualization/metric set. A new metric set is actually created which is sometimes referred to as a formula metric set.

For a walkthrough on adding formula visualizations, see Using a formula visualization and Connect to OLAP data and apply a formula.

6. Editing a formula

Once you've added a formula measure, you can go back and edit it as follows.

Open the Data Binding Panel for the data visualization. Click the edit button of the formula measure. In the Configure Metric Set Element dialog, scroll down and click Formula.

Edit a formula measure
Edit a formula measure

The formula bar opens and the corresponding formula is loaded into the text box.

Existing formula is loaded into the text box
Existing formula is loaded into the text box

You can make changes to the formula directly in the text box and then click apply

7. See also

Dundas Data Visualization, Inc.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours: 7am-6pm, ET, Mon-Fri