Column Properties

When a data model is opened in a worksheet view, column headers can be grouped. This is used to show the columns that are related to the same entity.

For example, if Customer is specified in this property for both Customer Number and Customer Name, the resulting column header in a worksheet would be as follows:

This property is used in conjunction with the Column Heading property.

Column Heading

Column Group Heading and Column Heading are combined to identify the columns of a data model.

Visible Level

This property is used to identify the minimum authorization level applicable when displaying the specified column. The column's visible level is one of the elementary authorizations that can be set for a user within Data Model Authorizations. The value of this elementary authorization is compared to the Visible Level property to determine if a user is allowed to see the column. (00 corresponds to the Admin user.)

Format

Display format for Numbers, Currency, Date & Time, Percentages and Text. Each category may have several options, as applicable (for example for numbers one can specify decimal places and leading zeros). A preview of a formatted expression is provided. From SEI Version 9, you can now display images or add hyperlinks to your worksheet.

Column Type (Fields)

There are three main categories of column type (fields):

• Dimension Fields: Fields for which data is to be analyzed, summarized or grouped (e.g. Company, Region Code, Customer Account No, Salesrep Code, Item Class, etc.). Fields that determine a unique record in the data model must be set as Dimension Key (needed if data model is setup to allow for data editing and can be a combination of multiple columns).

• Measure Data: Fields that represent data to be analyzed (e.g. Goods Value, Quantity, Cost). Measures that are calculated based on the values of other columns are automatically set as Measure Calculated.

• Description Data: Fields that give a description of some dimension fields (e.g. Customer Name, Item Description, Salesrep Name, etc.).

Description Field

This property allows linking a dimension key to its description field (e.g. Customer Account Number with Customer Name, Item Code with Item Description). If data is grouped by Item Code in a worksheet, the grouped lines could contain both the Item Code and its description, depending upon the Description Format property.

Description Format

Specify the layout, in a worksheet or a graph, of dimension fields and their descriptions. The Title indicates the name of the field, the Description refers to the value of the description field associated with the field and Code is the value of the field itself.

For example, if the Description field for a Customer Code was the Customer Name, and the Description Format is Title-Description-Code, then a worksheet grouped by the Customer Code would display each group as something similar to Customer code-123-Company ABC, where 123 is the Customer Code and Company ABC is the name of customer 123. You have the same option with the "-" separator or without the separator.

Prompt Query

Prompt assigned to this column.

Prompt Sort Order

If the current data model is used as a prompt, this property will define how the values in the prompt window will be sorted.

Subtotal

Specify which kind of summary operation will be used to calculate subtotals when data is grouped.

Subtotal Restrictions

How to apply restrictions on what grouping levels should show subtotal values.

Subtotal Exceptions

Allows a different subtotal type for a selected group (if subtotal is Percentage of total, exceptions will be ignored).

OnCalculation

If the Subtotal property is set to OnCalcul, this property defines a script used to calculate the subtotal.

Calculation

Customize a SQL calculation for the calculated column

Parameter Name

Specify an SEI Global Parameter name which is used to set up application and process Links, Dashboard Filtering and setting up Dashboard Filtering Panels (refer to Global Parameters).

Edit Level

This property is used to define the minimum level of security that a user must have to be authorized to edit the value of the current column. Column Editable level is one of the elementary Authorizations that can be set for a user within the Data Model Authorizations. The value of this elementary Authorization is compared to the Editable level property to determine if a user is allowed to edit the value of related column.

(Level 00 is only for the Admin user.)

Note:  If a column is to be editable, one or more dimensions must be set as a key in order to write properly into database.

Edit Calculation

This is used to recalculate this specific column when any changes occur in one of the dependent columns. For example in the Forecast Total column (that represents the total of the 12 columns of Forecast Amount), you could recalculate the total column when any of the forecast values change in one of the 12 columns by adding a script (necResult=Forecast1+Forecast2+Forecast3).

Split By Ratio On

Used for splitting any amount entered on a total line using a ratio based on this specified field. Applicable only if this column is editable. For example, a budget entry data model could have this property set to the actual amount, for the budget amount field (thus any budget values entered at a group level would be split at the detail level according to the ratios of the actual amounts).

Validate Against Prompt

When a prompt is set on this field, a validation will run so that the value is part of the prompt and block non-existent values if set to yes. With No, every value could be entered in this case.

(Source Field Name)

Database field name of the current column.

(Source File Name)

Database table or view name for the current column.

(Source File Alias)

Database table or view name for the current column (if a table or view has been added more than once).

Data Type

Database field type. It automatically takes the value from the database but it can be changed for calculated fields.

Data Length or Decimal Precision

If data type is NUMERIC, this property determines the number of decimal places that will be considered when displaying data. If data type is CHAR, the property determines the field length. This automatically defaults from the database, but can be changed for calculated fields.