Formatting Data In Data Grids

You can use the functions in this section to format your Data Grid to comply with locale-specific formatting standards or to provide data in a familiar format to your users.

ConditionalCellStyle

Use this function to apply a conditional style to an area of Data Grid cells. If you use an unsupported value for a style, a warning icon appears in the cell.

When you hover over the cell, a tooltip indicates the error.

Syntax:

where:

• Areaspec is a semicolon ( ; ) delimited list in order of dimension that can contain a list of pipe ( | ) delimited symbol specifications. If you leave the symbol specification empty, all the symbols for that dimension inherit the conditional style. You can have multiple conditional statements with overlapping area specifications; the order of the statements in the Data View definition file determines the priority from lowest to highest. For more information, see Working with area specs.
• Areaspec is a semicolon ( ; ) delimited list in order of dimension that can contain a list of pipe ( | ) delimited symbol specifications. If you leave the symbol specification empty, all the symbols for that dimension inherit the conditional style. You can have multiple conditional statements with overlapping area specifications; the order of the statements in the Data View definition file determines the priority from lowest to highest. For more information, see Working with area specs.
• Areaspec is a semicolon ( ; ) delimited list in order of dimension that can contain a list of pipe ( | ) delimited symbol specifications. If you leave the symbol specification empty, all the symbols for that dimension inherit the conditional style. You can have multiple conditional statements with overlapping area specifications; the order of the statements in the Data View definition file determines the priority from lowest to highest. For more information, see Working with area specs.
• Condition is a condition statement. Use %v to indicate the cell value with AND and OR and the following arithmetic or text operators:

  Arithmetic operator	Text operator	Definition
  ==	EQ	Equal to
  !=	NE	Not equal to
  >	GT	Greater than
  >=	GE	Great than or equal to
  <	LT	Less than
  <=	LE	Less than or equal to

  Note: Enclose string values with \" to apply a style to a string. For example, \"sample string\".

• Style1, Style2, etc can be any of the following:

  Style	Description	Example
  BACKGROUND	Specifies the cell background color to display. The values for this parameter can be system color names and are case sensitive. By default, the cell background color is white. For more information on system colors, refer to the MSDN web site section on colors by name.	BACKGROUND:Cyan
  FOREGROUND	Specifies the text color for the cell contents. The values for this parameter can be system color names and are case sensitive. By default, the cell foreground color is black. For more information on system colors, refer to the MSDN web site section on colors by name. Because the text in protected cells in a Data Grid appears in blue, you should choose another color for conditional cell formatting to avoid any confusion to users. Using this setting overrides the default color for protected cells.	FOREGROUND:Gray

Example:

Related functions

• Protect

DefaultDecimals

Use this function to specify the default number of decimals to display for cells containing numeric values in a Data Grid.

Note: The Data Grid displays decimals based on the following priority of functions, if specified: NumericInputOnly, SymbolDecimals, DefaultDecimals. For more information, see NumericInputOnly or SymbolDecimals.

Syntax:

where:

• Number is the number of decimals to display. If you do not specify this parameter, the Data Grid displays two decimals.

Example:

DimensionFreeze

Use this function to specify the number of columns in a dimension that should be frozen when scrolling horizontally. If a parent symbol is frozen, all of its children are frozen when it is expanded.

Note: If the Data Grid contains nested columns, the DimensionFreeze function is ignored.

Syntax:

where:

• Dimension is the dimension for which to freeze.
• Number is the number of columns in the specified dimension to freeze.

Example:

DimensionLabel

Use this function to replace generic dimension names in a Data Grid with terms more familiar to users. The label will appear in the following areas:

• Above Slice Dimensions
• In Symbol Selectors for Slice Dimensions
• In the Fixed Dimensions tab
• In the Line Item Details, Comments and Attachments dialog
• In the Print Preview for Data Grids

If you do not specify the dimension labels to use, the system displays the dimension name.

Syntax:

where:

• Dimension is the dimension for which to specify the dimension label.
• Label is the label to use.

Example:

DimensionTitles

Use this function to specify whether to display symbol names, descriptions, or a symbol’s attribute value for a specified dimension in the Data Grid. If you do not specify the dimension titles to use, the system displays symbol descriptions. If you specify the name or description and the attribute value for a dimension, the name or description displays.

Syntax:

where:

• Dimension is the dimension for which to specify dimension titles.
• NAME displays symbol names in the Data Grid.
• DESCRIPTION displays symbol descriptions in the Data Grid.
• ATTRIBUTE displays the value of the specified symbol attribute in the Data Grid.
• attribute is the name of the symbol attribute to use.

Note: You cannot specify NAME, DESCRIPTION, or ATTRIBUTE in the same command statement. To display symbol names and descriptions for a dimension, write separate statements for the dimension.

Example:

DynamicRollup

Use this function to dynamically update the totals in the Data Grid as users enter data. If this function is ON or not specified, the parent values update automatically to reflect manual changes users make to the values in the Data Grid.

Syntax:

where:

• ON dynamically rolls up data in the grid as users make changes.
• OFF does not update parent data as users make changes to the grid until the users submit their changes and then refresh the grid.

Example:

Filter

Use this function to filter out numeric or string values in a Data Grid based on a condition.

Syntax:

where:

• Areaspec is a semicolon ( ; ) delimited list in order of dimension that can contain a list of pipe ( | ) delimited symbol specifications. If you leave the symbol specification empty, all the symbols for that dimension that are part of the area spec are filtered if they meet the condition. For more information, see Working with area specs.
• Condition is a condition statement. Use %v to indicate the cell value with AND and OR and the following arithmetic or text operators:

  Arithemtic operator	Text operator	Definition
  ==	EQ	Equal to
  !=	NE	Not equal to
  >	GT	Greater than
  >=	GE	Great than or equal to
  <	LT	Less than
  <=	LE	Less than or equal to

Note: Enclose string values with \" to apply a style to a string. For example, \"sample string\".

Example:

LIDInputOnly

Use this function to enforce line item details for the specified symbols in a Data Grid, and optionally, to prepopulate line item details comments. For example, you can prepopulate line item details comments for travel expenses with Airfare, Hotel, Car rental, and so on. For more information, see Working with line item details.

Caution: If you select symbols for line item details, make sure you select the related time period in the Across dimension. Selecting the wrong time period symbol could result in mismatched data. For more information, see Columns.

Syntax:

where:

• Areaspec is a semicolon ( ; ) delimited list in order of dimension that can contain a list of pipe ( | ) delimited symbol specifications. If you leave the symbol specification empty, all the symbols for that dimension enforce line item details. For more information, see Working with area specs.

  Note: For the time periods dimension, you must specify a symbol for which the ZGPLineItemDetailReference attribute is set.

• Comment1... Comment20 are optional and define the prepopulated line item details comments to appear in the Line Item Details dialog. Separate comment lines with a pipe ( | ). You can add up to 20 prepopulated lines of comments.

Example:

NumericInputOnly

Use this function to restrict any input by users to numeric values for the specified data area. You can also restrict the number of decimals.

This restriction applies only when users manually input or copy/paste a value into the cell. If a model, procedure, or import process populates a cell with a numeric only restriction, the restriction is ignored.

If you designate a cell as numeric only and the current value is not a numeric value, a warning appears, but the data is still submitted to the database. All numeric input cells are right aligned in the Data Grid.

Note: The Data Grid displays decimals based on the following priority of functions, if specified: NumericInputOnly, SymbolDecimals, DefaultDecimals. For more information, see SymbolDecimals or DefaultDecimals.

Syntax:

where:

• Areaspec is a semicolon ( ; ) delimited list in order of dimension that can contain a list of pipe ( | ) delimited symbol specifications. For more information, see Working with area specs.
• Decimals is the number of decimals to restrict numeric input to. This parameter is optional. If you do not specify this parameter, the system restricts decimal input based on the Number parameter of the DefaultDecimals function. For more information, see DefaultDecimals.

Example:

Parentheses

Use this function to specify whether or not negative numbers display with parentheses instead of a negative sign.

Syntax:

where:

• ON displays negative numbers with parentheses.
• OFF displays negative numbers with a negative sign. If you do not specify this parameter, the grid displays negative numbers with a negative sign.

Example:

Protect

Use this function to prevent users from manually entering data to specific cells in a Data Grid. The contents of protected cells appear in blue in the Data Grid. If you are using conditional cell styles in the Data Grid, you should set the foreground color to something other than blue to avoid any confusion to users. If you specify both ConditionalCellStyle and Protect for a cell, the ConditionalCellStyle takes priority.

Note: Non-static parent cells are automatically protected.

Syntax:

where:

• Areaspec is a semicolon ( ; ) delimited list in order of dimension that can contain a list of pipe ( | ) delimited symbol specifications. If you leave the symbol specification empty, all the symbols for that dimension that are part of the area spec are protected from manual input by users. For more information, see Working with area specs.

Example:

Related functions

• CondtionalCellStyle

SymbolDecimals

Use this function to specify the number of decimals to display for a specific symbol. The priorities for decimals are (from highest to lowest priority):

• Inner row
• Outer row
• Inner column
• Outer column

Note: The Data Grid displays decimals based on the following priority of functions, if specified: NumericInputOnly, SymbolDecimals, DefaultDecimals. For more information, see NumericInputOnly or DefaultDecimals.

Syntax:

where:

• Dimension is the dimension that contains the symbol to apply the number of decimals to.
• Symbol is the symbol to apply the number of decimals to.
• Decimals is the number of decimals to display.

Example:

SymbolSuppress

Use this function to suppress symbols in the specified dimension that have values equal to zero. Suppression behavior applies only to the innermost dimension.

Syntax:

where:

• Dimension is the dimension name.
• Suppress is the symbol level to suppress and can be one of the following values:

  Value	Description
  LEAF	Suppresses any leaf symbols in the specified dimension that have a value of zero.
  ALL	Suppresses all symbols in the specified dimension that have a value of zero.

Example:

TextInputOnly

Use this function to restrict any input by users to text for the specified data area. This restriction applies only when users manually input or copy/paste a value into the cell. If a model, procedure, or import process populates a cell with a text only restriction, the restriction is ignored.

If you designate a cell as text only and the current value is not a text value, a warning appears, but the data is still submitted to the database.

All text input cells are left aligned in the Data Grid.

Note: If a user enters a numeric value into a text input cell, the value is rendered and stored as text.

Syntax:

where:

• Areaspec is a semicolon ( ; ) delimited list in order of dimension that can contain a list of pipe ( | ) delimited symbol specifications. For more information, see Working with area specs.

Example:

ThousandsSeparator

Use this function to specify whether numeric values display with a locale-specific thousands separator.

Syntax:

where:

• ON displays a locale-specific thousands separator based on the operating system setting.
• OFF does not display a thousands separator. If this function is not specified, OFF is used.

Example: