AX1723

Using the HierarchicalGrid data source with Data Grid components

You can optionally use a HierarchicalGrid data source with a Data Grid component, in order to show data in the grid grouped by dimensions. This data source defines a list of DataGridColumns data sources, so that each grouping level can be defined as a separate grid with its own unique primary table, columns, and other properties.

This feature is an alternative to using the built-in Show Hierarchical Column Data option in the Data Grid properties. You should use a HierarchicalGrid data source instead if:

• You need different grouping levels to show different columns. When using a HierarchicalGrid data source, each grouping level has its own DataGridColumns data source to determine the columns that display for that level. When using the Show Hierarchical Column Data option, all levels use the same DataGridColumns data source and show the same columns.

• You need different grouping levels to use different primary tables and/or filters. When using a HierarchicalGrid data source, each grouping level specifies its own primary table and filter, so each level can query a different set of data. When using the Show Hierarchical Column Data option, all levels use the same primary table and filter.

• You need different grouping levels to use different grid options, such as enabling drilling or the ability to select rows. When using a HierarchicalGrid data source, each grouping level has its own defined set of grid options that apply only to that level. When using the Show Hierarchical Column Data option, all levels use the same grid options.

The basic setup steps to use a HierarchicalGrid source are as follows:

• Create a DataGridColumns data source for each grouping level that you want to show in the grid.
• Create a HierarchicalGrid data source and set up a row for each DataGridColumns data source.
• Place a Data Grid component on the form canvas, and select the HierarchicalGrid data source as the data source for the component.

Defining a HierarchicalGrid data source

The tags for a HierarchicalGrid source are as follows:

Primary tag

[HierarchicalGrid;DataSourceName]

The DataSourceName identifies this data source so that it can be assigned to a Data Grid component. Data source names must be unique within a file and must start with a letter. Names can only contain letters, numbers, and underscores. Names are validated when the file is saved; an invalid name will prevent the save.

The placement of this primary tag defines the control column and the control row for the data source.

• All column tags must be placed in this row, to the right of the tag.
• All row tags must be placed in this column, below the tag.

Row tags

[Grid]

Each row flagged with this tag specifies a separately defined DataGridColumns data source to show as a grouping level in the Data Grid component. The first Grid row is the top-level grouping, the second grid row is the second level grouping, and so on.

Column tags

[GridColumnsDataSource]

The name of the DataGridColumns data source that defines the columns to display for this grouping level in the grid. Enter the name of any DataGridColumns data source.

The DataGridColumns data sources referenced here can use any of the normal features of DataGridColumns data sources, including enabling filters for columns, applying alternate aggregation or column filters, and using icons.

[PrimaryTable]

The name of the primary table for the grouping level. Enter any valid table name from the Table Library. The primary table determines which table columns are valid to include in the DataGridColumns data source for the grouping level. System tables (such as Axiom.Columns) cannot be used as the primary table.

For example, if you specify GL2020 as the primary table, then the query can retrieve data from that table, plus any reference tables that the primary table looks up to. If you want to include data from multiple data tables, you can include any table that shares keys with the primary table, as well as any shared lookup reference tables.

Each Grid row can use a different primary table, however, each grouping level must be filterable by the levels above it. Generally speaking, this means that all of the primary tables must share a common lookup column, and the sum by columns for each grouping level must be compatible with that lookup column. For more information, see How the data in each grouping level is determined.

[Filter]

Optional. A filter to limit the data returned by the query and displayed in the grouping level. Enter a filter criteria statement that is valid in the context of the primary table. If no filter is defined, all data that matches the query is displayed in the grouping level.

[SuppressZeroRows]

Optional. Specifies whether data rows that contain all zeroes are suppressed from showing in the grouping level (True/False). By default, this is False.

Non-key columns that meet both of the following criteria are evaluated to determine whether a row should be hidden:

• The column data type is Integer (all types) or Numeric.
• The column is from the primary table or an additional data table.

If the primary table is a data table, Integer and Numeric columns on lookup reference tables are ignored—meaning these columns may have values, but the row is still suppressed if all applicable data table columns have zero values. There is one exception: reference table columns are considered if the column classification is Values and the numeric type is Currency.

Calculated columns defined in the data grid are not evaluated for this purpose and do not prevent a row from being suppressed.

[PageSize]

Optional. A number that determines how many rows are shown in each "page" of the grouping level. If the results returned by the query exceed the page size, then the data is separated into multiple pages. Users can use the page controls at the bottom of the grouping to move among pages.

If omitted or blank, the default page size is 50. If set to 0, then all rows display in each grouping, with no paging.

[EnableRowSelection]

Optional. Specifies whether users can select a row in the grouping level (True/False). If False or omitted, then rows in the grouping level are not selectable.

If True, then rows are selectable in the grouping level. When a user selects a row, the values for that row are written back to the corresponding DataGridColumns data source, in the SelectedRowValue column. The form can be configured to change in some way based on the currently selected values.

For general information on how row selection works for Data Grid components, see Interactive behavior. Note the following when using row selection with a HierarchicalGrid data source:

• Although multiple grouping levels can be enabled for row selection, only one row across all groupings can be the currently selected row at any one time.

• When a row is selected in a grouping, values are written back for that row as well as for all grouping levels above that row. For example, if you expanded Region West and then selected Dept 42000, values are written back for the Region West row as well as the selected Dept 42000 row, in the corresponding DataGridColumns data sources.

• In addition to the row values, the grouping filter corresponding to the selected row is written back to the SelectedRowFilter column in the HierarchicalGrid data source.

[AutoSubmit]

Optional. Specifies whether the Axiom form automatically updates when a user selects a row in the grouping level (True/False). This option only applies if EnableRowSelection is True.

If False or omitted, auto submit is disabled. You should leave this option disabled if you have not enabled row selection. However, if you have enabled row selection, then in most cases you will want to enable auto submit as well.

If both auto submit and row selection are enabled, then the form automatically updates when the user selects a row in the grouping level (by clicking on it). If auto submit is disabled but row selection is enabled, then the user must use a separate Button component in order to update the form for the selected row.

[EnableDrilling]

Optional. Specifies whether drilling is enabled for rows in the grouping level. If False or omitted, then drilling is not enabled for the grouping level. If enabled, users can "drill down" a row in the grouping level to see the data in that row at a different level of detail.

For more information about how drilling works for Data Grid components, see Setting up drilling for Data Grid components in Axiom forms. Drilling works the same way when using a HierarchicalGrid data source; the only difference is that the drilling options are configured in the HierarchicalGrid data source instead of in the component properties.

[DrillButtonTooltip]

Optional. Defines text to display in a tooltip when a user hovers their cursor over the drill icon. If left blank, no tooltip displays on hover.

[DrillHierarchy]

Optional. Specify one or more hierarchies to determine the drilling levels available to users. For more information on how to specify the desired hierarchies, and how users select from the hierarchy levels, see Using hierarchies to define drilling levels.

[DrillLevelsDataSource]

Optional. Enter the name of a DrillLevels data source. If specified, users will be presented with the custom drilling options defined in this data source. For more information on creating the data source, and how users select from the custom drilling options, see Using a DrillLevels data source to define drilling levels.

[SelectedRowFilter]

System-controlled field. If a row is selected in the grid, this field is populated with a filter that represents the selected row (based on the sum by level for the grid). This field is automatically updated by Axiom Software when a user selects a row in the grid. This field supersedes the Selected Row Filter field in the Data Grid component properties, which is not populated when using a HierarchicalGrid data source.

For example, if a user selects the row for Dept 42000, then this field is updated to contain the filter Dept.Dept=42000 (on the data source row corresponding to the grouping level where the grid row was selected). If the selected row is within a grouping level that is not the top-level grouping, then the corresponding filters are also written for all grouping levels above the selected row.

This filter is for reference only and can be used to drive other components in the form if desired.

[ActionRowFilter]

System-controlled field. If an icon in the grid is used to trigger an action, this field is populated with a filter that represents the action row (based on the sum by level for the grid). This field is automatically updated by Axiom Software when a user clicks an interactive icon in the grid. This field supersedes the Action Row Filter field in the Data Grid component properties, which is not populated when using a HierarchicalGrid data source.

For example, if a user clicks the icon in the row for Dept 42000, then this field is updated to contain the filter Dept.Dept=42000 (on the data source row corresponding to the grouping level for the action row). If the action row is within a grouping level that is not the top-level grouping, then the corresponding filters are also written for all grouping levels above the action row.

This filter is for reference only and can be used to drive other components in the form if desired.

To use the Data Source Wizard to add the tags to a sheet, right-click in a cell and then select Create Axiom Form Data Source > Hierarchical Data Grid. You can also highlight a range of data first and then use the wizard to add the tags around that data. The cells in the row above and the column to the left of the selected area must be blank in order for Axiom to place the tags in sheet.

The following example shows a sample HierarchicalGrid data source tagged in a sheet:

Example HierarchicalGrid data source

The resulting data grid for the example data source shown above looks as follows:

Example data grid using HierarchicalGrid data source

In this example, the first two grouping levels are showing the same basic columns and querying the same table, just using a different sum by level. The third level queries a different table and shows a completely different set of columns.

Configuring a Data Grid component to use a HierarchicalGrid data source

When you configure the Data Source for a Data Grid component, you can select either a regular DataGridColumns data source or a HierarchicalGrid data source. When you select a HierarchicalGrid data source, most of the component-level properties become hidden because they are no longer set at the component level.

Example component properties when a HierarchicalGrid data source is selected

Notice that settings such as Primary Table, Data Filter, and Enable Row Selection no longer display in the component properties. Instead, these properties are set individually for each grouping level, within the HierarchicalGrid data source. If any of the hidden properties are configured on the Form Control Sheet, they will be ignored when the data source is a HierarchicalGrid data source.

How the data in each grouping level is determined

When the Data Grid component is rendered in the form, it reads the grouping levels from the HierarchicalGrid data source. Essentially, each row in the data source is treated as a separate Data Grid component, with its own component properties and DataGridColumns data source. The top-level grid is the first row of the HierarchicalGrid data source. If you expand an item in this top-level grid, it displays a child grid based on the second row of the HierarchicalGrid data source, and so on.

When you expand a grouping to see the next grouping level underneath it, the data is filtered to only show the relevant rows for the expanded grouping. For example, if you have levels of Region > Dept, then when you expand region US West to see the Dept grouping level, you only see the departments that belong to that region.

This behavior is accomplished by applying a filter to the child grid for the second grouping level, based on the sum by column of the first grouping level. In this example, the Dept.Region column is the sum by column for the Region grouping level. When region US West is expanded, a filter of Dept.Region='US West' is applied to the child grid for the Dept level (in addition to any filter defined for that grouping level in the HierarchicalGrid data source).

This filter is compounded as you move down levels. For example, imagine that you have levels of WorldRegion > Region > Dept. You expand world region North America and then region US West to see the Dept level. Now the filter applied to the Dept child grid is Dept.WorldRegion='North America' and Dept.Region='US West'.

This behavior means that all of the grouping levels in your HierarchicalGrid data source must use compatible primary tables and use sum by columns that can be applied as filters to the lower grouping levels. If the sum by level of a parent grid cannot be applied as a filter to the primary table of a child grid, then an error will occur when the user attempts to expand the grouping level to see the child grid.

If the child table has multiple lookup paths to a sum by column in the parent grid, Axiom Software must determine which path to use. Paths are applied in the following priority order:

1. Single-level lookup path (if there is only one)
2. Key column lookup path (if there is only one)
3. Shortest lookup path length (if there is only one at the shortest length)
4. If no single lookup path can be found that matches these rules, then an error occurs.