cPlan Tags

Defines the anchor date in a time-series block.

See Time Series Tags

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.

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.

Defines the system property Current Date. The string value must use the format "YYYY/MM/DD". Available starting with Spectre 7.1(19).

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.

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:

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

Starting with Spectre 7.1(19), additional options are available:

• calc-properties

• cplan-properties

• dimension-properties

• input-properties

See Spectre Properties.

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.