PIvotGrid DateTime fields

[Piyush Bhatt]

In the dataset when there is DateTime filed, the pivotgrid adds Year, Month, Quarter etc columns.

1) Now, we need these columns but PivotGrid does not display right values for these columns. It always displays the Date only for each of Year/Month/Quarter fields. For example, the Year field's datafield propery is set to "DateField.Year". 

When we try to override the behavior in _FieldCreated event by setting the DataField to "DateField" and GroupInterval = Year. Then it displays the Year but everytime there is postback it adds the Year field multiple times to the field window. There does not seem to be a way to find out how to stop adding this field multiple times.

2) For Month we would want to display "MMM, YYYY" how to achieve that?

[Konstantin Dikov]

Hello Piyush,

I am not sure that I understand the first issue correctly. Generally, when you are setting a DataTime type as a DataField of a row or column field, you need to select the group interval only:

<telerik:RadPivotGrid runat="server" ID="RadPivotGrid1" OnNeedDataSource="RadPivotGrid1_NeedDataSource">
    <Fields>
        <telerik:PivotGridRowField DataField="Description"></telerik:PivotGridRowField>
        <telerik:PivotGridAggregateField ZoneIndex="3" DataField="Value" Aggregate="Count">
        </telerik:PivotGridAggregateField>
        <telerik:PivotGridColumnField DataField="DateField" GroupInterval="Year"></telerik:PivotGridColumnField>
        <telerik:PivotGridColumnField DataField="DateField" GroupInterval="Quarter"></telerik:PivotGridColumnField>
    </Fields>
</telerik:RadPivotGrid>

And the dummy data:

protected void RadPivotGrid1_NeedDataSource(object sender, PivotGridNeedDataSourceEventArgs e)
{
    (sender as RadPivotGrid).DataSource = GetData;
}

public static DataTable GetData
{
    get
    {
        DataTable table = new DataTable();
        table.Columns.Add("Value", typeof(string));
        table.Columns.Add("Description", typeof(string));
        table.Columns.Add("DateField", typeof(DateTime));
        for (int i = 0; i < 5; i++)
        {
            table.Rows.Add(i, "Description", DateTime.Now.AddDays(i));
            table.Rows.Add(i, "Description", DateTime.Now.AddDays(i + 50));
            table.Rows.Add(i, "Description", DateTime.Now.AddDays(i + 250));
            table.Rows.Add(i, "Description2", DateTime.Now.AddDays(i));
            table.Rows.Add(i, "Description2", DateTime.Now.AddDays(i + 50));
            table.Rows.Add(i, "Description2", DateTime.Now.AddDays(i + 250));
        }

        return table;
    }
}

As for the second question, this is not supported scenario and the month that will be displayed will not be a DateTime object, so it will not be possible to apply format string to it. Since this is a Month group interval, the grouping will be based on the Month and the year is not included in that grouping. Nevertheless, if you need to group first on the year and then move to month grouping, you should use the following:

<telerik:PivotGridColumnField DataField="DateField" GroupInterval="Year"></telerik:PivotGridColumnField>
<telerik:PivotGridColumnField DataField="DateField" GroupInterval="Month"></telerik:PivotGridColumnField>

Hope this helps.


Regards,
Konstantin Dikov
Telerik

 

 

[Piyush Bhatt]

    <asp:PlaceHolder ID="gridPlaceHolder" runat="server" />        


The PivotGrid is created dynamically in Init event. The data object has DateTime property for SaleDate. As soon as the data is bound to the grid in NeedDataSource - the fields like Year/Month/Quarter are added. But they don't show the correct grouping or value while displaying in the result.

And if we try to add the Date field manually with GroupInterval then it displays properly but then field shows up multiple times in the Field window.

 public partial class Default : System.Web.UI.Page
    {
        protected override void OnInit(EventArgs e)
        {
            base.OnInit(e);
            _InitPivotGrid();
        }

        protected void Page_Load(object sender, EventArgs e)
        {

        }

        private void _InitPivotGrid()
        {
            RadPivotGrid pivotGrid = new RadPivotGrid();
            gridPlaceHolder.Controls.Add(pivotGrid);

            pivotGrid.EnableZoneContextMenu = true;
            pivotGrid.EnableConfigurationPanel = true;

            pivotGrid.ConfigurationPanelSettings.Position = PivotGridConfigurationPanelPosition.FieldsWindow;
            pivotGrid.ConfigurationPanelSettings.EnableDragDrop = true;
            pivotGrid.ConfigurationPanelSettings.EnableFieldsContextMenu = true;
            pivotGrid.ConfigurationPanelSettings.DefaultDeferedLayoutUpdate = true;

            pivotGrid.NeedDataSource += pivotGrid_NeedDataSource;
            pivotGrid.FieldCreated += pivotGrid_FieldCreated;
        }

        void pivotGrid_FieldCreated(object sender, PivotGridFieldCreatedEventArgs e)
        {
        }

        void pivotGrid_NeedDataSource(object sender, PivotGridNeedDataSourceEventArgs e)
        {
            RadPivotGrid pivotGrid = sender as RadPivotGrid;
            if (pivotGrid != null)
                pivotGrid.DataSource = PivotData.GetData();
        }
    }

    public class PivotData
    {
        public string Region { get; set; }
        public string Country { get; set; }
        public DateTime SaleDate { get; set; }
        public int Quantity { get; set; }

        public static List<PivotData> GetData()
        {
            List<PivotData> data = new List<PivotData>();

            string[] regions = new string[] { "North America", "Europe", "Asia", "Africa", "South America" };
            string[] countries = new string[] { "US", "Mexico", "Canada", "China", "India", "Japan", "South Africa", "Egypt", "Brazil", "Argentina" };

            DateTime stDate = new DateTime(2010, 1, 1);
            DateTime enDate = new DateTime(2014, 12, 1);

            Random r= new Random(1000);
            foreach(string region in regions) {
                foreach(string country in countries) {
                    for (DateTime date = stDate; date <= enDate; date = date.AddMonths(1))
                    {
                        PivotData pd = new PivotData() { Region = region, Country = country, SaleDate = stDate, Quantity = r.Next(100) };
                        data.Add(pd);
                    }
                }//foreach country
            }//foreach region
            return data;
        }//GetData
    }

[Piyush Bhatt]

make SaleDate = date in the foreach loop in above. That's a typo.

[Piyush Bhatt]

Finally figured out - overall conclusion is below:

1) For any Date field the control adds 'year/quarter/month' etc fields that are basically useless. We have to remove them anyway.
2) We should add the 'year/quarter/month' fields in either PageInit() or PageLoad() events manually and set its group interval accordingly
3) Programmatic creation of pivotgrid talks about adding in PageInit() and we have to set ViewState = false for that case. Otherwise it won't work correctly. But if we keep ViewState=false, then "Update" events from Field window does not maintain the correct field listing. So ViewState has to be TURNED ON and that requires to use PageLoad() ultimately to add such fields. This is not an issue but a note for any curious developer who will run into similar issues.

Thanks. 

[Michal]

For my needs satisfactorily served this code:

        protected void RadPivotGrid1_FieldCreated(object sender, PivotGridFieldCreatedEventArgs e)
        {
            if (e.Field.DataField.Contains(".Year"))
            {
                e.Field.DataField = e.Field.DataField.Replace(".Year", "");
                ((PivotGridGroupField)e.Field).GroupInterval = PivotGridGroupInterval.Year;
                ((PivotGridGroupField)e.Field).ShowGroupsWhenNoData = false;
            }
            if (e.Field.DataField.Contains(".Quarter"))
            {
                e.Field.DataField = e.Field.DataField.Replace(".Quarter", "");
                ((PivotGridGroupField)e.Field).GroupInterval = PivotGridGroupInterval.Quarter;
                ((PivotGridGroupField)e.Field).ShowGroupsWhenNoData = false;
            }
            if (e.Field.DataField.Contains(".Month"))
            {
                e.Field.DataField = e.Field.DataField.Replace(".Month", "");
                ((PivotGridGroupField)e.Field).GroupInterval = PivotGridGroupInterval.Month;
                ((PivotGridGroupField)e.Field).ShowGroupsWhenNoData = false;
            }
            if (e.Field.DataField.Contains(".Day"))
            {
                e.Field.DataField = e.Field.DataField.Replace(".Day", "");
                ((PivotGridGroupField)e.Field).GroupInterval = PivotGridGroupInterval.Day;
                ((PivotGridGroupField)e.Field).ShowGroupsWhenNoData = false;
            }
            if (e.Field.DataField.Contains(".Week") || e.Field.DataField.Contains(".Hour")|| e.Field.DataField.Contains(".Minute")|| e.Field.DataField.Contains(".Second")) // I do not need to use these features of a date field.
            {
                ((RadPivotGrid)sender).Fields.Remove(e.Field);
            }
        }