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

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

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

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