Columns

Overview

The data on which metrics are built is divided into two categories (column types):

Measures, which become the values plotted.
Dimensions, which become the indices of a graph.

Since they both describe a column used to make a metric graph, they are referred to as (Metric) Base Columns.

A Measure is a numerical calculation made on a metric base's source data. Average test scores, attendance percentages, and the total number of discipline incidents are measures. However, just because a piece of data is numeric, it is not necessarily a measure. A student's age should be considered a dimension, not a measure.

A Dimension is an attribute, such as a school subject or ethnicity, which specifies groups by which a measure is calculated. For example, average test scores are more analytically valuable when grouped by gender or special education. The fields used for grouping are considered dimensions.

Every metric requires a measure and can take any number of dimensions, but one or two are most common. A metric with no dimension columns groups all records together that have not been filtered out, and calculates its measure without a group.

Metric base column properties

To view Metric Base Column definition settings, choose Content Definitions in the Navigation tree, then Columns.

Metric base column definitions are stored first by the general data domain in Metric Base Column folders, under which they are typically further separated into other folders named Dimensions and Measures.

Open the folder and select the metric base column definition to open the View Properties window.

Control security

Setting	Description
Apply View Security	Determines whether the View security permission is applied when this column is displayed and used. When enabled, object security is applied when this column is rendered and used in a metric or detail. When disabled (default), the object security is not applied.

Base column settings

Setting	Description
Column Label	The label displayed in column headings and when displaying drill criteria or comments for this column. A value in this field is required to save the column definition.
Condition Set	The Condition Set that is used when this column is added to a metric. This selection can be overwritten at the metric column level.
Column Type	Defines the type of column. Dimension columns are used for the grouping of data and drilling. Measure columns are used for showing aggregated values such as count of students, average grades, etc.
Is Aggregate	Prevents the column's select clause from appearing in the grouping clause of the generated SQL statement for a metric. Useful for expressions like max ("Title:"). The setting "Is Aggregate" applies to measures as well. If there are any measure columns that are using an aggregating expression, be sure to check this option.
Select Clause	Expression added to the SELECT clause of the metric SQL statement when this column is used in a metric. This can be a single column from a table contained within the metric base's FROM clause or it could be a complicated calculation referencing multiple columns.
Where Clause	Expression added to the WHERE clause of the metric SQL statement when this column is used in a metric.
Order By	Overrides the standard expression that is generated for the Order by clause of the metric SQL statement. By default, the standard Order By uses the value defined in the Select Clause for this column if the column is defined as a Dimension.
Format Type	The type of value that is handled by the Formatting Mask used for the column. "Number" uses decimal format rules, "Date Time" uses date with time format rules; "Date" uses date-only format rules.
Format Mask	Formatting mask that is used when the column appears in an HTML table. Currently, this is not used for charts. These are based on standard Java formatting strings for Numbers or Dates. These formatting rules will also be applied as Excel format mask rules when the metric is exported to Excel.

Custom mapping settings

Setting	Description
Custom Mapping Allowed	When enabled, this setting specifies that this column will be mapped to the inner query when the metric base has a custom data mapping defined. When disabled, this column is always used in the outer query and no alias remap will occur. If used as a Select or Order column and a Mapped Alias is defined, it will be remapped and used in the inner query.
Mapped Alias	The custom alias to use for this column in the inner query when the metric base specifies a custom data mapping. The inner query is processed by a custom data mapping function. When this setting is empty, this column is used in the outer query instead.

List of values

Setting

Description

Dynamic Query Allowed

When enabled, this setting specifies that the List of Values for this column can resolve to a SQL query that is evaluated when the metric is refreshed. The statement, after variables and evaluation blocks are processed, must start with "select" (prefixing white space is ignored) to get processed as a query. Otherwise, it is considered a static entry.

List of Values

A list of values that always appears in the resulting metric data. Each item in the list appears in the specified order even if there are no results for that value. Values are delimited by a new line.

The SELECT clause of a Metric Base Column must be consistent with the SQL clauses of a Metric Base if the two are to be successfully associated with the same metric.