This article covers the advanced features of the Data Object Editor’s Group Tab “Expression” section. For a more generic article on this topic, see How to Create “Group By” Data Settings.
To organize groups alphabetically or numerically use, use the “Sort By'” option at the top of the Grouping Tab. In cases where a group needs to be conditionally re-organized (e.g., forced to the top or bottom of the list), use the “Expressions” section of the to create a custom expression which determines the relative order of the group. In this example, we will place the ASSETCLASSNAME “Cash” at the bottom of the table. In this case, start by clicking on “Insert Condition” in the Expressions section of the Grouping Tab.
If the column, group, and condition are already known simply type “Condition” field— if not, simply click on the function button to the right of the Condition field to bring up the function builder screen.
In the function builder, users can build a function which will conditionally place the group. In our example, we want to place the Asset Class “Cash” at the bottom of the table. Note, that this is not the mapping order but rather the order of the groups themselves (i.e., mapped order is done by end user in the PowerPoint Editor’s Fabricate & Edit Panel).
Generally, the conditions are fairly simplistic, usually consisting of the column’s name and the group to be ordered. Shown below, is a simple example using Asset Class Name column.
ASSETCLASSNAME = "Equities"If needed, there are three system variables that can be used to create more complex conditions, they are:
| Name | Display Name | Type | Description |
|---|---|---|---|
| _GroupingColumn | _GroupingColumn | System Variable | Dynamically references the selected “Group By” value (e.g., ASSETCLASSNAME) |
| _SortingColumn | _SortingColumn | System Variable | Dynamically references the selected “Sort By” column (e.g., PERCENTAGE) |
| _SortOrder | _SortOrder | System Variable | Dynamically references the Sort By value (e.g., Ascending or Descending) |
In the following example, if the user chooses to group the rows by “ASSETCLASSNAME” in the Data Settings and the ASSETCLASSNAME is equal to “Equities”, then the condition applies. Note how the variables are used without quotation marks while the value does.
_GroupingColumn = "ASSETCLASSNAME" and ASSETCLASSNAME = "Equities"After creating an expression, as shown above, we need to provide the position that the group(s) should occupy. For example, if we wanted place the group “Cash” last, we would assign “-1” to the condition.
If we want to add additional groups, that would be placed below “Cash”, we will assign increasingly negative values (e.g., -2, -3, etc.) as shown below.
To push a group to the top of the list, assign increasingly positive values. Shown below, Cash and ETF groups have been moved to the bottom of the list and Equity has been moved to the first position. All other groups (e.g., Fixed Income, Derivatives, etc.), will be sorted according the selected “Sort By” value.
