Add a calculated column to a table

Open the DWH toolbar tab

Confirm that the intended Data Warehouse (DWH) project is open, then click DWH on the toolbar.

Open the Tables list

On the DWH toolbar tab, click Tables to display the table definitions in the project.

Find and open the intended table

In the Tables list, identify your table using the following displayed columns. If you need to narrow the list, enter its name in Search criteria and click Search.

1. Table Schema — The namespace containing the table. Use it to distinguish same-named tables in different schemas.
2. Table Name — The table's name. Match it to the table containing the inputs for your calculation.
3. Table Type — The table's role, such as Import table. Confirm that it matches the object you intend to change.

Double-click the matching row to open the table editor. The screenshot selects Products in schema IMP.

Open Calculated columns

In the table editor, confirm Table Name, Table Schema, and Table Type, then click Calculated columns. This tab holds the table calculations covered by this how-to. If you need an analytical DAX column, use the separate workflow linked in Applicability.

Enter the expression and calculated-column settings

In an empty row of the Calculated columns grid, enter the new Column name and complete Statement. Review the settings below for that row:

1. Column name — Enter the technical name of the calculated output. Choose a name that identifies the calculation and does not duplicate an existing column in this table.
2. Statement — Enter an expression that produces the calculated value. A Macro is a reusable parameterized statement; its call can be used here when the calculation requires shared logic. Use the saved Macro's name and supply its required arguments in the correct order. For a direct expression, enter the calculation itself.
3. Persisted — Select this checkbox when your physical-table design requires stored computed values. SQL Server maintains those values when their inputs change. Persistence requires a deterministic expression: the same inputs must produce the same result. For a calculation that should remain unstored, leave the checkbox clear. See the SQL Server persistence requirements.
4. PKOrdinalPos — The column's position in the primary key, which identifies rows uniquely. If this column belongs to the intended key, enter its position, such as 1 for the first key column or 2 for the second. SQL Server requires a deterministic expression and an indexable result type for a computed key. Check the planned key against the computed-column index requirements. If it is not part of the key, leave this setting blank.
5. Friendly name / Description — Enter a Friendly name when the column needs a readable label for use in Transformations or generated analytical models. In the calculated-column row's Description, explain the calculation when explanatory metadata is needed. These values can be inherited by child objects; leave them blank when no label or description is required.
6. Referenced column / References — If your design requires a relationship, select the primary-key column in the other table under Referenced column. Check the list of referenced tables under References against that design. If no relationship is required, leave the new row without a reference. These entries describe relationship metadata; saving them is not a check that a physical database constraint exists.

Choose a calculation whose input columns exist in the table you are editing. Enter the indicated Column name and expression in Statement, without a SELECT statement or output alias.

These examples use ordinary non-key calculations: leave PKOrdinalPos blank and do not add a Referenced column. For an unstored calculation, leave Persisted clear; use the existing persistence guidance if your design requires stored values.

Calculate a line value

Multiply a quantity by its unit price to calculate the value of a line.

Required inputs in the same table: Quantity smallint NULL and UnitPrice decimal(10,2) NOT NULL. Use nonnegative values for this example.

Column name: LineValue

Statement:

[Quantity] * [UnitPrice]

A missing quantity produces a missing calculated value. Do not replace it with zero unless that matches the intended business rule.

Apply a discount and set the decimal scale

Calculate a line value after applying a discount, rounding the final amount to two decimal places.

Required inputs in the same table: Quantity smallint NULL, UnitPrice decimal(10,2) NOT NULL and DiscountRate decimal(5,4) NULL. Use nonnegative quantities and prices, and a discount rate from 0 to 1: 0.1000 represents a 10% discount.

Column name: NetLineValue

Statement:

CAST(
    [Quantity] * [UnitPrice] * (1.0000 - [DiscountRate])
    AS decimal(18,2)
)

For the first row, the unrounded calculation is 53.865; converting it to decimal(18,2) produces 53.87. A missing discount remains unknown; it is not treated as a 0% discount.

Return a stock status from a condition

Return a label based on the available quantity and a reorder threshold.

Required inputs in the same table: QuantityAvailable int NULL and ReorderLevel int NOT NULL. Use a nonnegative reorder level. This rule treats zero or negative stock as unavailable and quantities at or below the reorder level as requiring replenishment.

Column name: StockStatus

Statement:

CASE
    WHEN [QuantityAvailable] IS NULL THEN N'Unknown'
    WHEN [QuantityAvailable] <= 0 THEN N'Out of stock'
    WHEN [QuantityAvailable] <= [ReorderLevel] THEN N'Reorder'
    ELSE N'In stock'
END

The first matching condition determines the label. Checking for a missing quantity first keeps unknown stock distinct from zero stock.

Follow the existing save-and-reopen steps for the chosen calculation. The tables above show values expected when SQL Server evaluates the expressions against those inputs; reopening the AnalyticsCreator definition verifies the saved settings.

SQL Server determines a computed column's data type from its expression. Check that your input types produce the intended output type and precision; use an explicit CAST or CONVERT in the expression when needed. See SQL Server computed-column expressions and data types.

Expected Results: the new row contains your intended name, complete expression, persistence choice, and any required key or relationship settings. Review the entire expression before saving.

Save the table definition

Check the new row against your intended calculation, then click Save at the bottom of the table editor. If a message prevents saving, resolve the reported problem before continuing.

Reopen and check the saved calculation

Return to DWH → Tables, find the same table by its schema and name, and double-click it. Click Calculated columns and compare the saved row with the definition you entered in Enter the expression and calculated-column settings.

Check the complete Statement, not only the column name. Confirm Persisted, PKOrdinalPos, and any friendly name, description, or relationship entries. If a value differs, correct it, click Save, and repeat this check.

If you need another calculated column, repeat Enter the expression and calculated-column settings–Reopen and check the saved calculation for a new row. Close the table editor when all required definitions have been saved and checked.