New to Telerik UI for WinForms? Download free 30-day trial

Format Specific Fields in RadPivotGrid

Environment

Product Version Product Author
2021.2.511 RadPivotGrid Desislava Yordanova

Description

Learn how to format specific DateTime fields without affecting other fields with the same type.

format-specific-fields-in-pivotgrid 001

Solution

Let's consider that we have the following custom class:

        public class Item
        {
            public DateTime ShipDate { get; set; }

            public DateTime OrderDate { get; set; }

            public decimal Price { get; set; }

            public Guid Id { get; set; }

            public string Name { get; set; }

            public Item(DateTime shipDate, DateTime orderDate, decimal price, Guid id, string name)
            {
                this.ShipDate = shipDate;
                this.OrderDate = orderDate;
                this.Price = price;
                this.Id = id;
                this.Name = name;
            }
        }

The goal is to apply custom format ("dd/MM/yyyy") only for the "OrderDate" without affecting the way "ShipDate" is displayed. For this purpose, it would be necessary to handle the GroupElementFormatting event before populating the pivot with data. The challenging part here is to get the field from the PivotGroupElementEventArgs. A possible solution is to get the GroupDescription from DataGroup Level. Then, you can use the PropertyName to format the cell:

        LocalDataSourceProvider dataProvider = new LocalDataSourceProvider();

        public RadForm1()
        {
            InitializeComponent();

            Random rand = new Random();
            List<Item> items = new List<Item>();
            for (int i = 0; i < 100; i++)
            {
                items.Add(new Item(DateTime.Now.AddDays(rand.Next(1, 100)),DateTime.Now.AddDays(rand.Next(1, 100)), i * 0.25m * rand.Next(1, 50),Guid.NewGuid(),"Item" + i));
            }

            radPivotGrid1.GroupElementFormatting += RadPivotGrid1_GroupElementFormatting;

            dataProvider.ItemsSource = items;
            this.radPivotGrid1.DataProvider = dataProvider;

            dataProvider.BeginInit();
            dataProvider.RowGroupDescriptions.Add(new DateTimeGroupDescription() { PropertyName = "OrderDate", Step = DateTimeStep.Year, GroupComparer = new GroupNameComparer() });
            dataProvider.RowGroupDescriptions.Add(new DateTimeGroupDescription() { PropertyName = "OrderDate", Step = DateTimeStep.Month, GroupComparer = new GroupNameComparer() });
            dataProvider.ColumnGroupDescriptions.Add(new DateTimeGroupDescription() { PropertyName = "OrderDate", Step = DateTimeStep.Day });
            dataProvider.ColumnGroupDescriptions.Add(new DateTimeGroupDescription() { PropertyName = "ShipDate", Step = DateTimeStep.Day });
            dataProvider.AggregateDescriptions.Add(new PropertyAggregateDescription() { PropertyName = "Price", AggregateFunction = AggregateFunctions.Sum }); 
            dataProvider.EndInit();
        }

        private void RadPivotGrid1_GroupElementFormatting(object sender, PivotGroupElementEventArgs e)
        {
            if (e.GroupElement.Data.Group.Type == GroupType.GrandTotal ||
                e.GroupElement.Data.Group.Type == GroupType.Subtotal)
            {
                // Exclude the Grand and Sub totals
                return;
            }
            int level = e.GroupElement.Data.Group.Level;
            int aggregatesCount = dataProvider.AggregateDescriptions.Count;
            int descriptionCount = e.GroupElement.Data.Axis == PivotAxis.Rows ? dataProvider.RowGroupDescriptions.Count :
                                   dataProvider.ColumnGroupDescriptions.Count;

            if (e.GroupElement.Data.Axis == e.GroupElement.Data.Owner.AggregatesPosition)
            {
                int aggregatesLevel = e.GroupElement.Data.Owner.AggregatesLevel;
                if (level == aggregatesLevel ||
                    (aggregatesLevel == -1 && aggregatesCount > 0 && level == descriptionCount))
                {
                    return;
                }
                else if (e.GroupElement.Data.Owner.AggregatesLevel > -1 &&
                         level > e.GroupElement.Data.Owner.AggregatesLevel)
                {
                    level--;
                }
            }

            if (level < descriptionCount)
            {
                var groupDescription = e.GroupElement.Data.Axis == PivotAxis.Rows ? dataProvider.RowGroupDescriptions[level] :
                                       dataProvider.ColumnGroupDescriptions[level];

                if (groupDescription.PropertyName == "OrderDate")
                {
                    DateTime dt;
                    if (DateTime.TryParse(e.GroupElement.Text, out dt))
                    {
                        e.GroupElement.Text = dt.ToString("dd/MM/yyyy");
                    }
                }
                // Depending on the PropertyName format the e.GroupElement.Text
            }
        }

In this article