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

5 thoughts on “Subtotals (Summary rows) in Silverlight DataGrid

  1. Pingback: Grand total in a Silverlight DataGrid « SC Vinod's Blog

  2. Hi,

    Thanks for your piece of code.. It helped me. How do i update the total of each group, whenever the item value changes. In the above sample, if i change the revenue of KFC Breakfast to 2000, the total value should be updated to 15000. Any help on this..

    Thanks in advance.

  3. paps4u,

    If the value changes in the data source like SQL Server in which you are retrieving the values from, obviously the total value will get updated after refreshing the page.

  4. Vinod, How to refresh the datagrid dynamically when a value in a cell is changed, without rebinding, basically if the user has scrolled to middle of the datagrid, rebinding and refreshing will disturb the UI.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s