Subtotals (Summary rows) in Silverlight DataGrid

I was breaking my head a lots with the knowledge and the experience that I have on Silverlight itself on the whole. After creating a complex Stored procedure in SQL and binding the results to a Silverlight DataGrid and grouping them  I was asked to insert Subtotals after each of the Group of Rows. After working hard on Grouping in the DataGrid I started working on Inserting the Subtotals and finally accomplished.

Well, guys I was finally able to accomplish this using ObservableCollection, PagedCollectionView,  and CollectionViewGroup.

Fine Lets start with them then.

1. Create a Silverlight project, I’m not gonna go deep in to it as you guys should know how to create a Silverlight project.

2. Add a DataGrid to the  MainPage.XAML.

3. Now, lets create a class and properties in them which are gonna be bound to the DataGrid.

public class Revenues
    {
        public string Restaurant { get; set; }
        public string Shift { get; set; }
        public decimal Revenue { get; set; }
    }

4.Now, lets use ObservableCollection and add data to our Class through ObservableCollection in the MainPage Constructor .
ObservableCollection(NameSpace : System.Collections.ObjectModel)

            ObservableCollection<Revenues> OCRevenues = new ObservableCollection<Revenues>();

            OCRevenues.Add(new Revenues() { Restaurant = "McDonalds", Shift = "Breakfast", Revenue = 3000 });
            OCRevenues.Add(new Revenues() { Restaurant = "McDonalds", Shift = "Lunch", Revenue = 2000 });
            OCRevenues.Add(new Revenues() { Restaurant = "McDonalds", Shift = "Dinner", Revenue = 4000 });
            OCRevenues.Add(new Revenues() { Restaurant = "KFC", Shift = "Breakfast", Revenue = 1000 });
            OCRevenues.Add(new Revenues() { Restaurant = "KFC", Shift = "Lunch", Revenue = 5000 });
            OCRevenues.Add(new Revenues() { Restaurant = "KFC", Shift = "Dinner", Revenue = 8000 });
            OCRevenues.Add(new Revenues() { Restaurant = "Pizza Hut", Shift = "Breakfast", Revenue = 12000 });
            OCRevenues.Add(new Revenues() { Restaurant = "Pizza Hut", Shift = "Lunch", Revenue = 15000 });
            OCRevenues.Add(new Revenues() { Restaurant = "Pizza Hut", Shift = "Dinner", Revenue = 10000 });

5. Now, create a PagedCollectionView which is to be grouped and then bind it to the ObservableCollection  created above.

PagedCollectionView (NameSpace: using System.Windows.Data)

PagedCollectionView PGV = new PagedCollectionView(OCRevenues);

6. Group the PagedCollectionViewb by Restaurant property of the Revenues class using PropertyGroupDescription after checking if it can be grouped.

      if (PGV.CanGroup)
            {
                PGV.GroupDescriptions.Add(new PropertyGroupDescription("Restaurant"));
            }

7. Create  a Generic List for the Revenue class. This Generic List is going to store the the subtotals in future. Also, declare a decimal variable whose usage will be known shortly.

            List<Revenues> lstRev = new List<Revenues>();

            decimal subtotal = 0;

8. Now, let’s loop through each of the Groups in PagedCollectionView using CollectionViewGroup and then loop through each row in the CollectionViewGroup and calculate the Subtotals.

        foreach (CollectionViewGroup CVG in PGV.Groups)
            {
                foreach (var row in CVG.Items)
                {
                    subtotal = ((Revenues)row).Revenue + subtotal;
                }

                Revenues revenues = new Revenues();
                revenues.Revenue = subtotal;
                revenues.Restaurant = CVG.Name.ToString();
                lstRev.Add(revenues);
                subtotal = 0;
            }

Let me explain what is happening in this part of coding. We are looping through each CollectionViewGroup  in the PagedCollectionView . And then looping through each row inside the CollectionViewGroup  to sum the value in the Revenue property and store it in the subtotal variable.

Then, create a new object for the Revenue class. Assign the above subtotaled value to the Revenue property of the newly created object for the Revenue class and also assign the name of the CollectionViewGroup  to the Restaurant property.

Now, add the above create object for the Revenue class to the Generic List lstRev that was created previously.

And also make the value of the subtotal variable to be zero, so that the its value does not get added continuously.

7. Now, lets loop through each data in the Generic List lstRev and add it to the ObservableCollection and refresh our PagedCollectionView so that our new data in the ObservableCollection  gets added to it and bind the PagedCollectionView  to the DataGrid.

      foreach (var data in lstRev)
            {
                OCRevenues.Add(data);
            }

            PGV.Refresh();

            RevenueDG.ItemsSource = PGV;

Now, let’s execute the application to see the output.

Untitled

Once executed we will get a DataGrid with each group of data having a row in the end with a total.

Advertisements