Skip to main content

Calculated Column Usage Examples

  • Updated

Calculated Column Usage Examples

Here are some examples of the Calculated Column element in use.
1. The following shows a simple implementation - calculating sales tax:

In the definition shown above, a data table and a datalayer element have been added. A Calculated Column element has been added beneath the datalayer and its attributes have been configured so that it will multiply the values in the ProductSales column by a constant to produce the sales tax amount.

The resulting sales tax amount will be placed in a new column, named calcTax, in every row of the datalayer, as shown above. The data will be accessible as the token @Data.calcTax~. Instead of hard-coding the sales tax rate in the formula, it could be made dynamic using a token, like this:

    @Data.ProductSales~ * @Request.TaxRate~

Here's a slightly different example: the following expression uses the intrinsic IIF function to apply different tax rates, depending on other criteria:

    IIF(@Data.CategoryID~ = 4, @Data.ProductSales~ * @Request.TaxRate1~, @Data.ProductSales~ * @Request.TaxRate2~)

Of course, your expression doesn't have to use data from the datalayer at all. The following example simply creates a new column and puts the elapsed seconds for every row for it:

    DateDiff("s", CXMLDate("@Request.StartTime~"), CXMLDate("@Function.Time~"))

Note the use of the intrinsic CXMLDate function, which is necessary to convert ISO formatted XML DateTime data into a format that works with the DateDiff function (and should be used with DateAdd, DatePart, and other date functions). The possibilities are endless and allow developers a great deal of flexibility in manipulating the datalayer.

2. This example illustrates how the value created by one Calculated Column can be used in another Calculated Column.

In the definition shown above, a second Calculated Column has been added beneath the datalayer. As you can see, its Formula attribute uses data from one of the retrieved data columns and the first calculated column.

The resulting total amount will be placed in a new column, named calcTotal, in every row of the datalayer, as shown above. The data will be accessible as the token @Data.calcTotal~.

Related articles