A grouped data model includes a record set with a parent-child relationship. The nesting depth is not limited.
Unlike hierarchical (tree-like) models, in which parent-child relationships are defined using the parent record identifier, in the grouped models, relationships are defined based on repeated values.
Please note. The T-SQL GROUP BY ROLLUP clause is usually used to form grouped datasets.
For more information, see the Microsoft documentation.
A grouped model is represented by the !Group object type.
To be correct, each record must have two additional required fields:
!GroupMarker - the grouping markers. There will be as
many of them as many grouping levels will be used. In fact, they are
simply the T-SQL value of the grouping function for the
corresponding field. There will be as many such fields as are specified
in the group groupby rollup statement.
!Items - an array of child records. This value will always
be null in the recordset. The field is necessary for the system to build
a hierarchy based on the received data. Note that the data type of the
items must always be the same as the data type of the root object (with
the !Group modifier).
Due to the nature of the processing of models, you MUST first sort the recordset by grouping markers in reverse order, so that groups are displayed first.
Please note. If only one grouping level is specified for the model, then in
fact this means that you will simply calculate the totals for certain fields (those
that are displayed using the sum function).
The simplest way to demonstrate working with grouped models is with an example.
Suppose you have the Documents table looking as follows:
| Id | Date | Agent | Amount |
|---|---|---|---|
| 10 | 2021-05-01 | 10 | 150.00 |
| 11 | 2021-05-02 | 10 | 300.00 |
| 12 | 2021-05-01 | 20 | 320.00 |
| 13 | 2021-05-02 | 20 | 270.00 |
And the Agents table looking as follows:
| Id | Name |
|---|---|
| 10 | Agent 1 |
| 20 | Agent 2 |
Let's build a recordset with grouping by date and contractor.
As a result of this procedure the following recordset will be formed:
| ReportData!TData!Group | Agent | Date | Amount | Agent!!GroupMarker | Date!!GroupMarker | Items!TData!Items |
|---|---|---|---|---|---|---|
| null | null | null | 1040.00 | 1 | 1 | null |
| null | Agent 1 | null | 450.00 | 0 | 1 | null |
| null | Agent 2 | null | 590.00 | 0 | 1 | null |
| null | Agent 1 | 01.05.2020 | 150.00 | 0 | 0 | null |
| null | Agent 1 | 02.05.2020 | 300.00 | 0 | 0 | null |
| null | Agent 2 | 01.05.2020 | 320.00 | 0 | 0 | null |
| null | Agent 2 | 02.05.2020 | 270.00 | 0 | 0 | null |
The first three lines of the set represent the totals for the entire report (the first line) and for each of the countractors (the second and the third lines). Grouping markers show which field this total is for.
After processing this set, such a model will be formed (service properties are not shown for simplicity).
Note the $level and $groupName properties.
The $level - property - specifies the level in the tree
(from 0), and $groupName - - the group name - actually
simply selects one of the set values depending on the level.
You can display such a model, for example, using the Sheet object.
Result (display only, group convolution does not work)
| Agent/Date | Amount | |
| Total | 1 040,00 | |
| Agent 1 | 450,00 | |
| 01.05.2020 | 150,00 | |
| 02.05.2020 | 300,00 | |
| Agent 2 | 590,00 | |
| 01.05.2020 | 320,00 | |
| 02.05.2020 | 270,00 | |