Calculated Element

Contents[Hide]

The Calculated Element transform is created when a structure is dragged onto the canvas from a data connector that supports relational queries. The columns to be used in the data cube can then be selected or unselected and aggregators can be set up per element. When retrieving data, these settings will then be translated into a TSQL statement and sent to the data source.

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

Related video: Data Cleansing with Data Cubes

1. Input

The Calculated Element transform requires 1 input transform that has at least 1 column.

The input could be a SQL Select transform, or the result of another transform. For example, we'll use the output of an Aggregate transform, as seen below:

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

2. Configure

Steps to configure the Calculated Element transform:

    1. Click the Add Expression icon.

      Calculated Element transform configuration - Step 1
      Calculated Element transform configuration - Step 1

      You can add multiple expressions as needed.

    2. On the Calculated Element Expression dialog, enter the following:
      • Name - Name of the Calculated Element Expression (required field)
      • Description - Description of the Calculated Element Expression (optional field)
      • Data Type - Data Type of the output column (required field)

      Calculated Element transform configuration - Step 2
      Calculated Element transform configuration - Step 2

    3. Type in the expression on the Expression box, or click on Edit expression in editor to open the Script Editor

      Calculated Element transform configuration - Step 3
      Calculated Element transform configuration - Step 3

    4. On the Script Editor, enter the expression, and click Save. For example:

      Calculated Element transform configuration - Step 4
      Calculated Element transform configuration - Step 4

If you use the 'Expression box' to type in your calculated expression, you can simply type in '$' and the columns will be available for selection from the popup.

Type 'return $' and you'll get the list of columns.
Type 'return $' and you'll get the list of columns.

3. Output

The figure below illustrates the output from the Calculated Element transform.

Calculated Element - Sample Output
Calculated Element - Sample Output

4. Other Calculated Element Script Samples

  1. Get year part from a hard-coded date:

    DateTime date1 = new DateTime(2008, 1, 1, 6, 32, 0);
    return date1.Year;
    
  2. Get year part from a date column:

    DateTime date1 = $ModifiedDate$;
    return date1.Year;
    
  3. Concatenate 2 string columns:

    return $colA$ + $colB$;
    
  4. Add 3 months to a date column:

    DateTime date1 = $StartDate$;
    return date1.AddMonths(3);
    
  5. 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;
    
  6. Get the lowercase equivalent of a string:

    string s = $FirstName$;
    return s.ToLower();
    
  7. 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;
    

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