Adding Subtotals

So far we have covered the creation of node rows and outline trees. To make the outlines really useful, however, the node rows should include summary information for the data they contain.

If you create an outline tree using the Subtotal method, then the subtotals are added automatically. This will be described in a later section.

If you created the outline tree using the Rows.InsertNode method as described above, then you should use the Aggregate method to calculate the subtotals for each group of rows and insert the result directly into the node rows.

The C1FlexGrid.Subtotal method listed below shows how to do this:

To write code in C#

C#	Copy Code
// add subtotals to each node at a given level void AddSubtotals(int level, string colName) { // get column we are going to total on int colIndex = _flex.Cols.IndexOf(colName); // scan rows looking for nodes at the right level for (int r = _flex.Rows.Fixed; r < _flex.Rows.Count; r++) { if (_flex.Rows[r].IsNode) { var node = _flex.Rows[r].Node; if (node.Level == level) { // found a node, calculate the sum of extended price var range = node.GetCellRange(); var sum = _flex.Aggregate(AggregateEnum.Sum, range.r1, colIndex, range.r2, colIndex, AggregateFlags.ExcludeNodes); // show the sum on the grid // (will use the column format automatically) _flex[r, colIndex] = sum; } } } }

The AddSubtotals method scans the grid rows looking for node rows. When a node row of the desired level is found, the method uses the GetCellRange method to retrieve the node's child rows. Then it uses the C1FlexGrid.Aggregate method to calculate the sum of the values on the target column over the entire range. The call to Aggregate includes the ExcludeNodes flag to avoid double-counting existing nodes. Once the subtotal has been calculated, it is assigned to the node row's cell with the usual _flex[row, col] indexer.

Note that this does not affect the data source in any way, since node rows are not bound to the data.

Note also that the method can be used to add multiple totals to each node row. In this example, we will add totals for the Quantity and ExtendedPrice columns. In addition to sums, you could add other aggregates such as average, maximum, minimum, etc.

We can now use this method to create a complete outline, with node rows, outline tree, and subtotals:

To write code in C#

C#	Copy Code
void _btnTreeCountryCity_Click(object sender, EventArgs e) { using (new DeferRefresh(_flex)) { // restore original sort (by Country, City, SalesPerson) ResetBinding(); // group by Country, City GroupBy("Country", 0); // group by country (level 0) GroupBy("City", 1); // group by city (level 1) // add totals per Country, City AddSubtotals(0, "ExtendedPrice"); // extended price per country (level 0) AddSubtotals(0, "Quantity"); // quantity per country (level 0) AddSubtotals(1, "ExtendedPrice"); // extended price per city (level 1) AddSubtotals(1, "Quantity"); // quantity per city (level 1) // show outline tree _flex.Tree.Column = 0; _flex.AutoSizeCol(_flex.Tree.Column); _flex.Tree.Show(1); } }

If you run the project now, you will see a tree with node rows that show the total quantity and amount sold for each country and city. This is very nice, but there is a little problem. If you expand any of the node rows, you will see a lot of duplicate values. All rows under a given city node have the same country and city:>

This is correct, but it is also a waste of screen real estate. Eliminating these duplicate values is easy; all you have to do is set the Width of the columns that are being grouped on to zero. When you do that, however, you should remember to set the grid's AllowMerging property to Nodes, so the text assigned to the node rows will spill into the visible columns. (Another option would be to assign the node text to the first visible column, but merging is usually a better solution because it allows you to use longer text for the node rows).

Here is the revised code and the final result:

To write code in C#

C#	Copy Code
void _btnTreeCountryCity_Click(object sender, EventArgs e) { using (new DeferRefresh(_flex)) { // restore original sort (by Country, City, SalesPerson) ResetBinding(); // group by Country, City GroupBy("Country", 0); // group by country (level 0) GroupBy("City", 1); // group by city (level 1) // hide columns that we grouped on // (they only have duplicate values which already appear on the tree nodes) // (but don't make them invisible, that would also hide the node text) _flex.Cols["Country"].Width = 0; _flex.Cols["City"].Width = 0; // allow node content to spill onto next cell _flex.AllowMerging = AllowMergingEnum.Nodes; // add totals per Country, City AddTotals(0, "ExtendedPrice"); // extended price per country (level 0) AddTotals(0, "Quantity"); // quantity per country (level 0) AddTotals(1, "ExtendedPrice"); // extended price per city (level 1) AddTotals(1, "Quantity"); // quantity per city (level 1) // show outline tree _flex.Tree.Column = 0; _flex.AutoSizeCol(_flex.Tree.Column); _flex.Tree.Show(1); } }

The Country and City columns are now invisible, but their values still appear in the node rows. Collapsing the tree shows totals for each country and city.