Lesson 4 - Show Sparkline Charts in Grid Cells

  • 91 minutes to read

In this lesson, you will learn how to create a custom data query for collection views, how to use this query to show sparklines in the main grid and how to show detail collections for the selected item outside the grid.

Step 1 - Customize the GridControl

Change the grid column layout for CustomerCollectionView in the DevExpress.HybridApp project, as described in the previous lesson.

outlook_tut_les4_1

Step 2 - Create custom data query

In addition to properties that are present in the Customer object, let's add two unbound properties:

  • TotalSales representing overall sales for a customer;
  • MonthlySales which is a collection of values representing monthly sales.

There are many ways to achieve this. For example, we can use the GridControl.CustomUnboundColumnData event to load the necessary data dynamically. This approach, however, has at least two serious drawbacks:

  • a lot of queries to the database will be performed, which may affect performance;
  • this does not comply with the MVVM paradigm, since we move a lot of logic to the view and make it control-dependent.

Let's perform this task on the view model level using the projections mechanism. This will allow us to utilize any visual control to represent data and use unit-tests during development.

Create a class with existing Customer's properties that should be shown in the grid and two additional unbound properties.

public class CustomerInfo {
        public long Id { get; set; }
        public string Name { get; set; }
        public string AddressLine { get; set; }
        public string AddressCity { get; set; }
        public StateEnum AddressState { get; set; }
        public string AddressZipCode { get; set; }
        public string Phone { get; set; }
        public string Fax { get; set; }
        public decimal? TotalSales { get; set; }
        public IEnumerable<decimal> MonthlySales { get; set; }
    }

Note that this class contains the Id property that is not presented in the UI and which is necessary for the CollectionViewModel class to work with the CustomerInfo class as a projection type.

Create a helper method that creates the IQueryable<CustomerInfo> projection based on IQueryable<Customer>:

public static class QueriesHelper {
        public static IQueryable<CustomerInfo> GetCustomerInfo(IQueryable<Customer> customers) {
            return customers.Select(x => new CustomerInfo
            {
                Id = x.Id,
                Name = x.Name,
                AddressLine = x.AddressLine,
                AddressCity = x.AddressCity,
                AddressState = x.AddressState,
                AddressZipCode = x.AddressZipCode,
                Phone = x.Phone,
                Fax = x.Fax,
                TotalSales = x.Orders.Sum(orderItem => orderItem.TotalAmount),
                MonthlySales = x.Orders.GroupBy(o => o.OrderDate.Month).Select(g => g.Sum(i => i.TotalAmount)),
            });
        }
    }

To make CustomerCollectionViewModel work with CustomerInfo as a projection type, specify it as an additional generic parameter in the base class and use the GetCustomerInfo helper method to pass a projection parameter:

public partial class CustomerCollectionViewModel : CollectionViewModel<Customer, CustomerInfo, long, IDevAVDbUnitOfWork> {
    //...
        protected CustomerCollectionViewModel(IUnitOfWorkFactory<IDevAVDbUnitOfWork> unitOfWorkFactory = null)
         : base(unitOfWorkFactory ?? UnitOfWorkSource.GetUnitOfWorkFactory(), x => x.Customers, query => QueriesHelper.GetCustomerInfo(query)) {
        }
    }

Since the CustomerInfo property names match the corresponding properties in the Customer class, you do not need to change column field names.

Step 3 - Customizing summaries

Enable a total summary for the TotalSales property. In the designer view, select the grid, invoke the TotalSummary editor from Property Grid window, add a new GridSummary item and customize it as shown below:

outlook_tut_les4_2

Run the application.

outlook_tut_les4_3

Step 4- Adding Sparkline Column to the Grid

Select a grid, open its smart tag and click the Add Empty Column option.

outlook_tut_les4_4

Select the added column, open its smart tag, set the ColumnBase.FieldName property to MonthlySales, change the ColumnBase.EditSettings type to SparklineEditSettings, and set its BaseEdit.StyleSettings to BarSparklineStyleSettings.

outlook_tut_les4_5

Run the application.

outlook_tut_les4_6

Step 5- Adding Lazy Detail Collection to the Projection Type

Let's display customer stores for the selected customer under the grid. If you use the original Customer object in the CustomerCollectionViewModel, the solution is straightforward: just bind to the Customer.CustomerStores navigation collection. This is a lazy property, so stores for the customer are loaded only when the customer is selected in the grid and the CustomerStores property for this customer is accessed.

However, you are using the CustomerInfo projection type, so you need to add and assign this property manually:

public class CustomerInfo {
...
        public IEnumerable<CustomerStore> CustomerStores { get; set; }
    }
    public static IQueryable<CustomerInfo> GetCustomerInfo(IQueryable<Customer> customers) {
        return customers.Select(x => new CustomerInfo
        {
...
            CustomerStores = x.CustomerStores,
        });
    }

This works, but all detail collections of customers stores are now loaded at once when querying to the IQueryable<CustomerInfo>. This causes performance issues because a lot of unnecessary data is loaded.

To avoid this behavior and load customer stores only for records that are selected by the customer, do the following.

Make the CustomerInfo.CustomerStores property lazy.

public class CustomerInfo {
...
        Lazy<IEnumerable<CustomerStore>> customerStores;
        public IEnumerable<CustomerStore> CustomerStores { get { return customerStores.Value; } }
        public void SetDeferredStores(Func<IEnumerable<CustomerStore>> getStores) {
            this.customerStores = new Lazy<IEnumerable<CustomerStore>>(getStores);
        }
    }

Provide a method that assigns a function to be used for obtaining customer stores to the collection of the CustomerInfo object:

public static class QueriesHelper {
...
        public static void UpdateCustomerInfoStores(IEnumerable<CustomerInfo> entities, IQueryable<Customer> customers) {
            foreach(var item in entities) {
                item.SetDeferredStores(() => customers.First(x => x.Id == item.Id).CustomerStores.ToArray());
            }
        }
    }

The CollectionViewModel class provides a special virtual method that is called when one or many entities have been loaded from the database:

partial class CustomerCollectionViewModel {
        protected override void OnEntitiesLoaded(IDevAVDbUnitOfWork unitOfWork, IEnumerable<CustomerInfo> entities) {
            base.OnEntitiesLoaded(unitOfWork, entities);
            QueriesHelper.UpdateCustomerInfoStores(entities, unitOfWork.Customers);
        }
    }

Now, you can use the CustomerInfo.CustomerStores collection in the UI and it will be loaded on demand.

Step 6 - Using SlideView Control to Display Detail Collections

Place the SlideView control from the property grid under the GridControl. Assign the following ItemTemplate and ItemContainerStyle to it:

<dxwui:SlideView.ItemTemplate>
            <DataTemplate>
                <Border Background="Transparent">
                    <Grid Margin="0,0,16,0" Width="120">
                        <Grid.RowDefinitions>
                            <RowDefinition Height="Auto" />
                            <RowDefinition Height="Auto" />
                        </Grid.RowDefinitions>
                        <dxe:ImageEdit Height="120" IsReadOnly="True" EditValue="{Binding CrestLarge}" ShowBorder="False" />
                        <StackPanel Grid.Row="1" HorizontalAlignment="Center" Margin="0,5,0,0" TextBlock.FontSize="12">
                            <Label Content="{Binding AddressCity}" HorizontalAlignment="Center" />
                            <Label Content="{Binding AddressLine}" HorizontalAlignment="Center" />
                        </StackPanel>
                    </Grid>
                </Border>
            </DataTemplate>
        </dxwui:SlideView.ItemTemplate>
        <dxwui:SlideView.ItemContainerStyle>
            <Style TargetType="dxwui:SlideViewItem">
                <Setter Property="Template">
                    <Setter.Value>
                        <ControlTemplate TargetType="dxwui:SlideViewItem">
                            <ContentPresenter />
                        </ControlTemplate>
                    </Setter.Value>
                </Setter>
            </Style>
        </dxwui:SlideView.ItemContainerStyle>

Select the SildeView and open its smart tag. Set the SlideView.ShowBackButton property to false and bind the ItemsSource property to SelectedItem.CustomerStores using Binding Editor.

outlook_tut_les4_7

Run the application.

outlook_tut_les4_8

Result

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using DevExpress.DataAnnotations;

namespace DevExpress.DevAV {
    public class Employee : DatabaseObject {
        public Employee() {
            AssignedTasks = new List<EmployeeTask>();
        }

        [Required]
        public string FirstName { get; set; }

        [Required]
        public string LastName { get; set; }

        public PersonPrefix Prefix { get; set; }

        [Phone]
        public string HomePhone { get; set; }

        [Required, Phone]
        public string MobilePhone { get; set; }

        [Required, EmailAddress]
        public string Email { get; set; }

        public string Skype { get; set; }

        public DateTime? BirthDate { get; set; }

        public byte[] Picture { get; set; }

        public StateEnum AddressState { get; set; }
        public string AddressLine { get; set; }
        public string AddressCity { get; set; }
        public string AddressZipCode { get; set; }

        public EmployeeDepartment Department { get; set; }

        [Required]
        public string Title { get; set; }

        public EmployeeStatus Status { get; set; }

        public DateTime? HireDate { get; set; }

        public virtual List<EmployeeTask> AssignedTasks { get; set; }

        public string PersonalProfile { get; set; }

        public override string ToString() {
            return FirstName + " " + LastName;
        }
        public virtual int Order { get; set; }

        public string FullName
        {
            get { return string.Format("{0} {1}", FirstName, LastName); }
        }

        public string AddressCityLine
        {
            get { return GetCityLine(AddressCity, AddressState, AddressZipCode); }
        }
        internal static string GetCityLine(string city, StateEnum state, string zipCode) {
            return string.Format("{0}, {1} {2}", city, state, zipCode);
        }
    }

    public enum PersonPrefix {
        Dr,
        Mr,
        Ms,
        Miss,
        Mrs,
    }

    public enum EmployeeStatus {
        Salaried,
        Commission,
        Contract,
        Terminated,
        OnLeave
    }

    public enum EmployeeDepartment {
        [Display(Name = "Sales")]
        Sales = 1,
        [Display(Name = "Support")]
        Support,
        [Display(Name = "Shipping")]
        Shipping,
        [Display(Name = "Engineering")]
        Engineering,
        [Display(Name = "Human Resources")]
        HumanResources,
        [Display(Name = "Management")]
        Management,
        [Display(Name = "IT")]
        IT
    }
}
See Also