cPlan Tags

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.

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:

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

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.

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.

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.

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).

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.

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?

Drops the named column from the input.

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.

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.

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

See also:

• Text, cBase, and Dive Input Build Tags
• Spectre Format Conventions
• Spectre Properties

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.

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.

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.

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.

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.

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.

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

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.

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.

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.

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

squash {

}

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.

• 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.

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.

Indicates the type of metadata to retrieve. Options are:

• calcs (default)

• dimensions

• inputs

• calc-properties

• cplan-properties

• dimension-properties

• input-properties

See Spectre Properties.

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".