Spectre Build Calc Object

The Spectre Build Calc object allows you to apply operations to the data before building the cBase. The add and replace operations allow for versatility by using Spectre expressions.

The calc object properties panel has five sections where you set attributes.

You define operations on the data columns in the Operations table. A column count is included in the header.

Operations are how column values are evaluated in a calc object. You can add a new column to your tables by using the add operation functionality. By defining a column name and an expression, you control what values go in a column of your design.

  1. On the Operations pane, click the Add Add Operation icon.

    A new operation row appears.

  2. Click the operation row to edit the Spectre expression for the operation in the Expression pane.

  3. Double-click the Operation name to rename the new column.

The column value is set to the calculated value when the calculation object runs.

There are two ways to insert a replace operation:

  1. Click the Add Replace Operation icon.

    A new operation row appears with the name "ReplacedColumn". Change that name to the name of the column you want to replace.

  2. Double-click the name of the column you want to replace in the Available Columns table.

    A new operation row appears with the name of the column you double-clicked.

You can edit the Spectre expression for the operation in the Expression pane.

The column value is replaced with the calculated value when the calculation object runs.

You can remove unnecessary or unwanted operations with the remove operation functionality. This functionality does not delete columns, it only removes any operations that affect existing columns or delete columns being added through operations.

  1. In the Operations pane, click the row for the operation you want to remove.

  2. Click the Remove Operation icon.

For each operation selected, the Expression Editor allows you to enter Spectre expressions that run when the calculation object runs. The Function List provides a list of all available Spectre functions.

To insert a function into the Expression Editor directly from the Function List, click on the area in the Expression Editor where you want to insert a function, then double-click a function in the Function List. The function populates in the Expression Editor.

TIP: To view parameters and a summary of a function, highlight it in the Expression Editor and details of that function appear in the Documentation tab of the Comments pane.

With a row in the Operations table selected, additional properties can be set in the Operations Properties panel.

NOTE: See Text, cBase, and Dive Input Build Tags to view the full assortment of properties that can be used as tags in a Spectre Build script.

Attribute Description
Duration If set to true, the column must be a numeric column, and it will be marked as a duration column. Duration columns behave the same as normal numeric columns, except that they are meant to be displayed differently. When duration columns are displayed they ignore any format string and display as h:mm:ss (the value is interpreted as seconds).
Format

Sets the display format. Use to format dates, periods, currency, and Boolean expressions.

  1. Select the calc column.

  2. Click the ClosedFormat property and use the pull-down list.

    Spectre Build Calc Format Property

  3. Locate and select the desired format.

See also Spectre Format Conventions.

Label

Defines the display label used for a column.
Required Dimension

Sets a column to behave like a classic Info Field. In ProDiver, the info field is available as a column that you can add to other columns displayed in a dive. For example, to see Address requires that Customer is available.

  1. Select the Info column.
  2. Select Required Dimension in the properties pane.
  3. Use the pulldown to locate and select the core dimension.

    4. The first column is only available when the second column is displayed in the client.

See Defining Info Relationships in Build Code Samples. Also see the separate ProDiver Help.

Sort By

Specifies an alternate sort. When specifying columns, use sort-by to order them based on another column's value.

  1. Select the column which is to have an alternate sort order.
  2. Select Sort By in the Properties pane and use the pull-down to select from the available columns.

    3. The alternate sort is set.

  • An alternate sort can only be applied to a string column, and the sort-by column must be a string or integer column.
  • You cannot add, remove, or change alternate sorts after a cBase is built.
  • Dive and cPlan files also have a sort tag, but for string data, rely on alternate sorts established (if any) when the cBase is built. Dives can also specify sorting on numeric columns.

  • By default Spectre uses "numeric collation" when sorting strings. When a subset of a string looks like a number, then the numeric value is considered during the sort. For example, Spectre sorts "a2" before "a10". See also sort-natural.

  • Null strings sort at the end.

  • If two distinct strings in the column (to be sorted) are set to sort as the same value in the sort-by column, then the normal ordering of those distinct strings will resolve the tie.

  • If a row has the value null or the value unknown in the column with an alternate sort, then the sort-by column must also have the same value.

  • If the sort-by column has type string AND has the value null or the value unknown, then the column to be sorted must have the same value.

  • Spectre issues a warning if you attempt to alternate sort null as non-null, or unknown as not-unknown.
Sort Natural

Disables natural string sorting in text-input. The default is true—Spectre interprets value of digits as decimal numbers and sorts according to the numeric values in the string.

TIP: This feature only makes sense together with sort-by if the sort-by column is also a string column.
Suggested Dimension

Allows you to promote (true) or demote (false) a column.

  • Set to true to promote a numeric column type to become a diveable dimension and have it appear in the ProDiver console.
  • Set to false to demote a column from classification as a dimension so it does not appear in the ProDiver console.
  • When set to false, the new dimension does not appear in the console (which requires dimcount processing), but it can still be promoted to a dimension for diving—equivalent to the concept of a dynamic dimension for a classic model.
  • Using required-dimension on a column effectively sets suggested-dimension=false.
Summary Type

Sets the column's summary type explicitly. Any summary function that takes a single argument (except for count and percentile) are allowed. Most common are sum, info, any, first, last and const. The default for numeric data is sum; use kahan_sum if concerned about precision; use const for a column with only one value. The rest of the functions (for example, min, average, median) are better done as a separate calc (that is, not the default one).

See Summary Functions.

Each object has an area where you can enter comments for documentation purposes. Each row in the Operations table has a separate comment section.

It is a DI best practice to enter a note for every object in a Spectre Build script.