Problem

How do I set the value of a column-table using it's "Expression" property.


Solution

The "Expression" property of a column-table allow you to execute "simple" mathematical operations in order to have a computed value of column. Suppose we have the table test.Product with the following columns:

ProdID               
Description
Price
TaxRate
IsTaxEnabled
TaxedPrice

Let's suppose that the column TaxedPrice should contain the price including taxes rounded to 2 decimal places, only if the column IsTaxEnabled is true.
To do this we set the column TaxedPrice to type "Calculated" (we could even set it to decimal type, but this choise causes the physical write of the data and hence the use of more space); then we set the expression property as follow:

iif(IsTaxEnabled=true, round(Price*TaxRate,2), null)

Published in Wed, 05 January 2011 10:50