cPlan Tags

A Spectre cPlan script is defined using a cplan tag with optional tags between open and close { } braces. The tags are listed in alphabetical order, after the cplan tag, in the cPlan Tags and Loop Tags tables.

cPlan Tags

Tags Examples Notes
cplan

cplan {

...

}

cplan "/cbase/sales.cbase" {

...

}

Defines the block for the cPlan definition. The cPlan name is the same as the filename of the cPlan script.

Optionally, the path to the cPlan can be named before adding more tags. If path is provided, no other input tags are allowed. The cPlan file pointed to by "path" is imported before anything else in this cplan block is considered.

Parameters can be declared at this level. See the take-parameter tag.

anchor anchor `top(max(value("Order Date")))`

Defines the anchor date in a time-series block.

See Time Series Tags

breaks

breaks {

column "Mood"

column "Color"

first "First"

last "Last"

index "Index"

level "Level"

}

Identifies break levels in the input flow. A break level is a set of rows that have identical values for a set of break columns. The Break object creates up to four new columns that describe the position of each row in the break level. This action allows subsequent calculations to process a row of a break column knowing whether it is the first or last row of the break level.

In the example, the output cBase has two columns, Mood and Color, and four columns, First, Last, Index, and Level. The following table contains sample data:

Mood Color
Happy Yellow
Happy Yellow
Sad Blue
Sad Blue
Sad Blue

When the breaks tag processes this table, the results are as follows:

Mood Color First Last Index Level
Happy Yellow 1 0 1 0
Happy Yellow 0 1 2 2
Sad Blue 1 0 1 1
Sad Blue 0 0 2 2
Sad Blue 0 1 3 2
calc

calc "Half of Sales" `sum(value("Sales"))/2`

calc "Average Cases" `average(value("Cases"))`

calc "Profit" format="$#,###;-$#,###"

calc "Item Count" `dimcount(value("Item"))`

calc "Accounts with Late Charges" `filter(dimcount("Account ID"), sum(value("Late Charge Amount")) > 0)`

calc "Denial Rate All" `calc("All Denials")*100/calc("All CPTs")`

calc "Most Recent City" `filter(info(value("City")), value("Period") = max(value("Period")) and value("City") != null)`

calc "YearMoCalc" `any(period("standard", "year-month", value("Date") ))` format="YY MMM"

calc "Quarter" `period("standard", "year-quarter", value("Date"))`

Performs a computation on the data supplied by the data sources. In its simplest form, a calc is a name and an expression.

See About Spectre Expressions.

NOTES:

  • A calc is a declaration as to how to calculate something. It is not an instruction to perform the calculation.
  • Once defined, a calc can be referenced by name elsewhere in the cPlan.
  • When defining a calc, you can assign it to one or more calc-sets.
  • When the calc is used as a column, you can use filter, label, format, duration, property, and custom-property tags.
  • You can add properties to an automatically generated calc by using its name with no definition, followed by the properties.
  • Each calc name must be unique.
  • cPlans support millions of calculations.
calc-set

cplan {

input "/common/basic.cbase"

for "Color" "Red" "Green" "Blue" {

calc "$(Color) Weight" `calc("Weight")` filter=`value("Color") = param("Color")`

calc-set "Weights"

}

}

Defines a set of calculations to reference by name. In this example, the calc-set Weights has three columns: Red Weight, Green Weight, and Blue Weight.

Calc sets are used in Dives or times series to reference more columns.

The columns in a time-series block can also be made into a calc-set.

See cPlan Code Samples.

calendar

calendar "modified-standard" type="standard" {

period "year-month" format="YYYY-MMM"

}

calendar "fiscal" type="custom" {

cbase-input "fiscal.cbase"

}

calendar "fiscal" file="fiscal.calendar"

Adds a calendar for the cPlan.

The first example declaration reformats the standard display from YYYY/MM to YYYY-MMM.

The second example creates a fiscal calendar from the indicated cBase.

The last example builds the fiscal calendar from the named calendar text file.

See Calendar Declarations.

cbase-input

cplan {

cbase-input "sales.cbase"

}

 

cplan {

cbase-input "jan.cbase" "feb.cbase"

}

 

cplan {

multilevel {

cbase-input "abc.cbase"

cbase-input "def.cbase"

...

}

}

Indicates the cBase file to use as a data source.

In this example, rows from sales.cbase are used as the data source, and automatic summaries are generated to represent the columns. Automatic summaries generate add operations for numeric columns and info operations for string columns.

The second example has two input cBase files that have the same structure.

The third example merges two different inputs together. Dives that use this cPlan work on both inputs simultaneously, and can mix dimensions and summaries from both sides into one window. See Multilevel cPlans.

Also, see the cplan-input, dive-input, odbc-input, and text-input tags for other input types.

column column "Column1" Option for text-input or a lookup operation.
consolidate

cplan {

consolidate {

cbase-input "sales.cbase"

cbase-input "deals.cbase"

}

}

Consolidates two cBases on the fly.

NOTE: In general, run-time consolidation of cBases is not recommended—instead, build a larger cBase or build more frequently. One clear exception is when using odbc-input for dynamic content.

copy-properties-from

calc "units_sold" `sum(value("Units"))` format="#,#"

calc "max_units" `max(value("Units"))` {

copy-properties-from "units_sold"

}

Copies properties from one calc to another.
cplan-input

cplan {

cplan-input "nydata.cplan"

}

 

cplan {

cplan-input "sales.cplan" {

pass-parameter "region" "south"

}

}

Imports another cPlan.

The outer cPlan is responsible for passing any parameters to the inner cPlan. See the pass-parameter tag.

Also, see the cbase-input, dive-input, odbc-input, and text-input tags for other input types.

NOTE: Input operations such as add, remove, and filter in cplan-input are deprecated. Operations should be in the cplan tag instead (or in the inputs of the inner cPlan such as cbase-input).

current date property "Current Date" "2021/02/25"

Defines the system property Current Date. The string value must use the format "YYYY/MM/DD".

custom-property

custom-property "Abbreviation" "Dir"

custom-property "Good Direction" "U"

Refers to qualities of a calc or column that are not directly used by the system, but might be set by the user for reference and other purposes. Any property name can be used as a custom property.

See also Spectre Properties.

datagen-input

datagen-input count=100 {

column "ID" {

sequence start=10 end=25

}

column "Name" {

name

}

}

Generates random data in specified columns. For a list of all attributes, see Spectre Datagen Input Properties.
date dimension property "Date Dimension" "Dat" Defines a system property for the cPlan where Date is the name of a column.

default-calendar

default-calendar "offset november"

default-calendar "modified-standard"

Specifies a default calendar. The default calendar can be set to standard, offset <month>, iso 8601 or any declared calendar, including custom calendars. The default calendar is standard unless declared otherwise.
delimiter   Option for text-input useful for csv files.
description property description "YTD cPlan"

Includes text information in the cPlan.

dimension

dimension "CustomerPhone" {

property "Label" "Customer Phone"

}

 

dimension "Order Month" `period("gregorian month", value("Order Date"))`

 

dimension "Age" {

group "0-17" `value("Age")<18`

group "18-44" `value("Age")>=18 and value("Age")<45`

...

}

 

dimension "YearMo2" `period("gregorian month", value("Date") )` format="YY MMM"

 

dimension "Bucket" `integer(value("Qty")/100)`

Adds properties to columns from data sources. When those columns are used as dimensions, the properties set with the dimension tag are part of the column properties. Use the dimension tag to change a label or format or to add named groups.

If the dimension tag includes an expression, the name must be a new dimension name. These expression dimensions can include the suggested-dimension and sort-by tags. These match the attributes of the same names on the column and add tags in a Build file.

Dimensions defined in cPlans using expressions are generated at dive time and can be used in a Dive window and in QuickViews.

For more info, see What is an Expression Dimension?

dive-input

dive-input {

dive {

cplan {

text-input "sales.txt"

}

window {

dimension "product"

column "Total Revenue"

}

}

}

Performs a dive and uses the result as the input. This is equivalent to the Integrator squash operation, creating a single record for a series of dimensions.
drop-column

input "/common/demo_drl.cbase" {

drop-column "Revenue"

}

Drops the named column from the input.

duration

calc "Avg ED LOS" `average(value("ED LOS"))` duration=true

Indicates the numeric value is meant to be displayed as a time duration. The format is h:mm:ss.

Use with dimension or calc tags.

excel-input

excel-input "/data/sales.xlsx" sheet="Region1" range="A1:D25" headers=true {

...

}

Specifies an xlsx file to use as input, along with options for interpreting that data in the file. The following attributes are supported for use:

  • sheet—Specifies which sheet in the Excel file to pull data from. The value can be either a name or a number. Spectre checks for a name match first. If that fails, it tries treating sheet as a number. Name matching ignores case, and if multiple sheets have the same name it only looks at the first one that matches. If this value is empty, the first worksheet in the Excel file is used.

  • range—Specifies a range of cells to use from the input. Any cells outside of the range are ignored. The format for the range is the top-left cell (column letter and row number), followed by a colon, followed by the bottom-left cell (column letter and row number). For example:

    A1:D25 returns the 25 rows from the first column (column A) through the fourth column (column D)

  • limit-rows—Directs the build to use a specific number of rows from the Excel file.

  • headers—Indicates the presence or absence of column headings in the Excel file. Set to false to name each column manually.

  • start-row—Defines the row number to begin reading data from. If this value is empty, Spectre attempts to automatically determine which row represents the start.

    NOTE: If the headers value is set to true, the value of start-row should be the row number the header is on.

  • password—Provides a password if the Excel file is password-protected. It is not recommended to use password-protected Excel files as data sources.

  • trim—Removes any leading or trailing spaces from the data. This is the default behavior for Spectre. Set to false to disable the trimming of white space.

expand

expand {

start "Start Month"

end "End Month"

output "Project Month"

}

Expands one row into multiple rows, using an inclusive range defined by two columns. In the example, two columns containing months (Start Month and End Month) are defined as the inclusive range for expansion. Given a table such as the following:

Project Start Month End Month Employees
Project A 2020/06 2020/08 2
Project B 2021/01 2021/03 4

The expand tag processes and builds out one row for each month in between Start Month and End Month and consolidates the two input columns as the output column Project Month. Note that other columns (Project and Employees in this example) fill the newly expanded rows with identical values.

Project Project Month Employees
Project A 2020/06 2
Project A 2020/07 2
Project A 2020/08 2
Project B 2021/01 4
Project B 2021/02 4
Project B 2021/03 4

NOTES:

  • The expand tag supports integer-backed data types (integer and fixed100) and date types. Fixed100 columns iterate by .01 each row.

  • Datetime columns are not supported.

  • Rows where the start value is greater than the end value are excluded from the output, as are null and unknown values.

  • Period data types must have the same calendar to properly expand.

filter

cplan {

input "/common/demo_drl.cbase" {

filter `is_in(value("Sales Region"), "Boston", "Southwest", "West")`

}

}

cplan {

input "/common/basic.cbase"

calc "Red Weight" `calc("Weight")` filter `value("Color") = "Red"`

}

cplan {

input "/cbases/denial_rates_canister.cbase" name="subset_denial_counts" {

filter `param_match("Location")`

filter `param_match("Rendering Provider")`

rename "Denial Count" "Filtered Denials"

}

}

Discards rows of the input that do not match a specified expression. This is an optional input operation that is run on each row of the input.

NOTE: Multiple filters are combined with the AND operation. Use a single filter if AND/OR logic is required. For example: `filter `value("Make")="Jeep" or value("Group")="BMW Group"`.

A filter can be part of a calc tag.

A filter can use parameters.

A filter can be part of an input block, or used elsewhere in the cPlan.

TIP: The filter expression for a cPlan is the one system property for calcs that is not available for columns.

for

for "Color" "Red" "Green" "Blue" {

calc "$(Color) Weight" `calc("Weight")` filter=`value("Color") = param("Color")`

calc-set "Weights"

}

Loops through the values for the designated dimension while evaluating the calc. In this example, three new columns are calculated: Red Weight, Green Weight, and Blue Weight.

See the Loop Tags table.

format

calc "Variable Cost per Case" `calc("Variable Costs")/calc("Cases")` format="$#,#"

calc "Total Accounts" `dimcount(value("Account Number"))` format="#,###"

calc "Opportunity Cost" `calc("GM ALOS") - calc("ALOS")` format="#.##"

dimension "Datetime" label="Formatted" format="MMM D, YYYY ii:mm pp"

Option for text-input. Formats the result of the expression.

See also:

group

cplan {

input "/common/basic.cbase"

dimension "Direction" {

group "Northeast" "North" "East"

}

}

cplan {

input "/common/basic.cbase"

dimension "Mood" {

group "Ends with y" `rsubstr(value("Mood"), 1, 1) = "y"`

}

}

cplan {

...

dimension "ExprDim" `"X" + value("Dim")` {

group "Group" "XA" "XB"

}

}

filter `group("ExprDim", "Group")`

Defines multiple dimension values as a group by giving them a name.

  • The group can be used in a dive. See Dive Code Samples.
  • The group can be defined using an expression or parameters.
  • The group can be defined for a set of expression dimensions.
  • The group can be defined using a wildcard character against the dimension value such that new values will automatically be included in the group.
groups-from-file

cplan {

...

dimension "Dates" {

groups-from-file "./dates-group.dat"

}

}

Defines multiple dimension values as a group using a pre-defined group file.

  • The group file must be tab-separated with headings.

  • Every column must have a non-empty heading.

  • The heading of the first column is ignored. Each remaining column heading is a group name.

  • An empty string, denoted as "", indicates non-membership in a group.

  • For more information about named group files, see About Named Group Lookup Files.

headers   Option for text-input.
import-calcs

cplan {

input "/data/sales.cbase"

import-calcs "/data/mthly-calcs.txt"

}

Imports externally defined calculations from a tab-delimited text file.

See Parameter Examples.

NOTES:

  • The first line of the file must indicate the name of each column.
  • The file must also contain columns named Name and Definition.
  • Additional columns, if any, are considered properties.
  • Columns named Format and Label are automatically treated as the appropriate system properties.
  • Any other names are treated as user properties.
include-other-columns   Option for text-input.

input

(Deprecated)

cplan {

input "sales.cbase"

}

 

cplan {

input "/cplans/basic.cbase" name="basic"

input "/cplans/supplemetary.cbase" name="sup"

}

Provides the path to a cBase file to be used as a data source.

IMPORTANT: This syntax is deprecated—use cbase-input instead.

The first example indicates that rows from sales.cbase are used as a data source, and that automatic summaries are generated to represent the columns. Automatic summaries generate add operations for numeric columns and info operations for string columns.

The second example merges two inputs together.

IMPORTANT: This syntax is deprecated—use the multilevel tag instead.

Dives that use this cPlan work on both inputs simultaneously and can mix dimensions and summaries from both sides into one window. See Multilevel cPlans.

Also, see the cplan-input, dive-input, odbc-input, and text-input tags for other input types.

input-format column "Datetime" type="datetime" input-format="YYYY/MM/DD hh:mm:ss"

Provides a string that indicates the data format.

See also Text, cBase, and Dive Input Build Tags.

include-other-left-columns

build {

text-input "my-input.txt"

join "right outer" {

text-input "your-input.txt"

key "A" "A" "A"

include-other-left-columns suggested-dimension=false

}

output "my-output.cbase"

}

Specifies that all columns from the left flow of a join are to be included in the output flow. Key columns are not included. Similar to a column tag, include-other-left-columns has an optional suggested-dimension tag. The include-other-left-columns tag can be used with include-other-right-columns.
include-other-right-columns include-other-right-columns Specifies that all columns from the right flow of a join are to be included in the output flow. Same as include-other-left-columns but for the right flow.
join

cplan {

cbase-input "klant.cbase" {

join "left outer" {

cbase-input "order.cbase"

key "KlantID" "KlantID"

//ordercbase columns

column "ProductID"

column "Bedrag"

column "OrderID"

//klantcbase columns

column "klantgetal1"

column "KlantType"

column "KlantNaam"

}

}

}

Joins two input flows. Join type can be:

  • inner—Only rows that have matching keys are returned.
  • left outer—All rows from the left flow are returned.
  • outer—All rows are returned in the output flow.
  • right outer—All rows from the right flow are returned.

Note that the join is within the cbase-input block. A join must always be part of an input block in a cPlan.

json-input

json-input "/data/sales.json" {

column "product" key="product"

column "revenue" key="revenue"

column "units" key="revenue.0"

}

Specifies a json file to use as input. Each column that is created in the input must be paired with a key value in the json file. For more information, see Working with JSON File Format in Spectre .
keep

cplan {

cbase-input "budget.cbase"

keep {

column "Account"

column "Fee Total"

}

}

Keeps only the specified columns from the input. This is an optional input operation. See also remove.

A keep can be part of an input block, or used elsewhere in the cPlan.

key key "Acct ID" "Acct ID"

In a lookup block, names the lookup column on the input file (main key) and the column for the lookup table; the columns often have the exact same name.

See lookup.

keys-are-required-dimensions keys-are-required-dimensions Indicates that all columns brought in via the lookup are flagged as Infos off of the key dimension. This attribute is part of the lookup block.
label dimension "CustomerStreet" label="Customer Street" Changes the display name for a column.
limit-rows   Option for text-input.
lookup

cplan {

text-input "/data/address.txt" {

lookup {

text-input "location-master.txt"

key "address code" "location code"

column "Latitude"

column "Longitude"

}

}

}

Links an additional data source to the primary data source. The lookup block can also include column operations. Lookups are most efficient when the desired columns are specified using the column tag. This is particularly true when there are multiple lookups defined for the cPlan.

NOTE:

  • The first key listed is for the primary data source, while the second key is the lookup source. Often these keys have the same name.
  • If a second key is not listed, it is assumed to be the same as the key column in the primary source.
  • Lookups in cPlans should only be used as a last resort—better done at build time if the data set is large.
  • Lookup keys need to have the same data type defined in the source and lookup files.
  • Lookups are case sensitive.
  • The text-input, cbase-input, and odbc-input are all supported.
  • If no columns are specified in the lookup block, all columns are retrieved.
  • See the range-key tag.
  • A lookup does not need to be part of an input block in the cPlan.

TIP:

Lookups in cPlans make sense if the lookup tables change more frequently than the cBase. Consider if the same effect may be possible using a calc or an expression dimension, which scale better for large data sets.

metadata

cplan {

metadata {

cplan "xyz.cplan"

}

}

Specifies the inner cPlan to extract data from. The following columns are available:

  • automatically generated
  • column type
  • definition
  • description
  • format
  • label
  • name
  • origin
  • required dimensions
  • suggested column
  • suggested dimension
  • summary type
  • _masked

You can manipulate the created cPlan in the same ways as any other cPlan.

See cPlan Metadata Samples.

multilevel

cplan {

multilevel {

cbase-input "abc.cbase"

cbase-input "def.cbase"

...

}

}

Indicates a multilevel merge on multiple input files.

See Multilevel cPlans

NOTE: If consolidation of data sets is required, it should be done at build-time—listing multiple inputs in a Build script concatenates them together. With multiple inputs on a cPlan, dives are performed into each individually, before combining the results into a single table for display.

name

cbase-input "/cbases/denial_rates.cbase" name="full_data" {

rename "Denial Count" "All Denials"

}

 

Provides a name for the input that can be used downstream in expressions to reference that input. The name tag can be used with cbase-input, dive-input, odbc-input, and text-input.
odbc-input

cplan {

odbc-input trim=false {

dsn "MY-TestDB"

password "testpw"

user "testid"

query "SELECT * FROM Stats;"

stale-after 0

}

}

cplan {

cbase-input "import_export.cbase" {

lookup {

odbc-input {

dsn "Control Tables"

query "select code description from Master"

stale-after 60

}

key "code" "code"

column "description"

}

}

}

Indicates that a database is the data source. ODBC can be the primary input to the cPlan, or it can be used by a lookup within the cPlan.

TIP: The odbc-input is equivalent to a tunnel script—you retrieve live data.

Also, see the cbase-input, cplan-input, dive-input, and text-input tags for other input types.

For more tags to use in the odbc-input block, see ODBC Input Build Tags.

param_match

take-parameter "Denial Category" selection=true {

default-all-values

}

input "/cbases/denial_rates.cbase" name="subset_denial_counts" {

filter `param_match("Denial Category")`

}

Matches on one or multiple parameter selections.
pass-parameter

// outer.cplan

cplan {

take-parameter "must_also_set"

take-parameter "may_also_set" default="meh"

cplan-input "inner.cplan" {

pass-parameter "must_set" "so_i_will"

}

}

Provides for parameters inside a cplan-input tag.

NOTE: If a cPlan inherits from another cPlan using a path, the cPlan block can contain pass-parameter instructions for the cPlan referred to by the path but cannot contain take-parameter tags.

pgsql-input

pgsql-input {

connect-string "host=localhost port=5432 dbname=main-db user=postgres password=admin"

query "select * from database"

}

pgsql-input {

connect-file "connection.txt"

query "select * from database"

}

Specifies a PostgreSQL database as the input source. To use this input you must specify a connection string composed of the following sections:

  • host

  • port

  • dbname

  • user

  • password

The connection string can be listed directly in the input using the connect-string tag or in a file that is then linked to in the input using the connect-file tag.

The query tag is used to provide a SQL SELECT query that returns the data to be input into Spectre.

For more information about making a connect file, see Options for ODBC Login Credentials

property

for "summary" "Qty Shipped" "Profit" {

calc "$(summary) Y" {

property "Label" `param("summary") + " " + "2014"`

}

calc "$(summary) Y Y-1" {

property "Label" `param("summary") + " " + "2013"`

}

}

 

cplan {

cbase-input "/cbases/lookup_days.cbase"

calc "Tomorrow" {

property "Format" "YYYY-MM-DD"

property "Suggested Dimension" "False"

}

}

Sets qualities of a column used by the system in performing calculations and interpreting results. The following system properties can be set in a cPlan:

  • Current Date—The YYYY/MM/DD for the Current Date
  • Date Dimension —The name of the column used for the date
  • Description —A textual description of the column's contents
  • Format —A format string indicating how to display numeric data
  • Label —The display name of the column
  • Suggested Dimension—If set to True, the cPlan supports this column as a default dimension
  • Suggested Calc—If set to True, the cPlan supports this column in the displayed calcs by default

Both calc and dimension blocks support:

  • property "<name>" `<expression>`

  • property "<name>" "<value>"

See also Spectre Properties.

quotes   Option for text-input.
range-key

cplan {

text-input "base.cbase"

lookup {

text-input "lookup.txt"

range-key "Date" "Start Date" "End Date"

key "Code" "Code"

}

output "result.cbase"

}

Performs lookups using the range-key tag to connect one or more of the key columns to pairs of range columns in the lookup. A range-key takes three arguments:

  • The name of the key column in the main table
  • The name of the begin range column in the lookup table
  • The name of the end range column in the lookup table

For a lookup row to match a main row, the main table's key value must be between the begin-range and end-range values, inclusively.

The begin-range and end-range values can be null, in which case the range is open on either, or both, sides.

NOTE: Regular lookup keys can be any type except for double. Range lookup keys can be any type except for string.

remove

cplan {

cbase-input "/common/basic.cbase"

remove "Direction"

remove "Status"

}

Discards the specified column from the input. This is an optional input operation. Remove cannot be used with calcs. The remove does not need to be part of the input block in the cPlan.

See also keep.

rename

cplan {

cbase-input "/common/basic.cbase" {

rename "Direction" "Path"

}

}

cplan {

cbase-input "demodata.cbase" {

rename "customer zip" "zip code"

}

calc "Profit" `calc("Revenue") - calc("Cost")`

}

Changes the name of the column from the input—specify the old name, then the new. This is an optional input operation. The rename tag can only be used as part of an input block.

required-dimension replace "Sales Region" `value("Sales Region")` required-dimension="Company Name"

Sets a column to behave like classic Info Fields. In ProDiver, this field is available as a column that you can add to other columns displayed in a dive.

In this example, Sales Region becomes an Info Field on Company Name in the client console.

rotate

rotate {

rotate-output-names "TEST1" "TEST2"

row-out "R1"

auto-rows left-inclusive=true right-inclusive=true

row-out "R4"

}

Rotates data columns. The rotate tag can be within an input tag or used elsewhere in a cPlan.

See cPlan Code Samples.

rotate-output-names rotate-output-names "heading col name" "rotated data 1" "rotated data 2"

Identifies a heading column that indicates which columns are rotated to create a row, and value columns for the rotated column values. This tag names the columns that the respective row-out arguments refer to.

TIP: Elements for the rotate-output-names are labeled Row Name Column and Value Columns in the GUI.

row-out row-out "head" "start col" "end col"

Refines the rotation. The row-out tags specify the data that populates the heading column with the first argument and the columns that are rotated with the remaining arguments.

TIP: Elements for row-out appear in the GUI as Input columns :DimensionValue (first arg) and whatever is specified for Column.

squash

squash {

dimension "Mood"

dimension "Color"

column "Value"

column "Weight"

column "X" `calc("Value")`

}

Combines rows that are identical for a set of dimension columns. This tag adheres to the following syntax:

squash {

dimension "Dim1"

...

column "Calc1"

...

}

Columns may be explicitly referenced for inclusion or calculated as a constant or summary value. In the example, the Mood column and the Color column are checked for duplicate values. The Value and Weight columns have their values consolidated. The X column is calculated to be equal to the Value column.

stale-after

odbc-input {

dsn="Produce-dsn"

query="select apples, berries from veggies"

stale-after 60

}

Sets a time limit in seconds after which a cache entry is considered stale. The lower the number, the more up-to-date the data is, with the cost of less caching and more load on the DiveLine server. Use the stale-after tag to ensure that cached dives that use the results of the odbc-input are refreshed as often as the query results, which are re-run every minute in this example.

TIP: When an odbc-input does not specify a stale-after value, it is considered immediately stale and is not cached.

suggested-dimension column "Product Class" type="string" suggested-dimension=false Promotes (true) or demotes (false) a column when defining an expression dimension:
  • 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 string or date column from being defined as a dimension so that it does not appear in the ProDiver console.
  • When false is used, the column can still be promoted to a dimension for diving—equivalent to the concept of a dynamic dimension for a classic model.
summary summary "Cost" Indicates a summary for a time series.
take-parameter

cplan {

take-parameter "Mood Filter" selection=true {

default "Happy" "Dopey"

}
take-parameter "Color Filter" selection=true {

default-all-values

}

input "/common/basic.cbase"

calc "Filtered Value" `calc("Value")` filter=`param_match("Mood Filter", "Mood") and param_match("Color Filter", "Color")`

}

Declares parameters that must be set to interpret the cPlan.
text-input

cplan {

text-input "/data/month*.txt"

}

Specifies text files that should be immediately read and translated into a cBase as if those text inputs had been provided to a Build script.

Wild cards are supported in the filename and in the directory name as well. You can use the same attributes with a text-input tag in a cPlan that you can use in a Build. See Text, cBase, and Dive Input Build Tags. Not all tags are available.

TIP: Use text-input, similar to odbc-input, to base a cPlan on changing data.

Also, see the cbase-input, cplan-input, dive-input, and odbc-input tags for other input types.

time-series

time-series {

date "Order Date"

anchor ‘date(“2004/06/04”)‘

summary "Units"

ranges {

year-to-date

rolling-months 12

}

variations {

previous-year

}

}

Generates a large number of time-based calcs. Time series calcs are generated by combining a date expression, an anchor expression, a list of summaries, a list of ranges, and a list of variations.

This example generates the columns:

  • Units YTD
  • Units YTD LY
  • Units R12
  • Units R12 LY

See About Spectre Time Series.

tunnel-input

cplan {

consolidate {

cbase-input "main.cbase"

tunnel-input "extra.tnl"

}

}

Specifies a Tunnel script as the source for the input data. The tunnel script is executed and Spectre treats the resulting table of data as if it were a text file.

As with other data inputs, tunnel-input takes parameter provisions (same as in dive-input blocks), column options (same as in text-input blocks), and the stale-after option (same as in odbc-input blocks).

type

cplan {

metadata type="calcs" {

cbase-input "Accounts.cbase"

}

}

Indicates the type of metadata to retrieve. Options are:

  • calcs (default)

  • dimensions

  • inputs

  • calc-properties

  • cplan-properties

  • dimension-properties

  • input-properties

See Spectre Properties.

use-script-order

rotate use-script-order=false {

rotate-output-names "Size" "Count"

row-out "Small"

row-out "Medium"

row-out "Large"

}

Refines the rotation by allowing the programmer to avoid using script order for strings in a rotate. If not specified, defaults to true.

With the example in the snippet, the headings column, when sorted, sorts in string-sort order: "Large", "Medium", "Small". If use-script-order was omitted or set to true, that column sort in script-order—that is, the order they appear in the script: "Small", "Medium" then "Large".

You can generate sets of calcs that share some commonality by using loops. Loops in a script reinterpret a calc multiple times while varying one or more parameters with each iteration. The body of each loop consists of a set of calcs, time series objects, or additional loops that run several times with a varying parameter. There are three types of loops.

Loop Tags

Tag Example Notes
for

for "p" "Weight" "Height" {

calc "Minimum $(p)" `min(value(param("p")))` {

calc-set "$(p) Set"

}

calc "Maximum $(p)" `max(value(param("p")))` {

calc-set "$(p) Set"

}

calc "Average $(p)" `average(value(param("p")))` {

calc-set "$(p) Set"

}

calc "Median $(p)" `median(value(param("p")))` {

calc-set "$(p) Set"

}

}

Abstracts common patterns.

This example loops through the block for each value of "p". Creates min, max, average, and median calcs for both Weight and Height, and groups them into the calc sets "Weight Set" and "Height Set".

for-file

for-file "for_loop_parms.txt" "Name" "Definition" "Label" "Department" {

calc "$(Name)" `eval(param("Definition"))` {

property "Label" `param("Label")`

user-property "Department" `param("Department")`

}

}

Varies several parameters at once by reading them from a file. The file must be a tab-delimited text file, where the first line contains the name of each column, and each subsequent line contains a set of values for those names.

In this snippet, the for-file loop is specified by giving the name of the file, then each column that should be made available as a parameter, and then the block of operations.

TIP: The for-file block is similar to the for block but with externally located values. Useful for overriding the labels for existing calcs.

for-pair

for-pair "calc_name" "function" {

in {

"Total" "sum"

"Minimum" "min"

"Maximum" "max"

}

calc "$(calc_name)" `eval(param("function") + "(value(\\"Sales\\"))")` {

calc-sets "Range Calc Set"

}

}

Loops over two variables at once. Variables are listed after for-pair, and the in block holds the pairs over which to iterate.

This example creates three calcs as follows:

"Total" `sum(value("Sales"))`

"Minimum" `min(value("Sales"))`

"Maximum" `max(value("Sales"))`

See also: