none
Sum and display column values from Child DataGrid in Master DataGrid?

    Question

  • I would like to get the sum total value of two seperate columns in a child DataGrid and display these two values in the master DataGrid. The master DataGrid is bound to an ObservableCollection of Employee objects, and the child DataGrid is bound to an ObservableCollection of Sale objects (as defined in the DataGrid.RowDetailsTemplate), and the two collections are related through a foreign key collection navigation property (Employee.ID = Sale.EmployeeID) such that one Employee can have many Sales.  I would like to display the sum total of two properties (SaleAmount and Commission) from the Child DataGrid in the Master DataGrid, but I'm not sure how to accomplish this through databinding.

    The Objects defined:

        public class Employee
        {
            public int ID { get; set; }
            public int EmployeeNumber { get; set; }
            public string EmployeeName { get; set; }
            public string Department { get; set; }
        }
    
        public class Sale
        {
            public int ID { get; set; }
            public int EmployeeID { get; set; }
            public DateTime SaleDate { get; set; }
            public decimal SaleAmount { get; set; }
            public decimal Commission { get; set; }
        }

    ViewModel collections:

            private ObservableCollection<Employee> employees;
            private ObservableCollection<Sale> sales;
    
            public ObservableCollection<Employee> Employees
            {
                get { return employees; }
            }
    
            public ObservableCollection<Sale> Sales
            {
                get { return sales; }
            } 

    DataGrid as it is defined in the View:

            <DataGrid AutoGenerateColumns="False" ItemsSource="{Binding Employees}">
                <DataGrid.Columns>
                    <DataGridTextColumn Header="Name" Binding="{Binding EmployeeName}"/>
                    <DataGridTextColumn Header="ID" Binding="{Binding EmployeeNumber}"/>
                    <DataGridTextColumn Header="Department" Binding="{Binding Department}"/>
                    <DataGridTextColumn Header="Total Sales" Binding="{Binding ??}"/>
                    <DataGridTextColumn Header="Total Commission" Binding="{Binding ??}"/>
                </DataGrid.Columns>
                <DataGrid.RowDetailsTemplate>
                    <DataTemplate>
                        <StackPanel>
                            <DataGrid AutoGenerateColumns="False" ItemsSource="{Binding Sales}">
                                <DataGrid.Columns>
                                    <DataGridTextColumn Header="Sale Date" Binding="{Binding SaleDate}"/>
                                    <DataGridTextColumn Header="Sale Amount" Binding="{Binding SaleAmount}"/>
                                    <DataGridTextColumn Header="Commission" Binding="{Binding CommissionAmount}"/>
                                </DataGrid.Columns>
                            </DataGrid>
                        </StackPanel>
                    </DataTemplate>
                </DataGrid.RowDetailsTemplate>
            </DataGrid>

    If I haven't explained myself well I can provide any additional details as needed.

    Thursday, February 08, 2018 2:59 PM

Answers

  • Hi,

    Consider create a new type to wrap the Employee.

     public class EmployeeWrapper
        {
            public Employee Employee { get; set; }
    
            public EmployeeWrapper(Employee employee)
            {
                this.Employee = employee;
            }
       
            public decimal TotalSales
            {
                get
                {
                    return Employee.Sales.Select(s=>s.SaleAmount).Sum();
                }
            }
    
            public decimal TotalCommission
            {
                get
                {
                    return Employee.Sales.Select(s => s.Commission).Sum();
                }
            }
        }

    Then you can bind as below.

       <DataGrid x:Name="dg"
                AutoGenerateColumns="False"
                CanUserAddRows="False"
                ColumnWidth="*"
                ItemsSource="{Binding EmployeeWrappers}">
                <DataGrid.Columns>
                    <DataGridTextColumn Binding="{Binding Employee.EmployeeName}" Header="Name" />
                    <DataGridTextColumn Binding="{Binding Employee.EmployeeNumber}" Header="ID" />
                    <DataGridTextColumn Binding="{Binding Employee.Department}" Header="Department" />
                    <DataGridTextColumn Header="Total Sales" Binding="{Binding TotalSales}"/>
                    <DataGridTextColumn Header="Total Commission" Binding="{Binding TotalCommission}"/>
                </DataGrid.Columns>
                <DataGrid.RowDetailsTemplate>
                    <DataTemplate>
                        <StackPanel>
                            <DataGrid
                                AutoGenerateColumns="False"
                                Background="#88AFD1"
                                CanUserAddRows="False"                       
                                ItemsSource="{Binding Employee.Sales}">
                                <DataGrid.Columns>
                                    <DataGridTextColumn Binding="{Binding SaleDate}" Header="Sale Date" />
                                    <DataGridTextColumn Binding="{Binding SaleAmount}" Header="Sale Amount" />
                                    <DataGridTextColumn Binding="{Binding Commission}" Header="Sale Commission" />
                                </DataGrid.Columns>
                            </DataGrid>
                        </StackPanel>
                    </DataTemplate>
                </DataGrid.RowDetailsTemplate>
            </DataGrid>

    Test Result.

    Best Regards,

    Bob


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by rcmpgrc Thursday, February 15, 2018 5:54 PM
    Friday, February 09, 2018 9:18 AM
    Moderator

All replies

  • I see 2 problems there.

    You're binding directly to model classes and you're thinking in terms of a dataset and relations rather than the OO version.

    .

    Make a viewmodel  EmployeeVM have a public property which is an ObservableCollection<Sale>

    You can then bind the itemssource as you have there and the child datagrid will work.

    As it is, the datacontext it's in is an instance of Employee, which doesn't have a Sales property.

    Roughly ( this is air code ).

        public class EmployeeVM
        {
          public ObservableCollection<Sale> Sales {get;set;}

    Add to EmployeeVM two properties which hold the totals and their Get uses Linq to sum the column out of their sales.

    Copy data from your model into these viewmodels.

    You can use automapper, write code that transfers property by property or use reflection to iterate properties.

    eg

        public static class ModelLibExtensions
        {
            public static TConvert ConvertTo<TConvert>(this object entity) where TConvert : new()
            {
                var convertProperties = TypeDescriptor.GetProperties(typeof(TConvert)).Cast<PropertyDescriptor>();
                var entityProperties = TypeDescriptor.GetProperties(entity).Cast<PropertyDescriptor>();
    
                var convert = new TConvert();
    
                foreach (var entityProperty in entityProperties)
                {
                    var property = entityProperty;
                    PropertyInfo pi = property.ComponentType.GetProperty(property.Name);
                    int count = pi.GetCustomAttributes(true).Where(x => x is DoNotCopyAttribute).Count();
                    if(count == 0)
                    { 
                        var convertProperty = convertProperties.FirstOrDefault(prop => prop.Name == property.Name);
                        if (convertProperty != null)
                        {
                            convertProperty.SetValue(convert, entityProperty.GetValue(entity));
                        }
                    }
                }
                return convert;
            }


    Hope that helps.

    Technet articles: WPF: Layout Lab; All my Technet Articles

    Thursday, February 08, 2018 3:34 PM
    Moderator
  • Hi Andy, thanks for helping.  I'm using EF to generate my model from a SQL Server database and the Employee entity has a collection property which is defined in the model like this:

    public virtual ICollection<Sale> Sale { get; set; }

    I'm not allowed to publish code so I created the Employee/Sale scenario but already see that I've made mistakes in transposing my code to this example.  For example, I'm already binding the child DataGrid through the collection property (which you also suggested) so that part is working fine, I just don't know how to wire up the Employee ViewModel to access the properties of that Sales collection.  I'll try to figure out what you're doing with that extension method... thanks!


    • Edited by rcmpgrc Thursday, February 08, 2018 7:15 PM
    Thursday, February 08, 2018 6:56 PM
  • Hi,

    Consider create a new type to wrap the Employee.

     public class EmployeeWrapper
        {
            public Employee Employee { get; set; }
    
            public EmployeeWrapper(Employee employee)
            {
                this.Employee = employee;
            }
       
            public decimal TotalSales
            {
                get
                {
                    return Employee.Sales.Select(s=>s.SaleAmount).Sum();
                }
            }
    
            public decimal TotalCommission
            {
                get
                {
                    return Employee.Sales.Select(s => s.Commission).Sum();
                }
            }
        }

    Then you can bind as below.

       <DataGrid x:Name="dg"
                AutoGenerateColumns="False"
                CanUserAddRows="False"
                ColumnWidth="*"
                ItemsSource="{Binding EmployeeWrappers}">
                <DataGrid.Columns>
                    <DataGridTextColumn Binding="{Binding Employee.EmployeeName}" Header="Name" />
                    <DataGridTextColumn Binding="{Binding Employee.EmployeeNumber}" Header="ID" />
                    <DataGridTextColumn Binding="{Binding Employee.Department}" Header="Department" />
                    <DataGridTextColumn Header="Total Sales" Binding="{Binding TotalSales}"/>
                    <DataGridTextColumn Header="Total Commission" Binding="{Binding TotalCommission}"/>
                </DataGrid.Columns>
                <DataGrid.RowDetailsTemplate>
                    <DataTemplate>
                        <StackPanel>
                            <DataGrid
                                AutoGenerateColumns="False"
                                Background="#88AFD1"
                                CanUserAddRows="False"                       
                                ItemsSource="{Binding Employee.Sales}">
                                <DataGrid.Columns>
                                    <DataGridTextColumn Binding="{Binding SaleDate}" Header="Sale Date" />
                                    <DataGridTextColumn Binding="{Binding SaleAmount}" Header="Sale Amount" />
                                    <DataGridTextColumn Binding="{Binding Commission}" Header="Sale Commission" />
                                </DataGrid.Columns>
                            </DataGrid>
                        </StackPanel>
                    </DataTemplate>
                </DataGrid.RowDetailsTemplate>
            </DataGrid>

    Test Result.

    Best Regards,

    Bob


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by rcmpgrc Thursday, February 15, 2018 5:54 PM
    Friday, February 09, 2018 9:18 AM
    Moderator
  • Hi Andy, thanks for helping.  I'm using EF to generate my model from a SQL Server database and the Employee entity has a collection property which is defined in the model like this:

    public virtual ICollection<Sale> Sale { get; set; }

    I'm not allowed to publish code so I created the Employee/Sale scenario but already see that I've made mistakes in transposing my code to this example.  For example, I'm already binding the child DataGrid through the collection property (which you also suggested) so that part is working fine, I just don't know how to wire up the Employee ViewModel to access the properties of that Sales collection.  I'll try to figure out what you're doing with that extension method... thanks!


    Binding directly to your model  - the entity framework classes  - is ok for simple applications and you get change tracking "for free" from the datacontext.

    That's how my samples work.

    https://gallery.technet.microsoft.com/scriptcenter/WPF-Entity-Framework-MVVM-78cdc204

    This is just an introductory sample and there'd be a few more "steps" to a real world business app.

    Binding to the model is convenient but you pay for that in the shape of some significant down sides.

    Not the least of which is validation.

    Unless you write a validationrule for everything ( usually a bad idea ). You need the data to arrive in your bound properties before you can validate it.

    Once it's there then you need to revert that object if it's invalid.

    Also, you need some quite clunky wrapping of properties to put your logic in.

    What I usually do is to create a new viewmodel, copying the values in from a model.

    Which is what that extension method does.

    And here's some fairly random code which uses it:

            private void recurseSubordinates(Unit unit, UnitVM uvm)
            {
                foreach (var _unit in unit.Subordinates)
                {
                    UnitVM _uvm = _unit.ConvertTo<UnitVM>();
                    AllUnits.Add(_uvm);
                    uvm.Subordinates.Add(_uvm);
                    _uvm.IsExpanded = true;
                    recurseSubordinates(_unit, _uvm);
                }
            }
    
            private void recurseVMSubordinates(Unit unit, UnitVM uvm)
            {
                foreach (var _uvm in uvm.Subordinates)
                {
                    Unit u = _uvm.ConvertTo<Unit>();
                    unit.Subordinates.Add(u);
                    recurseVMSubordinates(u, _uvm);
                }
            }

    This code is from a game and a unit is a military unit so a division has subordinate companies, a company has subordinate platoons and so on. All of those being units.

    Where was I. 

    A UnitVM can have properties whose type and name match Unit and they'll be copied each way.

    It can have other properties and logic.

    When a user abandons an edit, you can just new up that viewmodel again from the model.

    You do, however, need to limit the user to one edit at a time or track IsNew and IsDirty in a viewmodel.


    Hope that helps.

    Technet articles: WPF: Layout Lab; All my Technet Articles

    Friday, February 09, 2018 12:00 PM
    Moderator
  • That definitely works, although I'm getting some Binding errors from the converters and styles used with the DataGrid.  Assume it's because the DataItem is now the wrapper class?  In the RowDetailsTemplate (child) DataGrid how would I bind the SelectedItem to a ViewModel property? Something like this throws an exception.

            public Sale SelectedSalesItem
            {
                get { return selectedSalesItem; }
                set
                {
                    if (selectedSalesItem != value)
                    {
                        selectedSalesItem = value;
                        NotifyPropertyChanged("SelectedSalesItem");
                    }
                }
            }

    Friday, February 09, 2018 4:05 PM
  • An object is always equal, because by default you're checking type.

    So lose this bit.

    if (selectedSalesItem != value)
       {

    And 

    What error, when?

    Where did you put selectedsalesitem?

    The datacontext of the child datagrid is whatever is presented to that row.

    That would be EmployeeWrapper now.


    Hope that helps.

    Technet articles: WPF: Layout Lab; All my Technet Articles

    Friday, February 09, 2018 4:30 PM
    Moderator
  • I placed SelectedSalesItem in the Child DataGrid.

    Binding to VM property before implementing the wrapper class:

    View

    <DataGrid AutoGenerateColumns="False" 
    	ItemsSource="{Binding Sales}"
    	SelectedItem="{Binding SelectedSalesItem}">

    View Model  (thanks for Setter tip!)

    public Sale SelectedSalesItem
    {
    	get { return selectedSalesItem; }
            set
                {
                  selectedSalesItem = value;
                  NotifyPropertyChanged("SelectedSalesItem");
                }
    }


    After implementing the wrapper class:

    View

    <DataGrid AutoGenerateColumns="False" 
    	ItemsSource="{Binding Employee.Sales}"
    	SelectedItem="{Binding SelectedSalesItem}">

    View Model

    public ??? SelectedSalesItem
    {
    	get { return selectedSalesItem; }
            set
                {
                  selectedSalesItem = value;
                  NotifyPropertyChanged("SelectedSalesItem");
                }
    }

    I was able to bind SelectedItem in the Master DataGrid to SelectedEmployee property by declaring SelectedEmployee as type EmployeeWrapper, but what is the type of the SelectedSalesItem property that I'm trying to bind in the Child DataGrid? 

    The error I see in the output window is related to this:

    System.Windows.Data Error: 40 : BindingExpression path error:

    'SelectedSalesItem' property not found on 'object' ''EmployeeWrapper'

    BindingExpression:Path=SelectedSalesItem; DataItem='EmployeeWrapper'

    target element is 'DataGrid' (Name=''); target property is

    'SelectedItem' (type 'Object')





    • Edited by rcmpgrc Friday, February 09, 2018 7:04 PM
    Friday, February 09, 2018 7:03 PM
  • It'd be a Sale.

    That error means what it says.

    It's not finding the property in EmployeeWrapper.

    ( Where I told you to put it ).

    Where did you put that property?

    By the way.

    https://msdn.microsoft.com/en-us/library/system.componentmodel.inotifypropertychanged%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396

        // This is a simple customer class that 
        // implements the IPropertyChange interface.
        public class DemoCustomer : INotifyPropertyChanged
        {
            // These fields hold the values for the public properties.
            private Guid idValue = Guid.NewGuid();
            private string customerNameValue = String.Empty;
            private string phoneNumberValue = String.Empty;
    
            public event PropertyChangedEventHandler PropertyChanged;
    
            // This method is called by the Set accessor of each property.
            // The CallerMemberName attribute that is applied to the optional propertyName
            // parameter causes the property name of the caller to be substituted as an argument.
            private void NotifyPropertyChanged([CallerMemberName] String propertyName = "")
            {
                if (PropertyChanged != null)
                {
                    PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
                }
            }

    Means you can do

     NotifyPropertyChanged();
    

    And say goodbye to the magic string.

    There's also nameof now.

    public string Foo
    {
       get
       {
          return this.foo;
       }
       set
       {
           if (value != this.foo)
           {
              this.foo = value;
              OnPropertyChanged(nameof(Foo));
           }
       }
    }

    Which I think is maybe slightly more efficient, longer to write than using callermembername.

    Why use it rather than a string?

    Because you can rename a property and forget to change the magic string or mistype.

    nameof is compiler checked and when you rename a property, it'll also change.


    Hope that helps.

    Technet articles: WPF: Layout Lab; All my Technet Articles

    Friday, February 09, 2018 8:06 PM
    Moderator
  • That's helpful, I have definitely renamed properties before without changing the hardcoded property string name. 

    SelectedSalesItem property is still located in the ViewModel but will move it into the EmployeeWrapper class and try it again.  Thanks!

    Wednesday, February 14, 2018 10:04 PM
  • I marked Bob's reply as the answer because that is the exact route I eventually took to get this working.

    Andy, many thanks for all the patience, guidance and helpful insight you provided to me!

    Thursday, February 15, 2018 5:59 PM