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.
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
ConditionalCellStyle Trial_Balance;;;;;;;, "%v!=0", FOREGROUND:Red, BACKGROUND:Yellow
ConditionalCellStyle SALEST#99;QAPYR#99;;;;;;;;;;;;;;, "%v==\"test3\"", BACKGROUND:YellowRelated functions
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”.
where:
- Number is the number of decimals to display. If you do not specify this parameter, the Data Grid displays two decimals.
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.
where:
- Dimension is the dimension for which to freeze.
- Number is the number of columns in the specified dimension to freeze.
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.
where:
- Dimension is the dimension for which to specify the dimension label.
- Label is the label to use.
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.
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
DimensionTitles ACCOUNTS, NAME
DimensionTitles TIMEPER, DESCRIPTION
DimensionTitles ENTITIES, DESCRIPTION
DimensionTitles PRODUCTS, ATTRIBUTE:AZReportDescription 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.
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.
Filter
Use this function to filter out numeric or string values in a Data Grid based on a condition.
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
Filter Trial_Balance###;;;;;;;, "%v==0"
Filter SALEST#99;QAPYR#99;;;;;;;;;;;;;;, "%v==\"test3\"" 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.
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.
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.
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”.
Parentheses
Use this function to specify whether or not negative numbers display with parentheses instead of a negative sign.
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.
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.
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
Protect sales6;;;;;;;
Protect sales1|sales2|sales5|cashT#99;Jan_YTD;Canada###;;CCAD|CUSD;; Related functions
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”.
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.
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.
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.
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.
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”.
ThousandsSeparator
Use this function to specify whether numeric values display with a locale-specific thousands separator.
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.