Column Properties

Once a field has been added to the columns its properties can be set within the Data Model Fields Table section.

Property Name Description Possible Values
General    
Column Group Heading

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.

Free text

Column Heading

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

Free text

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

00-No Authorization

10-Basic

20-Intermediate

30-Advanced

40-Manager

50-Administrator

60-System Administrator

99-Security Officer

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.

Selected from a list

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

 

 

Dimension key

Dimension data

 

Measure Data

Measure Calculated

 

Description data

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.

Selected from a list of the available fields within the data model

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.

Title-Code-Description

Title-Description-Code

Title-Code

Title-Description

Code-Description

Code

Description

Description-Code

Title Code Description

Title Description Code

Title Code

Title Description

Code Description

Prompt Query

Prompt assigned to this column.

Any Data Models that are defined as a prompt.

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.

None

ASC

DESC

Subtotal

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

None

Sum

Min

Max

Avg (average)

Count

Count Distinct

OnCalcul

Percentage of total

Subtotal Restrictions

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

Subtotal restrictions window

Subtotal Exceptions

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

Subtotal exceptions window

OnCalculation

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

Refer to the JavaScript Builder section

Calculation

Customize a SQL calculation for the calculated column

SQL syntax, refer to the SQL Script Builder

Window Function This property allows the user to create and apply a customized Window Function. SUM, AVG, COUNT, MIN, MAX, STDEV, STDEVP, VAR, VARP, ROW_NUMBER, RANK, DENSE_RANK, NTILE_4, NTILE_100, LAG, LEAD, FIRST_VALUE, LAST_VALUE, CUME_DIST, PERCENT_RANK, ASC, DESC

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

List of Global Parameters

Edit

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.

00-No Authorization

10-Basic

20-Intermediate

30-Advanced

40-Manager

50-Administrator

60-System Administrator

99-Security Officer

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

Refer to the JavaScript Builder section

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

Selected from a list

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.

Yes

No

Advanced

(Source Field Name)

Database field name of the current column.

Cannot be changed.

(Source File Name)

Database table or view name for the current column.

Cannot be changed.

(Source File Alias)

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

Cannot be changed.

Data Type

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

Note

User input will be validated in the following places:

CHAR

NUMERIC

INTEGER

DATE

TIME

DATETIME

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.

Numeric

Where Clause Section
Where

The Where section allows you to enter a clause for filtering of the data for the whole data model that regular users cannot override on their views.

This clause should be written in a language compatible with the data source of the data model. For example, if the data source is SQL Server, the Where clause should contain a statement in T-SQL Language.