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 at least 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 from the toolbar and select Calculated Element.

Add the transform
Add the transform

3. Configure the transform

Double-click the Calculated Element transform to configure it.

In the Calculated Element dialogue, provide a name and description for the transform, and de-select the columns FirstName and LastName to omit them from the output.

Click the plus icon under Calculated Elements to add a calculated element to the transform.

Configure the Calculated Element transform
Configure the Calculated Element transform

You can add multiple elements using a single transform.

On the Calculated Element new element dialog, provide a name and description for the new element, and select String in the Data Type drop-down.

Enter the following script into the expression field:

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

Configure the new element
Configure the new element

Expand the Automatic Placeholders section to view a list of available placeholders.

Click Submit and close the Calculated Element dialogue.

4. Output

Select the Process Result transform and expand the Data Preview panel. The column Full Name has been added to the output.

Calculated Element output
Calculated Element output

5. Expression samples

The following are some common expressions for calculated elements:

  • Get year part from a date column:

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

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

    DateTime date1 = $Date$;
    int i = date1.Year;
    string s = "";
    if (i == 2015)
        s = "This Year";  
        s = "Other Year";  
    return s;
  • Get the lowercase equivalent of a string:

    string s = $FirstName$;
    return s.ToLower();
  • Change a UNIX timestamp to date format:

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

    return MEDIAN($Sales Amount$);

6. Notes

  • When using the Decimal data type, two additional optional fields become available:
    1. Precision - Maximum count of digits in the whole number.
    2. Scale - Maximum count of decimal digits.

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