Calculated Element

Contents[Hide]

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 Add Calculated Element.

Configure the Calculated Element transform
Configure the Calculated Element transform

Note
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 in the expression field:

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

Configure the new element
Configure the new element

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. 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
When you use them in an expression, the placeholders move from the list of automatic placeholders to the list of defined 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 data. You can introduce an offset to the input element used for a placeholder by clicking Edit and changing the value. 

For example, to use the value of the ProductID from the previous row, simply add a -1 Offset Value to the $ProductID$ placeholder and use this placeholder in the expression.

6. Expression samples

The following are some common expressions for calculated elements:

  • Get the 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";  
    }
    else
    {
        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$);

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

8. Limitations

  • Column names that contain $ are not allowed to be used with Calculated Elements. It is suggested instead to rename the column.

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