Creating computed columns and complex expressions

Using SQL, you can manipulate data to return it in the format that you require. You can create computed columns that return values derived from multiple fields, for example:

QuantityOrdered * PriceEach

ContactFirstName || ' ' || ContactLastName

You can aggregate data using SQL functions, for example:

SUM(OrderAmount)

AVG(OrderTotal)

You can create statements that provide if-then-else logic, for example:

CASE WHEN QuantityInStock > 0 THEN 'In Stock' ELSE 'Out of Stock' END

How to create a computed column

1	Choose Columns.

2	In Column, click in an empty cell. Click the arrow button, scroll down the list of available columns, and choose Build Expression.

3	Click outside the cell to open the expression builder.

4	In Expression Builder, select the type of expression to build and choose Next. Expression Builder displays different properties depending on the expression type.

Figure 2-6 shows an example of a function expression. The expression uses an aggregate function, SUM, to calculate order totals. The expression, SUM(QUANTITYORDERED * PRICEEACH), is created by selecting the SUM function, and the required columns and operator to use in the calculation.

Figure 2-6

Example of a function expression

Choose Finish. The computed column appears under Columns.

5	In Alias, type an alias for the computed column so that it is easily identified.

Copyright Actuate Corporation 2012