Totals by Hierarchy

You can calculate totals by hierarchy. To do so, you must indicate the HIERARCHY keyword after the name of the field the totals are calculated for. Totals by check points and totals by hierarchy for control points will be calculated in the result.

Example:

SELECT Doc.Product, Doc.Count, Doc.Reference.Number, Doc.Reference.Recipient
FROM Document.ExpInvoice.Contents AS Doc
ORDER BY Doc.Product
TOTALS SUM(Count) BY Doc.Product HIERARCHY

Query result:

Product	Count	Number	Recipient
Sanitaryware	104
Faucet	84
Faucet	10	001	Urupinsk products
Faucet	8	003	“Master” store
Faucet	44	004	“Giant” store
Faucet	22	005	Urupinsk products
Plunger	20
Plunger	5	001	Urupinsk products
Plunger	1	003	“Master” store
Plunger	14	004	“Giant” store
Furniture	134
Table	26
Table	1	001	Urupinsk products
Table	15	002	“Furniture” store
Table	10	004	“Giant” store
Chair	108
Chair	55	002	“Furniture” store
Chair	5	003	“Master” store
Chair	32	004	“Giant” store
Chair	16	005	Urupinsk products

If necessary, you can calculate totals of only values by hierarchy, without calculating totals in the check points. To do so, you must use the keyword ONLY before the keyword HIERARCHY.

Example:

SELECT Doc.Product, Doc.Count, Doc.Reference.Number, Doc.Reference.Recipient
FROM Document.ExpInvoice.Contents Doc
ORDER BY Doc.Product
TOTALS SUM(Count) BY Doc.Product ONLY HIERARCHY

Query result:

Product	Count	Number	Recipient
Sanitaryware	104
Faucet	10	001	Urupinsk products
Faucet	8	003	“Master” store
Faucet	44	004	“Giant” store
Faucet	22	005	Urupinsk products
Plunger	5	001	Urupinsk products
Plunger	1	003	“Master” store
Plunger	14	004	“Giant” store
Furniture	134
Table	1	001	Urupinsk products
Table	15	002	“Furniture” store
Table	10	004	“Giant” store
Chair	55	002	“Furniture” store
Chair	5	003	“Master” store
Chair	32	004	“Giant” store
Chair	16	005	Urupinsk products