Calculated Element


The Calculated Element transform lets you create new elements by writing DundasScript expressions. In addition to using built-in operators and methods, you can pass data into a Dundas BI formula function. The result of each expression is a new output element.

Related video: Data Cleansing with Data Cubes

1. Input

The Calculated Element transform requires input data from one transform.

For example, we will use the data cube created in the article Using a data cube to join tables.

Input Data: Calculated Element transform
Input Data: Calculated Element transform

2. Add the transform

To add the transform, select the connection between the second Join and the Process Result.

Select the connection
Select the connection

 Click Insert Common in the toolbar and select Calculated Element.

Add the transform
Add the transform

When you add certain transforms like a Calculated Element transform, an icon with an exclamation mark appears on the top-left of the transform indicating that data will be taken in-memory for processing and could impede performance in some cases.

3. Configure the transform

Double-click the Calculated Element transform to configure it.

In the Calculated Element transform configuration dialog, you can optionally provide a Name and Description for the transform for your reference. The name will be displayed below this transform on the canvas. 

De-select any columns you want to omit from the output.

Click Add Calculated Element.

Configure the Calculated Element transform
Configure the Calculated Element transform

You can add multiple elements using a single transform.

In the Calculated Element dialog, provide a name and optional description for the new element, and set Data Type to the value your script will return. When you choose String as the data type, you can specify a value for Maximum Length. The default value is 200.

When using the Decimal data type, two additional optional fields become available. If set, your values cannot exceed these limits when using cube storage:

  • Precision - Maximum count of digits in each number.
  • Scale - Maximum count of digits after the decimal separator.

Write your DundasScript in the expression field, which must include a return statement. For example:

return $FirstName$ + " " + $LastName$;

Type the dollar sign ('$') to begin entering a placeholder for another column's values, and choose from the popup menu that appears.

Configure the new element
Configure the new element

Click the submit button at the bottom of the dialog when finished.

4. Output

Select the Process Result transform and expand the Data Preview window to see the new columns in the output.

Calculated Element output
Calculated Element output

5. Working with placeholders

The transform automatically defines a placeholder for each input element. The above example makes use of two such placeholders (FirstName and LastName).

To view a list of automatically defined placeholders, expand the Automatic Placeholders section. 

View the list of automatic placeholders
View the list of automatic placeholders

Note the offset value next to each placeholder, which is set to a default of 0. The offset is a number of records to go back or forward when retrieving a value used in the script. You can introduce an offset to the input element used for a placeholder by clicking the Edit icon and changing the value. 

For example, to use the value of the ProductID from the previous row, set the Offset Value for the $ProductID$ placeholder to -1.

The order of some data may change depending on how it is queried based on its use in a metric set. To ensure the sort order is as you expect, you can insert a Sort transform before the Calculated Element.

To use values from a single column/input element multiple times but with different offsets, click Define Placeholders and add additional placeholders. You can add more than one for the same element but with different identifiers and offsets to refer to in your script.

6. Expression samples

The following are some common expressions for calculated elements:

  • Get the year part from a date column:

    DateTime date = $ModifiedDate$;
    return date.Year;
  • Add 3 months to a date column:

    DateTime date = $StartDate$;
    return date.AddMonths(3);
  • Check the Year and return a string:

    DateTime date = $Date$;
    string text;
    if (date.Year == DateTime.Today.Year)
        text = "This Year";  
        text = "Other Year";  
    return text;
  • Get the lowercase equivalent of a string:

    string s = $FirstName$;
    return s.ToLower();
  • Apply title case capitalization to a string:

    string s = $Title$;
    return CultureInfo.CurrentCulture.TextInfo.ToTitleCase(s);
  • Split a string up by commas into multiple values and return the first:

    var values = $CommaSeparated$.Split(',');
    return values[0];
  • Change a UNIX timestamp to the DateTime type:

    DateTime dateTime = new DateTime(1970, 1, 1, 0, 0, 0, 0, DateTimeKind.Utc);
    dateTime = dateTime.AddSeconds($Timestamp$).ToLocalTime();
    return dateTime;
  • Use the median function from Dundas BI formulas:

    return MEDIAN($Sales Amount$);

7. Limitations

  • Column names that contain $ are not allowed to be used with Calculated Elements. You can rename this column to refer to it in script.

8. See also

Dundas Data Visualization, Inc.
400-15 Gervais Drive
Toronto, ON, Canada
M3C 1Y8

North America: 1.800.463.1492
International: 1.416.467.5100

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