Friday, 25 January 2013

Group Total and Grand Total in GridView


In last post, I had posted a post on Group Total and Grand Total on GridView, which displays records in groups and provides the total of each group at the end of the group. In this post, I am planning to include +, - buttons on each group totals which helps to analyze the records easily.

For more understanding, here is the use case of the requirement –
  1. The records defined in the XML should bound to the Grid View in a normal way.
  2. The records should be grouped by Customer Name in first level and Order ID in second level and the Group Total for end of the each group should show.
  3. When Group Total shows at end of each group, the Customer Name and the Order ID information should show in addition to the group total.
  4. As there are two levels of grouping, the Order ID group should show little indent to the Customer Name and the actual value (data row) should show indent to the Order Id.
  5. The Customer Name, Order ID groups must be displayed with different background color to differentiate the groups.
  6. The Grand Total of all the records should be shown after all the records in the Grid.
  7. There must also be +, - buttons on each group total which helps to hide that particular group and make it visible.
  8. There should also be a +, - button at the grand total which used to hide and show all the rows and show only group total.
  9. There is also need to show +, - buttons on the header of each group column which can be used to show and hide the groups at that level for the whole rows.
This requirement talks about having two different groups, Customer Name is the first group and Order ID is the second group. So the grid will have one or more Customer Name as groups, each Customer Name group will have one or more Order ID as group. Actual data row will be displayed under each Order ID.

At the end of the Order ID group, the Order ID value will be displayed with some additional information such as Order Date, Delivery Date. At the end of the Customer Name group, the Customer Name value will be displayed with the Customer ID. Every group will have Group Total.

Before going for actual implementation, please note the following points -
  1. To implement these examples, all the records must show in a single page of the Grid View (So, no pagination). Because for calculating the Group Total and Grand Total, the code required all the records must be in loop.
  2. The records must be sorted on the group wise. So all the records related to a particular group will show one after another. It will be useful for calculating cumulative values together. Keeping records in different group will be considered as separate group and cumulative values will be calculated as another separate group. As we have two groups in this example, we must sort by Customer Name at first and then Order ID next.
In this page, I had provided two level of grouping as explained before. For more grouping, I provided in the downloadable source code at the end.

The implementation goes as below –

The XML source which bond to the GridView
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
<?xml version="1.0" encoding="utf-8" ?>
<Orders>
   
  <Order CustomerID="ALFKI" CompanyName="Alfreds Futterkiste"
         OrderID="10643" OrderDate="12-Apr-2012" DeliveryDate="15-Apr-2012" ContactTitle="Sales Representative" Address="Obere Str. 5711" City="Berlin" Country="Germany" Phone="030-0074321" Fax="030-0076545"
         ProductID="28" ProductName="Rössle Sauerkraut" UnitPrice="45.60" Quantity="15" Discount="0.25" Amount="683.75" />
  <Order CustomerID="ALFKI" CompanyName="Alfreds Futterkiste"
         OrderID="10643" OrderDate="12-Apr-2012" DeliveryDate="15-Apr-2012" ContactTitle="Sales Representative" Address="Obere Str. 5711" City="Berlin" Country="Germany" Phone="030-0074321" Fax="030-0076545"
         ProductID="39" ProductName="Chartreuse verte" UnitPrice="18.00" Quantity="21" Discount="0.25" Amount="377.75"/>
  <Order CustomerID="ALFKI" CompanyName="Alfreds Futterkiste"
         OrderID="10643" OrderDate="12-Apr-2012" DeliveryDate="15-Apr-2012" ContactTitle="Sales Representative" Address="Obere Str. 5711" City="Berlin" Country="Germany" Phone="030-0074321" Fax="030-0076545"
         ProductID="46" ProductName="Spegesild" UnitPrice="12.00" Quantity="2" Discount="0.25" Amount="23.75"/>
  <Order CustomerID="ALFKI" CompanyName="Alfreds Futterkiste"
         OrderID="10692" OrderDate="21-May-2012" DeliveryDate="23-May-2012" ContactTitle="Sales Representative" Address="Obere Str. 5711" City="Berlin" Country="Germany" Phone="030-0074321" Fax="030-0076545"
         ProductID="63" ProductName="Vegie-spread" UnitPrice="43.90" Quantity="20" Discount="0.00" Amount="878.00"/>
  <Order CustomerID="ALFKI" CompanyName="Alfreds Futterkiste"
         OrderID="10702" OrderDate="01-Jun-2012" DeliveryDate="15-Jun-2012" ContactTitle="Sales Representative" Address="Obere Str. 5711" City="Berlin" Country="Germany" Phone="030-0074321" Fax="030-0076545"
         ProductID="3" ProductName="Aniseed Syrup" UnitPrice="10.00" Quantity="6" Discount="0.00" Amount="60.00"/>
   
  <Order CustomerID="ANATR" CompanyName="Ana Trujillo Emparedados y helados"
         OrderID="10308" OrderDate="11-Aug-2012" DeliveryDate="21-Sep-2012" ContactTitle="Owner" Address="Avda. de la Constitución 2222" City="México D.F." Country="Mexico" Phone="(5) 555-4729" Fax="(5) 555-3745"
         ProductID="69" ProductName="Gudbrandsdalsost" UnitPrice="28.80" Quantity="1" Discount="0.00" Amount="28.80"/>
  <Order CustomerID="ANATR" CompanyName="Ana Trujillo Emparedados y helados"
         OrderID="10308" OrderDate="11-Aug-2012" DeliveryDate="21-Sep-2012" ContactTitle="Owner" Address="Avda. de la Constitución 2222" City="México D.F." Country="Mexico" Phone="(5) 555-4729" Fax="(5) 555-3745"
         ProductID="70" ProductName="Outback Lager" UnitPrice="12.00" Quantity="5" Discount="0.00" Amount="60.00"/>
  <Order CustomerID="ANATR" CompanyName="Ana Trujillo Emparedados y helados"
         OrderID="10926" OrderDate="01-May-2012" DeliveryDate="12-Jun-2012" ContactTitle="Owner" Address="Avda. de la Constitución 2222" City="México D.F." Country="Mexico" Phone="(5) 555-4729" Fax="(5) 555-3745"
         ProductID="72" ProductName="Mozzarella di Giovanni" UnitPrice="34.80" Quantity="10" Discount="0.00" Amount="348.00"/>
   
  <Order CustomerID="AROUT" CompanyName="Around the Horn"
         OrderID="10927" OrderDate="11-Aug-2012" DeliveryDate="21-Sep-2012" ContactTitle="Sales Representative" Address="120 Hanover Sq." City="London" Country="UK" Phone="(171) 555-7788" Fax="(171) 555-6750"
         ProductID="24" ProductName="Guaraná Fantástica" UnitPrice="3.60" Quantity="25" Discount="0.00" Amount="90.00"/>
  <Order CustomerID="AROUT" CompanyName="Around the Horn"
         OrderID="10927" OrderDate="11-Aug-2012" DeliveryDate="21-Sep-2012" ContactTitle="Sales Representative" Address="120 Hanover Sq." City="London" Country="UK" Phone="(171) 555-7788" Fax="(171) 555-6750"
         ProductID="31" ProductName="Gorgonzola Telino" UnitPrice="12.50" Quantity="50" Discount="0.05" Amount="624.95"/>
  <Order CustomerID="AROUT" CompanyName="Around the Horn"
         OrderID="11016" OrderDate="15-May-2012" DeliveryDate="15-Jun-2012" ContactTitle="Sales Representative" Address="120 Hanover Sq." City="London" Country="UK" Phone="(171) 555-7788" Fax="(171) 555-6750"
         ProductID="31" ProductName="Gorgonzola Telino" UnitPrice="12.50" Quantity="15" Discount="0.00" Amount="187.50"/>
  <Order CustomerID="AROUT" CompanyName="Around the Horn"
         OrderID="11016" OrderDate="15-May-2012" DeliveryDate="15-Jun-2012" ContactTitle="Sales Representative" Address="120 Hanover Sq." City="London" Country="UK" Phone="(171) 555-7788" Fax="(171) 555-6750"
         ProductID="36" ProductName="Inlagd Sill" UnitPrice="19.00" Quantity="16" Discount="0.00" Amount="304.00"/>
   
  <Order CustomerID="BERGS" CompanyName="Berglunds snabbköp"
         OrderID="10278" OrderDate="02-Nov-2012" DeliveryDate="15-Nov-2012" ContactTitle="Order Administrator" Address="Berguvsvägen  8" City="Luleå" Country="Sweden" Phone="0921-12 34 65" Fax="0921-12 34 67"
         ProductID="44" ProductName="Gula Malacca" UnitPrice="15.50" Quantity="16" Discount="0.00" Amount="248.00"/>
  <Order CustomerID="BERGS" CompanyName="Berglunds snabbköp"
         OrderID="10278" OrderDate="02-Nov-2012" DeliveryDate="15-Nov-2012" ContactTitle="Order Administrator" Address="Berguvsvägen  8" City="Luleå" Country="Sweden" Phone="0921-12 34 65" Fax="0921-12 34 67"
         ProductID="59" ProductName="Raclette Courdavault" UnitPrice="44.00" Quantity="15" Discount="0.00" Amount="660.00"/>
  <Order CustomerID="BERGS" CompanyName="Berglunds snabbköp"
         OrderID="10278" OrderDate="02-Nov-2012" DeliveryDate="15-Nov-2012" ContactTitle="Order Administrator" Address="Berguvsvägen  8" City="Luleå" Country="Sweden" Phone="0921-12 34 65" Fax="0921-12 34 67"
         ProductID="63" ProductName="Vegie-spread" UnitPrice="35.10" Quantity="8" Discount="0.00" Amount="280.80"/>
  <Order CustomerID="BERGS" CompanyName="Berglunds snabbköp"
         OrderID="10278" OrderDate="02-Nov-2012" DeliveryDate="15-Nov-2012" ContactTitle="Order Administrator" Address="Berguvsvägen  8" City="Luleå" Country="Sweden" Phone="0921-12 34 65" Fax="0921-12 34 67"
         ProductID="73" ProductName="Röd Kaviar" UnitPrice="12.00" Quantity="25" Discount="0.00" Amount="300.00"/>
  <Order CustomerID="BERGS" CompanyName="Berglunds snabbköp"
         OrderID="10280" OrderDate="15-Apr-2012" DeliveryDate="07-Jul-2012" ContactTitle="Order Administrator" Address="Berguvsvägen  8" City="Luleå" Country="Sweden" Phone="0921-12 34 65" Fax="0921-12 34 67"
         ProductID="24" ProductName="Guaraná Fantástica" UnitPrice="3.60" Quantity="12" Discount="0.00" Amount="43.20"/>
</Orders>
The ASPX script
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
<asp:GridView ID="grdViewOrders" runat="server" AutoGenerateColumns="False" TabIndex="1"
    Width="100%" DataSourceID="XmlDataSource1" CssClass="grdViewOrders"
    CellPadding="4" ForeColor="Black" GridLines="Vertical" BackColor="White"
    BorderColor="Black" BorderStyle="Solid" BorderWidth="1px"
    OnRowDataBound="grdViewOrders_RowDataBound"
    OnRowCreated="grdViewOrders_RowCreated" >           
    <Columns>
        <asp:TemplateField HeaderText="">
            <ItemStyle Width="10px" CssClass="DataCell" BorderStyle="Solid" BorderWidth="1" />
            <ItemTemplate></ItemTemplate>
            <HeaderStyle CssClass="DataCell" Width="10px" />
        </asp:TemplateField>
        <asp:TemplateField HeaderText="">
            <ItemStyle Width="10px" CssClass="DataCell" />
            <ItemTemplate></ItemTemplate>
            <HeaderStyle CssClass="DataCell" Width="10px" />
        </asp:TemplateField>
        <asp:TemplateField HeaderText="">
            <ItemStyle Width="10px" CssClass="DataCell" />
            <ItemTemplate></ItemTemplate>
            <HeaderStyle CssClass="DataCell" Width="10px" />
        </asp:TemplateField>
                 
        <asp:BoundField DataField="ProductName" HeaderText="ProductName">
            <ItemStyle HorizontalAlign="Left" CssClass="DataCell"></ItemStyle>
            <HeaderStyle CssClass="DataCell" />
        </asp:BoundField>
        <asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice" DataFormatString="{0:c}">
            <ItemStyle HorizontalAlign="Right" CssClass="DataCell"></ItemStyle>
            <HeaderStyle CssClass="DataCell" />
        </asp:BoundField>
        <asp:BoundField DataField="Quantity" HeaderText="Quantity" DataFormatString="{0:c}">
            <ItemStyle HorizontalAlign="Right" CssClass="DataCell"></ItemStyle>
            <HeaderStyle CssClass="DataCell" />
        </asp:BoundField>
        <asp:BoundField DataField="Discount" HeaderText="Discount" DataFormatString="{0:c}">
            <ItemStyle HorizontalAlign="Right" CssClass="DataCell"></ItemStyle>
            <HeaderStyle CssClass="DataCell" />
        </asp:BoundField>
        <asp:BoundField DataField="Amount" HeaderText="Amount" DataFormatString="{0:c}">
            <ItemStyle HorizontalAlign="Right" CssClass="DataCell"></ItemStyle>
            <HeaderStyle CssClass="DataCell" />
        </asp:BoundField>
    </Columns>
    <RowStyle BackColor="#F7F7DE" BorderStyle="Solid" BorderWidth="1px" BorderColor="Black" />
    <FooterStyle BackColor="#CCCC99" />
    <PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />
    <SelectedRowStyle BackColor="#CE5D5A" ForeColor="White" Font-Bold="True" />
    <HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" BorderStyle="Solid" BorderWidth="1px" BorderColor="Black" />
    <AlternatingRowStyle BackColor="White" BorderStyle="Solid" BorderWidth="1px" BorderColor="Black" />
    <SortedAscendingCellStyle BackColor="#FBFBF2" />
    <SortedAscendingHeaderStyle BackColor="#848384" />
    <SortedDescendingCellStyle BackColor="#EAEAD3" />
    <SortedDescendingHeaderStyle BackColor="#575357" />
</asp:GridView>
<asp:XmlDataSource ID="XmlDataSource1" runat="server" DataFile="Data/Orders.xml"></asp:XmlDataSource>
The C# Code behind
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
// To keep track of the previous row Group Identifier
string strPreviousRowCustomerID = string.Empty; // First Level Grouping Track Id (Used to identify the group is getting changed)
string strPreviousRowOrderID = string.Empty; // Second Level Grouping Track Id (Used to identify the group are getting changed)
 
// To keep track the Index of Group Total
int intSubTotalIndex = 0; // For increasing the row count (for inserting a row in the current row)
 
string strCustomerGroupHeaderText = string.Empty;
string strOrderGroupHeaderText = string.Empty;
 
// To customer temporarily store Sub Total - First Level Grouping (Declare variables for sum, count columns)
double dblCustomerGroupSubTotalUnitPrice = 0;
double dblCustomerGroupSubTotalQuantity = 0;
double dblCustomerGroupSubTotalDiscount = 0;
double dblCustomerGroupSubTotalAmount = 0;
 
// To order temporarily store Sub Total - Second Level Grouping
double dblOrderGroupSubTotalUnitPrice = 0;
double dblOrderGroupSubTotalQuantity = 0;
double dblOrderGroupSubTotalDiscount = 0;
double dblOrderGroupSubTotalAmount = 0;
 
// To temporarily store Grand Total
double dblGrandTotalUnitPrice = 0;
double dblGrandTotalQuantity = 0;
double dblGrandTotalDiscount = 0;
double dblGrandTotalAmount = 0;
 
protected void Page_Load(object sender, EventArgs e)
{
    TableCell cell = grdViewOrders.HeaderRow.Cells[0]; // First Cell in the Header - Grand Total
    System.Web.UI.HtmlControls.HtmlImage img = new System.Web.UI.HtmlControls.HtmlImage();
    img.Src = "images/minus.gif";
    img.Attributes.Add("class", "ExpandCollapseGrandStyle");
    img.Attributes.Add("alt", "0");
    cell.Controls.Add(img);
    cell.HorizontalAlign = HorizontalAlign.Left;
    //cell.CssClass = "HeaderCell";
    cell.Attributes.Add("alt", "HeaderCell" + ",0");
 
    cell = grdViewOrders.HeaderRow.Cells[1]; // Second Group
    img = new System.Web.UI.HtmlControls.HtmlImage();
    img.Src = "images/minus.gif";
    img.Attributes.Add("class", "ExpandCollapseHeaderStyle");
    img.Attributes.Add("alt", "1");
    cell.Controls.Add(img);
    cell.HorizontalAlign = HorizontalAlign.Left;
    //cell.CssClass = "HeaderCell" + "1";
    cell.Attributes.Add("alt", "HeaderCell" + ",1");
 
    cell = grdViewOrders.HeaderRow.Cells[2]; // First Group
    img = new System.Web.UI.HtmlControls.HtmlImage();
    img.Src = "images/minus.gif";
    img.Attributes.Add("class", "ExpandCollapseHeaderStyle");
    img.Attributes.Add("alt", "2");
    cell.Controls.Add(img);
    cell.HorizontalAlign = HorizontalAlign.Left;
    //cell.CssClass = "HeaderCell" + "2";
    cell.Attributes.Add("alt", "HeaderCell" + ",2");
}
 
/// <summary>
/// Event fires for every row creation
/// Used for creating SubTotal row when next group starts by adding Group Total at previous row manually
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void grdViewOrders_RowCreated(object sender, GridViewRowEventArgs e)
{
    bool IsCustomerSubTotalRowNeedToAdd = false; // First Level Grouping
    bool IsOrderSubTotalRowNeedToAdd = false; // Second Level Grouping
    bool IsGrandTotalRowNeedtoAdd = false;
 
    #region Reset firstlevel, secondlevel counters for Sub Total.
    // Not First row
    if ((strPreviousRowCustomerID != string.Empty) && (DataBinder.Eval(e.Row.DataItem, "CustomerID") != null))
    {
        // When customer is not changing, but order is changing - second level grouping changing
        if ((strPreviousRowCustomerID == DataBinder.Eval(e.Row.DataItem, "CustomerID").ToString()) &&
            (strPreviousRowOrderID != DataBinder.Eval(e.Row.DataItem, "OrderID").ToString())
        )
        {
            IsOrderSubTotalRowNeedToAdd = true;
        }
        // When customer changing - first level grouping changing
        if (strPreviousRowCustomerID != DataBinder.Eval(e.Row.DataItem, "CustomerID").ToString())
        {
            IsCustomerSubTotalRowNeedToAdd = true;
            IsOrderSubTotalRowNeedToAdd = true;
        }
    }
    #endregion
 
    #region When final row completed. firstlevel, secondlevel, and Grand Total needed
    if ((strPreviousRowCustomerID != string.Empty) &&
        (strPreviousRowOrderID != string.Empty) &&
        (DataBinder.Eval(e.Row.DataItem, "CustomerID") == null) &&
        (DataBinder.Eval(e.Row.DataItem, "OrderID") == null)
        )
    {
        IsCustomerSubTotalRowNeedToAdd = true;
        IsOrderSubTotalRowNeedToAdd = true;
        IsGrandTotalRowNeedtoAdd = true;
    }
    #endregion
 
    // Second Level Grouping
    if (IsOrderSubTotalRowNeedToAdd)
    {
        #region Inserting Order Group Total row - Second Level Grouping
        GridView grdViewOrders = (GridView)sender;
 
        // Creating a Row
        GridViewRow row = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert);
 
        //Adding Group Expand Collapse Cell
        TableCell cell = new TableCell();
        cell.CssClass = "DataCell";
        row.Cells.Add(cell);
        row.CssClass = "ExpandCollapse" + strPreviousRowOrderID;
 
        //Adding Expand Collapse Cell
        cell = new TableCell();
        cell.CssClass = "DataCell";
        row.Cells.Add(cell);
 
        cell = new TableCell();
        System.Web.UI.HtmlControls.HtmlImage img = new System.Web.UI.HtmlControls.HtmlImage();
        img.Src = "images/minus.gif";
        img.Attributes.Add("alt", strPreviousRowOrderID + ",2"); // Second Level Grouping
        img.Attributes.Add("class", "ExpandCollapseStyle");
        cell.Controls.Add(img);
        cell.HorizontalAlign = HorizontalAlign.Left;
        cell.CssClass = "FirstGroupTotalRowStyle";
        row.Cells.Add(cell);
 
        //Adding Header Cell
        cell = new TableCell();
        cell.Text = strOrderGroupHeaderText;
        cell.HorizontalAlign = HorizontalAlign.Left;
        cell.ColumnSpan = 1;
        cell.CssClass = "FirstGroupTotalRowStyle";
        row.Cells.Add(cell);
 
        //Adding Unit Price Column
        cell = new TableCell();
        cell.Text = string.Format("{0:0.00}", dblOrderGroupSubTotalUnitPrice);
        cell.HorizontalAlign = HorizontalAlign.Right;
        cell.CssClass = "FirstGroupTotalRowStyle";
        row.Cells.Add(cell);
 
        //Adding Quantity Column
        cell = new TableCell();
        cell.Text = dblOrderGroupSubTotalQuantity.ToString();
        cell.HorizontalAlign = HorizontalAlign.Right;
        cell.CssClass = "FirstGroupTotalRowStyle";
        row.Cells.Add(cell);
 
        //Adding Discount Column
        cell = new TableCell();
        cell.Text = string.Format("{0:0.00}", dblOrderGroupSubTotalDiscount);
        cell.HorizontalAlign = HorizontalAlign.Right;
        cell.CssClass = "FirstGroupTotalRowStyle";
        row.Cells.Add(cell);
 
        //Adding Amount Column
        cell = new TableCell();
        cell.Text = string.Format("{0:0.00}", dblOrderGroupSubTotalAmount);
        cell.HorizontalAlign = HorizontalAlign.Right;
        cell.CssClass = "FirstGroupTotalRowStyle";
        row.Cells.Add(cell);
 
        //Adding the Row at the RowIndex position in the Grid
        grdViewOrders.Controls[0].Controls.AddAt(intSubTotalIndex, row);
        intSubTotalIndex++;
        #endregion
 
        #region Reseting the Sub Total Variables
        dblOrderGroupSubTotalUnitPrice = 0;
        dblOrderGroupSubTotalQuantity = 0;
        dblOrderGroupSubTotalDiscount = 0;
        dblOrderGroupSubTotalAmount = 0;
        #endregion
    }
 
    // First Level Grouping
    if (IsCustomerSubTotalRowNeedToAdd)
    {
        #region Inserting Customer Group Total Row - First Level Grouping
        GridView grdViewOrders = (GridView)sender;
 
        // Creating a Row
        GridViewRow row = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert);
 
        //Adding Group Expand Collapse Cell
        TableCell cell = new TableCell();
        cell.CssClass = "DataCell";
        row.Cells.Add(cell);
        row.CssClass = "ExpandCollapse" + strPreviousRowCustomerID;
 
        //Adding Expand Collapse Cell
        cell = new TableCell();
        System.Web.UI.HtmlControls.HtmlImage img = new System.Web.UI.HtmlControls.HtmlImage();
        img.Src = "images/minus.gif";
        img.Attributes.Add("alt", strPreviousRowCustomerID + ",1"); // First Level Grouping
        img.Attributes.Add("class", "ExpandCollapseStyle");
        cell.Controls.Add(img);
        cell.HorizontalAlign = HorizontalAlign.Left;
        cell.CssClass = "SecondGroupTotalRowStyle";
        row.Cells.Add(cell);
 
        cell = new TableCell();
        cell.CssClass = "SecondGroupTotalRowStyle";
        row.Cells.Add(cell);
 
        //Adding Header Cell
        cell = new TableCell();
        cell.Text = strCustomerGroupHeaderText;
        cell.HorizontalAlign = HorizontalAlign.Left;
        cell.ColumnSpan = 1;
        cell.CssClass = "SecondGroupTotalRowStyle";
        row.Cells.Add(cell);
 
        //Adding Unit Price Column
        cell = new TableCell();
        cell.Text = string.Format("{0:0.00}", dblCustomerGroupSubTotalUnitPrice);
        cell.HorizontalAlign = HorizontalAlign.Right;
        cell.CssClass = "SecondGroupTotalRowStyle";
        row.Cells.Add(cell);
 
        //Adding Quantity Column
        cell = new TableCell();
        cell.Text = dblCustomerGroupSubTotalQuantity.ToString();
        cell.HorizontalAlign = HorizontalAlign.Right;
        cell.CssClass = "SecondGroupTotalRowStyle";
        row.Cells.Add(cell);
 
        //Adding Discount Column
        cell = new TableCell();
        cell.Text = string.Format("{0:0.00}", dblCustomerGroupSubTotalDiscount);
        cell.HorizontalAlign = HorizontalAlign.Right;
        cell.CssClass = "SecondGroupTotalRowStyle";
        row.Cells.Add(cell);
 
        //Adding Amount Column
        cell = new TableCell();
        cell.Text = string.Format("{0:0.00}", dblCustomerGroupSubTotalAmount);
        cell.HorizontalAlign = HorizontalAlign.Right;
        cell.CssClass = "SecondGroupTotalRowStyle";
        row.Cells.Add(cell);
 
        //Adding the Row at the RowIndex position in the Grid
        grdViewOrders.Controls[0].Controls.AddAt(intSubTotalIndex, row);
        intSubTotalIndex++;
        #endregion
 
        #region Reseting the Sub Total Variables
        dblCustomerGroupSubTotalUnitPrice = 0;
        dblCustomerGroupSubTotalQuantity = 0;
        dblCustomerGroupSubTotalDiscount = 0;
        dblCustomerGroupSubTotalAmount = 0;
        #endregion
    }
 
    if (IsGrandTotalRowNeedtoAdd)
    {
        #region Grand Total Row - Third Level Grouping
        GridView grdViewOrders = (GridView)sender;
 
        // Creating a Row
        GridViewRow row = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert);
 
        //Adding Group Expand Collapse Cell
        TableCell cell = new TableCell();
        //System.Web.UI.HtmlControls.HtmlImage img = new System.Web.UI.HtmlControls.HtmlImage();
        //img.Src = "images/minus.gif";
        //img.Attributes.Add("class", "ExpandCollapseGrandStyle");
        //img.Attributes.Add("alt", "0");
        //cell.Controls.Add(img);
        //cell.HorizontalAlign = HorizontalAlign.Left;
        cell.CssClass = "GrandTotalRowStyle";
        row.Cells.Add(cell);
 
        cell = new TableCell();
        cell.CssClass = "GrandTotalRowStyle";
        row.Cells.Add(cell);
 
        //Adding Expand Collapse Cell
        cell = new TableCell();
        cell.CssClass = "GrandTotalRowStyle";
        row.Cells.Add(cell);
 
        //Adding Header Cell
        cell = new TableCell();
        cell.Text = "Grand Total";
        cell.HorizontalAlign = HorizontalAlign.Left;
        cell.ColumnSpan = 1;
        cell.CssClass = "GrandTotalRowStyle";
        row.Cells.Add(cell);
 
        //Adding Unit Price Column
        cell = new TableCell();
        cell.Text = string.Format("{0:0.00}", dblGrandTotalUnitPrice);
        cell.HorizontalAlign = HorizontalAlign.Right;
        cell.CssClass = "GrandTotalRowStyle";
        row.Cells.Add(cell);
 
        //Adding Quantity Column
        cell = new TableCell();
        cell.Text = dblGrandTotalQuantity.ToString();
        cell.HorizontalAlign = HorizontalAlign.Right;
        cell.CssClass = "GrandTotalRowStyle";
        row.Cells.Add(cell);
 
        //Adding Discount Column
        cell = new TableCell();
        cell.Text = string.Format("{0:0.00}", dblGrandTotalDiscount);
        cell.HorizontalAlign = HorizontalAlign.Right;
        cell.CssClass = "GrandTotalRowStyle";
        row.Cells.Add(cell);
 
        //Adding Amount Column
        cell = new TableCell();
        cell.Text = string.Format("{0:0.00}", dblGrandTotalAmount);
        cell.HorizontalAlign = HorizontalAlign.Right;
        cell.CssClass = "GrandTotalRowStyle";
        row.Cells.Add(cell);
 
        //Adding the Row at the RowIndex position in the Grid
        grdViewOrders.Controls[0].Controls.AddAt(e.Row.RowIndex, row);
        #endregion
    }
 
    #region Getting the Group Header Text
    if (DataBinder.Eval(e.Row.DataItem, "CustomerID") != null)
        strCustomerGroupHeaderText = DataBinder.Eval(e.Row.DataItem, "CompanyName").ToString() + " (" + DataBinder.Eval(e.Row.DataItem, "CustomerID").ToString() + ")";
 
    if (DataBinder.Eval(e.Row.DataItem, "OrderID") != null)
        strOrderGroupHeaderText = DataBinder.Eval(e.Row.DataItem, "OrderID").ToString() + " (Order Date : " + DataBinder.Eval(e.Row.DataItem, "OrderDate").ToString() + ", Delivery Date : " + DataBinder.Eval(e.Row.DataItem, "DeliveryDate").ToString() + ")";
    #endregion
}
 
/// <summary>
/// Event fires when data binds to each row
/// Used for calculating Group Total
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void grdViewOrders_RowDataBound(object sender, GridViewRowEventArgs e)
{
    // This is for cumulating the values
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        strPreviousRowCustomerID = DataBinder.Eval(e.Row.DataItem, "CustomerID").ToString();
        strPreviousRowOrderID = DataBinder.Eval(e.Row.DataItem, "OrderID").ToString();
 
        double dblUnitPrice = Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "UnitPrice").ToString());
        double dblQuantity = Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "Quantity").ToString());
        double dblDiscount = Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "Discount").ToString());
        double dblAmount = Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "Amount").ToString());
 
        // Cumulating Sub Total
        dblCustomerGroupSubTotalUnitPrice += dblUnitPrice;
        dblCustomerGroupSubTotalQuantity += dblQuantity;
        dblCustomerGroupSubTotalDiscount += dblDiscount;
        dblCustomerGroupSubTotalAmount += dblAmount;
 
        dblOrderGroupSubTotalUnitPrice += dblUnitPrice;
        dblOrderGroupSubTotalQuantity += dblQuantity;
        dblOrderGroupSubTotalDiscount += dblDiscount;
        dblOrderGroupSubTotalAmount += dblAmount;
 
        // Cumulating Grand Total
        dblGrandTotalUnitPrice += dblUnitPrice;
        dblGrandTotalQuantity += dblQuantity;
        dblGrandTotalDiscount += dblDiscount;
        dblGrandTotalAmount += dblAmount;
 
        e.Row.Style.Add("display", "block");
        e.Row.Cells[0].CssClass = "DataRowStyle";
        e.Row.Cells[0].Attributes.Add("alt", ",3");
    }
    intSubTotalIndex++;
}
The Style sheet
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
.SecondGroupTotalRowStyle{
    border:solid 1px Black;
    background-color:chocolate;
    font-weight:bold;
}
.FirstGroupTotalRowStyle {
    border:solid 1px Black;
    background-color:#81BEF7;
}
.GrandTotalRowStyle{
    border:solid 1px Black; 
    background-color:Gray;
    font-weight:bold;
}
.GroupHeaderStyle{
    border:solid 1px Black;
    background-color:#81BEF7;
    font-weight:bold;
}
.ExpandCollapseStyle
{
    border:0px;
    cursor:pointer;
    width:10px;
}
.ExpandCollapseGrandStyle
{
    border:0px;
    cursor:pointer;
    width:10px;
}
.DataCell
{
    border:solid 1px Black;
}
.ExpandCollapseHeaderStyle {
    background-color:#6B696B;
    cursor:pointer;
}
The JavaScript
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
//ExpandCollapse
$(document).ready(function () {
    $('.ExpandCollapseStyle').click(function () {
        var selectedTrackId = $(this).attr('alt');
 
        var isSelectedTrackerFound = false;
        var selectedTrackerGroupIndex = 0;
 
        var ExpandOrCollapse = $(this).attr('src');
 
 
        $($(".grdViewOrders tr").get().reverse()).each(function () {
 
            var currentTrackId = $(this).find(".ExpandCollapseStyle").attr('alt');
 
            if (currentTrackId == null)
                currentTrackId = $(this).find(".DataRowStyle").attr('alt');
 
            if (currentTrackId != null) {
 
                if (selectedTrackId.split(",")[0] == currentTrackId.split(",")[0]) {
 
                    isSelectedTrackerFound = true;
                    if (selectedTrackerGroupIndex == 0) {
                        selectedTrackerGroupIndex = currentTrackId.split(",")[1];
 
                        if (ExpandOrCollapse == 'images/plus.gif') {
                            $(this).find(".ExpandCollapseStyle").attr('src', 'images/minus.gif');
                        }
                        else {
                            $(this).find(".ExpandCollapseStyle").attr('src', 'images/plus.gif');
                        }
                    }
                }
                else {
                    if (currentTrackId != null) {
                        if (parseInt(selectedTrackerGroupIndex) > 0) {
                            if (parseInt(selectedTrackerGroupIndex) >= parseInt(currentTrackId.split(",")[1]))
                                isSelectedTrackerFound = false;
                        }
                    }
                    if (isSelectedTrackerFound == true) {
 
                        if (ExpandOrCollapse == 'images/plus.gif') {
                            $(this).css("display", "block");
                            $(this).find(".ExpandCollapseStyle").attr('src', 'images/minus.gif');
                        }
                        else {
                            $(this).css("display", "none");
                            $(this).find(".ExpandCollapseStyle").attr('src', 'images/plus.gif');
                        }
                    }
                }
            }
        });
    })
 
    $('.ExpandCollapseGrandStyle').click(function () {
 
        var ExpandOrCollapse = $(this).attr('src');
        var selectedTrackId = $(this).attr('alt');
 
        var isSelectedTrackerFound = false;
        var selectedTrackerGroupIndex = 0;
 
        $($(".grdViewOrders tr").get().reverse()).each(function () {
 
            var currentTrackId = $(this).find(".ExpandCollapseStyle").attr('alt');
 
 
            if (currentTrackId != null) {
 
                if (ExpandOrCollapse == 'images/plus.gif') {
 
                    if (parseInt(selectedTrackId) + 1 == parseInt(currentTrackId.split(",")[1])) {
                        $(this).find(".ExpandCollapseStyle").attr('src', 'images/plus.gif');
                        $('.ExpandCollapse' + currentTrackId.split(",")[0]).css("display", "block");
                    }
                    if (parseInt(selectedTrackId) + 1 < parseInt(currentTrackId.split(",")[1])) {
                        $('.ExpandCollapse' + currentTrackId.split(",")[0]).css("display", "none");
                    }
                }
                else {
 
                    $(this).find(".ExpandCollapseStyle").attr('src', 'images/plus.gif');
                    $('.ExpandCollapse' + currentTrackId.split(",")[0]).css("display", "none");
                }
            }
            else { // Data Row
                currentTrackId = $(this).find(".DataRowStyle").attr('alt');
                if (currentTrackId != null) {
                    if (ExpandOrCollapse == 'images/plus.gif') {
                        //$(this).css("display", "block");
                        $(this).css("display", "none");
                    }
                    else {
                        $(this).css("display", "none");
                    }
                }
            }
        });
 
        $(".grdViewOrders tr").children("th").each(function (index) {
 
            var currentTrackId = $(this).attr('alt');
            if (currentTrackId != null) {
                if (parseInt(currentTrackId.split(",")[1]) > parseInt(selectedTrackId)) {
                    if (ExpandOrCollapse == 'images/minus.gif')
                        $(this).find('.ExpandCollapseHeaderStyle').attr('src', 'images/plus.gif');
                }
            }
        });
 
        if ($('.ExpandCollapseGrandStyle').attr('src') == 'images/minus.gif') {
            $('.ExpandCollapseGrandStyle').attr('src', 'images/plus.gif');
        }
        else {
            $('.ExpandCollapseGrandStyle').attr('src', 'images/minus.gif');
        }
    })
 
    $('.ExpandCollapseHeaderStyle').click(function () {
 
        var selectedTrackId = $(this).attr('alt');
 
        var ExpandOrCollapse = $(this).attr('src');
 
        $($(".grdViewOrders tr").get().reverse()).each(function () {
 
            var currentTrackId = $(this).find(".ExpandCollapseStyle").attr('alt');
 
            if (currentTrackId != null) {
 
                if (ExpandOrCollapse == 'images/plus.gif') {
 
                    if (parseInt(currentTrackId.split(",")[1]) <= parseInt(selectedTrackId)) {
                        $(this).find(".ExpandCollapseStyle").attr('src', 'images/minus.gif');
                        $('.ExpandCollapse' + currentTrackId.split(",")[0]).css("display", "block");
                    }
 
                    if (parseInt(currentTrackId.split(",")[1]) == (parseInt(selectedTrackId) + 1)) {
                        $(this).find(".ExpandCollapseStyle").attr('src', 'images/plus.gif');
                        $('.ExpandCollapse' + currentTrackId.split(",")[0]).css("display", "block");
                    }
                    if (parseInt(currentTrackId.split(",")[1]) > (parseInt(selectedTrackId) + 1))
                        $('.ExpandCollapse' + currentTrackId.split(",")[0]).css("display", "none");
                }
                else {
 
                    if (parseInt(currentTrackId.split(",")[1]) == parseInt(selectedTrackId))
                        $(this).find(".ExpandCollapseStyle").attr('src', 'images/plus.gif');
 
                    if (parseInt(currentTrackId.split(",")[1]) > parseInt(selectedTrackId)) {
                        $('.ExpandCollapse' + currentTrackId.split(",")[0]).css("display", "none");
                    }
                    if (parseInt(currentTrackId.split(",")[1]) < parseInt(selectedTrackId))
                        $(this).find(".ExpandCollapseStyle").attr('src', 'images/minus.gif');
                }
            }
 
            currentTrackId = $(this).find(".DataRowStyle").attr('alt');
            if (currentTrackId != null) {
 
                if (parseInt(currentTrackId.split(",")[1]) > parseInt(selectedTrackId)) {
                    if (ExpandOrCollapse == 'images/plus.gif') {
                        if (parseInt(currentTrackId.split(",")[1]) == (parseInt(selectedTrackId) + 1))
                            $(this).css("display", "block");
                        else
                            $(this).css("display", "none");
                    }
                    else {
                        $(this).css("display", "none");
                    }
                }
            }
        });
 
        $(".grdViewOrders tr").children("th").each(function (index) {
 
            var currentTrackId = $(this).attr('alt');
            if (currentTrackId != null) {
                if (parseInt(currentTrackId.split(",")[1]) > parseInt(selectedTrackId)) {
                    if (ExpandOrCollapse == 'images/minus.gif')
                        $(this).find('.ExpandCollapseHeaderStyle').attr('src', 'images/plus.gif');
                }
                if (parseInt(currentTrackId.split(",")[1]) < parseInt(selectedTrackId)) {
                    if (ExpandOrCollapse == 'images/plus.gif') {
                        $(this).find('.ExpandCollapseHeaderStyle').attr('src', 'images/minus.gif');
                        $('.ExpandCollapseGrandStyle').attr('src', 'images/minus.gif');
                    }
                }
            }
        });
 
        if ($(this).attr('src') == 'images/minus.gif') {
            $(this).attr('src', 'images/plus.gif');
        }
        else {
            $(this).attr('src', 'images/minus.gif');
        }
    })
 
    function isDisplayed(object) {
        // if the object is visible return true
        if ($(object).css('display') == 'block') {
            return true;
        }
        // if the object is not visible return false
        return false;
    };
});
The output of this example will be as below.





Below is the screenshots for five level of grouping.






download the working example of the source code here in C# here and in VB here.

No comments:

Post a Comment

Note: only a member of this blog may post a comment.