Creating groups and summaries in Grid View
GridView has a lot of improvements over the DataGrid but it still lacks some very important features. A recurring requirement not available in the GridView is to create groups and summaries. To create summaries we can easily code the RowDataBound event. Grouping is a more complex task, and involves more coding and debugging. But when we need to combine these two features the things really start to get messy. With this in mind I have implemented the GridViewHelper, as the name suggests, a helper class to aid the creation of groups and summaries.
Using the GridViewHelper
Below we will see some GridViewHelper samples. First we show the grid to which the groups and summaries will be created. The sample data comes from Northwind database, with a few modifications:
To create a summary for the ItemTotal column we need only the promised 2 lines of code:
First we create the GridViewHelper setting the grid in which it will act in the constructor. Then we register the summary specifying the column name and the summary operation to be performed. The result is below:
In this sample a new line was added to display the summary. Another option is to use the footer row to display the summary instead of creating a new one. When a new row is added to the grid, only the required cells to display the summarized columns are created. Using the footer, all the cells are created. In case of group summaries, generation of all cells or only the needed cells is a group attribute.
Now we will create a group. The code is shown below:
The first parameter of RegisterGroup method defines the columns to which the group must be created. It's also possible to create a composite group, consisting of an array of columns. The second parameter specifies if the group is automatic. In this case a new row will be created automatically for the group header. The third parameter specifies if the group columns must be hidden. The ApplyGroupSort method sets the sort expression of the grid as being the group columns, in this case, ShipRegion. This is required to grouping works properly, except if the data comes ordered from database.
In the above sample the column ShipRegion have been hidden:
Let's make something more interesting, let's add a summary to the created group. We need just one more line to register the summary to the group:
This time, the RegisterSummary method takes another parameter. The parameter specifies the name of the group to which the summary must be created. Group name is automatically generated from the group column names. If the group has only one column, group name will be the name of that column. If the group has more than one column, the group name will be the ordered concatenation of the columns that composes the group, joined with a plus sign ("+"): "ShipRegion+ShipName".
We can see below the grid with grouping and a summary for the group:
It's possible to create more than one group in the grid, simulating a hierarchical grouping, as seen below:
Result:
Visualization is compromised when there is more than one group. GridViewHelper has events to allow easy implementation of visual or functional adjusts. The list of events follows below:
With a few more lines of code we can improve the visual aspect of the grid:
The grid after the cosmetics:
More grouping options
There are two more interesting samples. The first presents a composite group. The second defines a suppress group, that has the same behavior of the sql GROUP BY clause. The repeating values are suppressed, and a summary operation is performed on the other columns.
Below we can see the code and the grid appearance for the composite group:
We can add a summary to the group. This time we will define an average operation and add a label to indicate the operation:
Using the GridViewHelper
Below we will see some GridViewHelper samples. First we show the grid to which the groups and summaries will be created. The sample data comes from Northwind database, with a few modifications:
| ShipRegion | ShipName | OrderId | ProductName | Quantity | UnitPrice | ItemTotal |
|---|---|---|---|---|---|---|
| NM | Old World Delicatessen | 10922 | Alice Mutton | 15 | US$ 39,00 | US$ 585,00 |
| NM | Old World Delicatessen | 10922 | Teatime Chocolate Biscuits | 35 | US$ 4,50 | US$ 157,50 |
| NM | Old World Delicatessen | 10925 | Inlagd Sill | 25 | US$ 19,00 | US$ 475,00 |
| NM | Old World Delicatessen | 10925 | Filo Mix | 12 | US$ 7,00 | US$ 84,00 |
| OR | Save-a-lot Markets | 10935 | Chai | 21 | US$ 18,00 | US$ 378,00 |
| OR | Save-a-lot Markets | 10935 | Carnarvon Tigers | 4 | US$ 62,50 | US$ 250,00 |
| OR | Save-a-lot Markets | 10935 | Tunnbröd | 8 | US$ 9,00 | US$ 72,00 |
| OR | Gourmet Lanchonetes | 10959 | Rhönbräu Klosterbier | 20 | US$ 7,75 | US$ 155,00 |
| OR | Split Rail Beer & Ale | 10961 | Filo Mix | 6 | US$ 7,00 | US$ 42,00 |
| OR | Split Rail Beer & Ale | 10961 | Lakkalikööri | 60 | US$ 18,00 | US$ 1.080,00 |
| OR | Lazy K Kountry Store | 10969 | Spegesild | 9 | US$ 12,00 | US$ 108,00 |
| NM | Old World Delicatessen | 10981 | Côte de Blaye | 60 | US$ 263,50 | US$ 15.810,00 |
| NM | White Clover Markets | 10989 | Grandma's Boysenberry Spread | 40 | US$ 25,00 | US$ 1.000,00 |
| NM | White Clover Markets | 10989 | Queso Cabrales | 15 | US$ 21,00 | US$ 315,00 |
| NM | White Clover Markets | 10989 | Camembert Pierrot | 4 | US$ 9,65 | US$ 38,60 |
To create a summary for the ItemTotal column we need only the promised 2 lines of code:
protected void Page_Load(object sender, EventArgs e)
{
GridViewHelper helper = new GridViewHelper(this.GridView1);
helper.RegisterSummary("ItemTotal", SummaryOperation.Sum);
}
{
GridViewHelper helper = new GridViewHelper(this.GridView1);
helper.RegisterSummary("ItemTotal", SummaryOperation.Sum);
}
First we create the GridViewHelper setting the grid in which it will act in the constructor. Then we register the summary specifying the column name and the summary operation to be performed. The result is below:
| ShipRegion | ShipName | OrderId | ProductName | Quantity | UnitPrice | ItemTotal |
|---|---|---|---|---|---|---|
| NM | Old World Delicatessen | 10922 | Alice Mutton | 15 | US$ 39,00 | US$ 585,00 |
| NM | Old World Delicatessen | 10922 | Teatime Chocolate Biscuits | 35 | US$ 4,50 | US$ 157,50 |
| NM | Old World Delicatessen | 10925 | Inlagd Sill | 25 | US$ 19,00 | US$ 475,00 |
| NM | Old World Delicatessen | 10925 | Filo Mix | 12 | US$ 7,00 | US$ 84,00 |
| OR | Save-a-lot Markets | 10935 | Chai | 21 | US$ 18,00 | US$ 378,00 |
| OR | Save-a-lot Markets | 10935 | Carnarvon Tigers | 4 | US$ 62,50 | US$ 250,00 |
| OR | Save-a-lot Markets | 10935 | Tunnbröd | 8 | US$ 9,00 | US$ 72,00 |
| OR | Gourmet Lanchonetes | 10959 | Rhönbräu Klosterbier | 20 | US$ 7,75 | US$ 155,00 |
| OR | Split Rail Beer & Ale | 10961 | Filo Mix | 6 | US$ 7,00 | US$ 42,00 |
| OR | Split Rail Beer & Ale | 10961 | Lakkalikööri | 60 | US$ 18,00 | US$ 1.080,00 |
| OR | Lazy K Kountry Store | 10969 | Spegesild | 9 | US$ 12,00 | US$ 108,00 |
| NM | Old World Delicatessen | 10981 | Côte de Blaye | 60 | US$ 263,50 | US$ 15.810,00 |
| NM | White Clover Markets | 10989 | Grandma's Boysenberry Spread | 40 | US$ 25,00 | US$ 1.000,00 |
| NM | White Clover Markets | 10989 | Queso Cabrales | 15 | US$ 21,00 | US$ 315,00 |
| NM | White Clover Markets | 10989 | Camembert Pierrot | 4 | US$ 9,65 | US$ 38,60 |
| US$ 20.550,10 | ||||||
In this sample a new line was added to display the summary. Another option is to use the footer row to display the summary instead of creating a new one. When a new row is added to the grid, only the required cells to display the summarized columns are created. Using the footer, all the cells are created. In case of group summaries, generation of all cells or only the needed cells is a group attribute.
Now we will create a group. The code is shown below:
protected void Page_Load(object sender, EventArgs e)
{
GridViewHelper helper = new GridViewHelper(this.GridView1);
helper.RegisterGroup("ShipRegion", true, true);
helper.ApplyGroupSort();
}
{
GridViewHelper helper = new GridViewHelper(this.GridView1);
helper.RegisterGroup("ShipRegion", true, true);
helper.ApplyGroupSort();
}
The first parameter of RegisterGroup method defines the columns to which the group must be created. It's also possible to create a composite group, consisting of an array of columns. The second parameter specifies if the group is automatic. In this case a new row will be created automatically for the group header. The third parameter specifies if the group columns must be hidden. The ApplyGroupSort method sets the sort expression of the grid as being the group columns, in this case, ShipRegion. This is required to grouping works properly, except if the data comes ordered from database.
In the above sample the column ShipRegion have been hidden:
| ShipName | OrderId | ProductName | Quantity | UnitPrice | ItemTotal |
|---|---|---|---|---|---|
| NM | |||||
| Old World Delicatessen | 10922 | Alice Mutton | 15 | US$ 39,00 | US$ 585,00 |
| Old World Delicatessen | 10922 | Teatime Chocolate Biscuits | 35 | US$ 4,50 | US$ 157,50 |
| Old World Delicatessen | 10925 | Inlagd Sill | 25 | US$ 19,00 | US$ 475,00 |
| Old World Delicatessen | 10925 | Filo Mix | 12 | US$ 7,00 | US$ 84,00 |
| Old World Delicatessen | 10981 | Côte de Blaye | 60 | US$ 263,50 | US$ 15.810,00 |
| White Clover Markets | 10989 | Grandma's Boysenberry Spread | 40 | US$ 25,00 | US$ 1.000,00 |
| White Clover Markets | 10989 | Queso Cabrales | 15 | US$ 21,00 | US$ 315,00 |
| White Clover Markets | 10989 | Camembert Pierrot | 4 | US$ 9,65 | US$ 38,60 |
| OR | |||||
| Save-a-lot Markets | 10935 | Chai | 21 | US$ 18,00 | US$ 378,00 |
| Save-a-lot Markets | 10935 | Carnarvon Tigers | 4 | US$ 62,50 | US$ 250,00 |
| Save-a-lot Markets | 10935 | Tunnbröd | 8 | US$ 9,00 | US$ 72,00 |
| Gourmet Lanchonetes | 10959 | Rhönbräu Klosterbier | 20 | US$ 7,75 | US$ 155,00 |
| Split Rail Beer & Ale | 10961 | Filo Mix | 6 | US$ 7,00 | US$ 42,00 |
| Split Rail Beer & Ale | 10961 | Lakkalikööri | 60 | US$ 18,00 | US$ 1.080,00 |
| Lazy K Kountry Store | 10969 | Spegesild | 9 | US$ 12,00 | US$ 108,00 |
Let's make something more interesting, let's add a summary to the created group. We need just one more line to register the summary to the group:
protected void Page_Load(object sender, EventArgs e)
{
GridViewHelper helper = new GridViewHelper(this.GridView1);
helper.RegisterGroup("ShipRegion", true, true);
helper.RegisterSummary("ItemTotal", SummaryOperation.Sum, "ShipRegion");
helper.ApplyGroupSort();
}
{
GridViewHelper helper = new GridViewHelper(this.GridView1);
helper.RegisterGroup("ShipRegion", true, true);
helper.RegisterSummary("ItemTotal", SummaryOperation.Sum, "ShipRegion");
helper.ApplyGroupSort();
}
This time, the RegisterSummary method takes another parameter. The parameter specifies the name of the group to which the summary must be created. Group name is automatically generated from the group column names. If the group has only one column, group name will be the name of that column. If the group has more than one column, the group name will be the ordered concatenation of the columns that composes the group, joined with a plus sign ("+"): "ShipRegion+ShipName".
We can see below the grid with grouping and a summary for the group:
| ShipName | OrderId | ProductName | Quantity | UnitPrice | ItemTotal |
|---|---|---|---|---|---|
| NM | |||||
| Old World Delicatessen | 10922 | Alice Mutton | 15 | US$ 39,00 | US$ 585,00 |
| Old World Delicatessen | 10922 | Teatime Chocolate Biscuits | 35 | US$ 4,50 | US$ 157,50 |
| Old World Delicatessen | 10925 | Inlagd Sill | 25 | US$ 19,00 | US$ 475,00 |
| Old World Delicatessen | 10925 | Filo Mix | 12 | US$ 7,00 | US$ 84,00 |
| Old World Delicatessen | 10981 | Côte de Blaye | 60 | US$ 263,50 | US$ 15.810,00 |
| White Clover Markets | 10989 | Grandma's Boysenberry Spread | 40 | US$ 25,00 | US$ 1.000,00 |
| White Clover Markets | 10989 | Queso Cabrales | 15 | US$ 21,00 | US$ 315,00 |
| White Clover Markets | 10989 | Camembert Pierrot | 4 | US$ 9,65 | US$ 38,60 |
| US$ 18.465,10 | |||||
| OR | |||||
| Save-a-lot Markets | 10935 | Chai | 21 | US$ 18,00 | US$ 378,00 |
| Save-a-lot Markets | 10935 | Carnarvon Tigers | 4 | US$ 62,50 | US$ 250,00 |
| Save-a-lot Markets | 10935 | Tunnbröd | 8 | US$ 9,00 | US$ 72,00 |
| Gourmet Lanchonetes | 10959 | Rhönbräu Klosterbier | 20 | US$ 7,75 | US$ 155,00 |
| Split Rail Beer & Ale | 10961 | Filo Mix | 6 | US$ 7,00 | US$ 42,00 |
| Split Rail Beer & Ale | 10961 | Lakkalikööri | 60 | US$ 18,00 | US$ 1.080,00 |
| Lazy K Kountry Store | 10969 | Spegesild | 9 | US$ 12,00 | US$ 108,00 |
| US$ 2.085,00 | |||||
It's possible to create more than one group in the grid, simulating a hierarchical grouping, as seen below:
protected void Page_Load(object sender, EventArgs e)
{
GridViewHelper helper = new GridViewHelper(this.GridView1);
helper.RegisterGroup("ShipRegion", true, true);
helper.RegisterGroup("ShipName", true, true);
helper.ApplyGroupSort();
}
{
GridViewHelper helper = new GridViewHelper(this.GridView1);
helper.RegisterGroup("ShipRegion", true, true);
helper.RegisterGroup("ShipName", true, true);
helper.ApplyGroupSort();
}
Result:
| OrderId | ProductName | Quantity | UnitPrice | ItemTotal |
|---|---|---|---|---|
| NM | ||||
| Old World Delicatessen | ||||
| 10922 | Alice Mutton | 15 | US$ 39,00 | US$ 585,00 |
| 10922 | Teatime Chocolate Biscuits | 35 | US$ 4,50 | US$ 157,50 |
| 10925 | Inlagd Sill | 25 | US$ 19,00 | US$ 475,00 |
| 10925 | Filo Mix | 12 | US$ 7,00 | US$ 84,00 |
| 10981 | Côte de Blaye | 60 | US$ 263,50 | US$ 15.810,00 |
| White Clover Markets | ||||
| 10989 | Grandma's Boysenberry Spread | 40 | US$ 25,00 | US$ 1.000,00 |
| 10989 | Queso Cabrales | 15 | US$ 21,00 | US$ 315,00 |
| 10989 | Camembert Pierrot | 4 | US$ 9,65 | US$ 38,60 |
| OR | ||||
| Lazy K Kountry Store | ||||
| 10969 | Spegesild | 9 | US$ 12,00 | US$ 108,00 |
| Gourmet Lanchonetes | ||||
| 10959 | Rhönbräu Klosterbier | 20 | US$ 7,75 | US$ 155,00 |
| Split Rail Beer & Ale | ||||
| 10961 | Filo Mix | 6 | US$ 7,00 | US$ 42,00 |
| 10961 | Lakkalikööri | 60 | US$ 18,00 | US$ 1.080,00 |
| Save-a-lot Markets | ||||
| 10935 | Chai | 21 | US$ 18,00 | US$ 378,00 |
| 10935 | Carnarvon Tigers | 4 | US$ 62,50 | US$ 250,00 |
| 10935 | Tunnbröd | 8 | US$ 9,00 | US$ 72,00 |
Visualization is compromised when there is more than one group. GridViewHelper has events to allow easy implementation of visual or functional adjusts. The list of events follows below:
| GroupStart | Occurs when a new group starts, it means, when new values are found in the group column. |
| GroupEnd | Occurs in the last row of the group |
| GroupHeader | Occurs when an automatic header row is added for the group. The event is not triggered if the group is not automatic. |
| GroupSummary | Occurs when the summary row is generated for the group. The event is not triggered if the group is not automatic, but will be triggered if the group is a suppression group (will be seen later on). |
| GeneralSummary | Occurs after the general summaries be calculated. If the summary is automatic the event occurs after the summary row be added and after the summary values be placed in the row. |
| FooterDataBound | Occurs in the footer databinding. |
With a few more lines of code we can improve the visual aspect of the grid:
protected void Page_Load(object sender, EventArgs e)
{
GridViewHelper helper = new GridViewHelper(this.GridView1);
helper.RegisterGroup("ShipRegion", true, true);
helper.RegisterGroup("ShipName", true, true);
helper.GroupHeader += new GroupEvent(helper_GroupHeader);
helper.ApplyGroupSort();
}
private void helper_GroupHeader(string groupName, object[] values, GridViewRow row)
{
if ( groupName == "ShipRegion" )
{
row.BackColor = Color.LightGray;
row.Cells[0].Text = " " + row.Cells[0].Text;
}
else if (groupName == "ShipName")
{
row.BackColor = Color.FromArgb(236, 236, 236);
row.Cells[0].Text = " " + row.Cells[0].Text;
}
}
{
GridViewHelper helper = new GridViewHelper(this.GridView1);
helper.RegisterGroup("ShipRegion", true, true);
helper.RegisterGroup("ShipName", true, true);
helper.GroupHeader += new GroupEvent(helper_GroupHeader);
helper.ApplyGroupSort();
}
private void helper_GroupHeader(string groupName, object[] values, GridViewRow row)
{
if ( groupName == "ShipRegion" )
{
row.BackColor = Color.LightGray;
row.Cells[0].Text = " " + row.Cells[0].Text;
}
else if (groupName == "ShipName")
{
row.BackColor = Color.FromArgb(236, 236, 236);
row.Cells[0].Text = " " + row.Cells[0].Text;
}
}
The grid after the cosmetics:
| OrderId | ProductName | Quantity | UnitPrice | ItemTotal |
|---|---|---|---|---|
| NM | ||||
| Old World Delicatessen | ||||
| 10922 | Alice Mutton | 15 | US$ 39,00 | US$ 585,00 |
| 10922 | Teatime Chocolate Biscuits | 35 | US$ 4,50 | US$ 157,50 |
| 10925 | Inlagd Sill | 25 | US$ 19,00 | US$ 475,00 |
| 10925 | Filo Mix | 12 | US$ 7,00 | US$ 84,00 |
| 10981 | Côte de Blaye | 60 | US$ 263,50 | US$ 15.810,00 |
| White Clover Markets | ||||
| 10989 | Grandma's Boysenberry Spread | 40 | US$ 25,00 | US$ 1.000,00 |
| 10989 | Queso Cabrales | 15 | US$ 21,00 | US$ 315,00 |
| 10989 | Camembert Pierrot | 4 | US$ 9,65 | US$ 38,60 |
| OR | ||||
| Lazy K Kountry Store | ||||
| 10969 | Spegesild | 9 | US$ 12,00 | US$ 108,00 |
| Gourmet Lanchonetes | ||||
| 10959 | Rhönbräu Klosterbier | 20 | US$ 7,75 | US$ 155,00 |
| Split Rail Beer & Ale | ||||
| 10961 | Filo Mix | 6 | US$ 7,00 | US$ 42,00 |
| 10961 | Lakkalikööri | 60 | US$ 18,00 | US$ 1.080,00 |
| Save-a-lot Markets | ||||
| 10935 | Chai | 21 | US$ 18,00 | US$ 378,00 |
| 10935 | Carnarvon Tigers | 4 | US$ 62,50 | US$ 250,00 |
| 10935 | Tunnbröd | 8 | US$ 9,00 | US$ 72,00 |
More grouping options
There are two more interesting samples. The first presents a composite group. The second defines a suppress group, that has the same behavior of the sql GROUP BY clause. The repeating values are suppressed, and a summary operation is performed on the other columns.
Below we can see the code and the grid appearance for the composite group:
protected void Page_Load(object sender, EventArgs e)
{
GridViewHelper helper = new GridViewHelper(this.GridView1);
string[] cols = new string[2];
cols[0] = "ShipRegion";
cols[1] = "ShipName";
helper.RegisterGroup(cols, true, true);
helper.ApplyGroupSort();
}
{
GridViewHelper helper = new GridViewHelper(this.GridView1);
string[] cols = new string[2];
cols[0] = "ShipRegion";
cols[1] = "ShipName";
helper.RegisterGroup(cols, true, true);
helper.ApplyGroupSort();
}
| OrderId | ProductName | Quantity | UnitPrice | ItemTotal |
|---|---|---|---|---|
| NM - Old World Delicatessen | ||||
| 10922 | Alice Mutton | 15 | US$ 39,00 | US$ 585,00 |
| 10922 | Teatime Chocolate Biscuits | 35 | US$ 4,50 | US$ 157,50 |
| 10925 | Inlagd Sill | 25 | US$ 19,00 | US$ 475,00 |
| 10925 | Filo Mix | 12 | US$ 7,00 | US$ 84,00 |
| 10981 | Côte de Blaye | 60 | US$ 263,50 | US$ 15.810,00 |
| NM - White Clover Markets | ||||
| 10989 | Grandma's Boysenberry Spread | 40 | US$ 25,00 | US$ 1.000,00 |
| 10989 | Queso Cabrales | 15 | US$ 21,00 | US$ 315,00 |
| 10989 | Camembert Pierrot | 4 | US$ 9,65 | US$ 38,60 |
| OR - Lazy K Kountry Store | ||||
| 10969 | Spegesild | 9 | US$ 12,00 | US$ 108,00 |
| OR - Gourmet Lanchonetes | ||||
| 10959 | Rhönbräu Klosterbier | 20 | US$ 7,75 | US$ 155,00 |
| OR - Split Rail Beer & Ale | ||||
| 10961 | Filo Mix | 6 | US$ 7,00 | US$ 42,00 |
| 10961 | Lakkalikööri | 60 | US$ 18,00 | US$ 1.080,00 |
| OR - Save-a-lot Markets | ||||
| 10935 | Chai | 21 | US$ 18,00 | US$ 378,00 |
| 10935 | Carnarvon Tigers | 4 | US$ 62,50 | US$ 250,00 |
| 10935 | Tunnbröd | 8 | US$ 9,00 | US$ 72,00 |
We can add a summary to the group. This time we will define an average operation and add a label to indicate the operation:
thank you
ReplyDelete