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 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. Automatically defaults from the database, but can be changed for calculated fields. |
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. |