[Solved] Group on DateTime Column

[ben]

I have a grid that has a datetime field in it.  The field shows the date and the corresponding time and is a valid date.  I now want to group on this field, however, my grouping i want to be at the "Date" portion of the date, not the "Date and Time."  Can this be done? 

If so, how would this work and where would i put the corresponding format expression.

[Veli]

Hi Ben,

The only way to implement this functionality would be to modify the data at the data retrieval moment, so that on binding, RadGrid would have only date strings to form its groups, but not datetime. However, doing so would cause the entire date column to display only the date part of the field. If you need to keep the format as date/time, you will need to roll back the changes to the column before RadGrid is rendered on the page.

Attached is a sample project demonstrating this functionality. In NeedDataSource event handler of RadGrid, just before we set its data source, we modify the datetime column to contain only date values. This way RadGrid organizes its groups accordingly. Before rendering, however, we need to bring back the column values to the original, therefore we get the data from the data source a second time and modify RadGrid's cell values in the PreRender event.

Best wishes,
Veli
the Telerik team

Instantly find answers to your questions at the new Telerik Support Center

[Marco]

Dear Telerik Team,

I want to realize a similar situation as described by ben. My grid also has a DateTime column that I want to group on. Thus, I downloaded project you listed in your post (AJAXGridSortByShortDate.zip), extracted it and run it. You are grouping by "StartDate", thus, I would expect that the grid displays:

    StartDate: 24.07.2008
                Items with date = 24.07.2008

But the solution doesnt! I get the followong results:

    StartDate: 06.07.2008
            TestID = 6    TestName=Item 1 Level 3    StartDate: 25.07.2009 16:30:00

   StartDate: 24.07.2008
            TestID = 3    TestName=Item 1 Level 2    StartDate: 25.07.2009 16:00:00
            TestID = 4    TestName=Item 2 Level 2    StartDate: 24.07.2009 18:00:00
            TestID = 5    TestName=Item 3 Level 2    StartDate: 24.07.2009 12:00:00

   StartDate: 25.07.2008
            TestID = 1    TestName=Item 1 Level 1    StartDate: 24.07.2009 10:00:00
            TestID = 2    TestName=Item 2 Level 1    StartDate: 06.07.2009 18:00:00

This is not, what I expected. I have tried so many approaches with the radgrid but cannot get a solution with working grouping by date. Esspecially I would like the grid to display the items that way:

    StartDate: 24.07.2008
            TestID = x    TestName=Item x Level y    StartDate: 16:00:00    //It should just display the time, not the entire date

Using the approach of the demo, the DateTime column will be of type string. Thus, it will be impossible to sort by date (ascending or descending) inside a group.

Can you please help me of how to do this with the RadGrid or do you please have a working demo project that allows me to extract the knowledge to my project?

Kind regards and can't wait to hear from you,
Marco
            

[Veli]

Hello Marco,

Thank you for noticing. Indeed, the dates are messed up and this is due to improper restoration of dates on PreRender. You can find attached a modified version of the code-behind file. Grouping works OK now.

Best wishes,
Veli
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

[Marco]

Hallo Veli,

hmm, ok, yes it works for your sample. But let me show you, what I want to achieve:

In your demo, the RadGrid starts with GroupingEnabled set to true. Ok, this way, the grid starts in grouped mode. Thisis what I want. But I also want to be able to sort by date in this grouped mode. As an example, let us examine 24.07.2008 in your demo:

The grid displays the different dates in grouped mode properly and sorts the dates in descending direction. For 24.07 you get:
    1.) 3 Item 1 Level 2 24.07.2008 18:00:00 
    2.) 4 Item 2 Level 2 24.07.2008 12:00:00
    3.) 5 Item 3 Level 2 24.07.2008 10:00:00

Now I want the grid to sort also in ascending direction for the 24.07, like this:
    1.) 5 Item 3 Level 2 24.07.2008 10:00:00 
    2.) 4 Item 2 Level 2 24.07.2008 12:00:00
    3.) 3 Item 1 Level 2 24.07.2008 18:00:00

When I turn on "AllowSorting" for the Rad grid, I am able to sort by text and by numbers, but not for date when being in grouped mode. I guess that this happens, because the item's text is set in PreRender and thus not treated as a real date any longer.

Is there any solution to be anble to sort by date in grouped mode as mentioned above?

I am looking forward to hearing from you,
Marco

[Veli]

Hi Marco,

Note that you are sorting by the grouping field inside the group, which, effectively, is one and the same value for all rows. Hence your sort order does not change for sorting on columns used for grouping.

In this case, I need to sort my data outside of RadGrid. In RadGrid's NeedDataSource event, I check to see if a sort expression exists for StartDate column. If such an expression exists, I sort my data manually before binding it to RadGrid:

void RadGrid1_NeedDataSource(object source, GridNeedDataSourceEventArgs e)
{
    DataView view = AccessDataSource1.Select(new DataSourceSelectArguments()) as DataView;
    DataTable table = view.ToTable();

    //Check to see if sorting by StartDate and sort manually
    string dateColumnSortOrder = GetSortedOrderByField("StartDate");

    if (dateColumnSortOrder != string.Empty)
    {
        DataTable buffer = table.Copy();
        table.Clear();

        DataRow[] sorted = buffer.Select("", "StartDate " + dateColumnSortOrder);
        foreach (DataRow sortedRow in sorted)
        {
            table.Rows.Add(sortedRow.ItemArray);
        }
    }

    //Now we can safely get the short string of dates, as they are already sorted
    //as specified by the sort expression
    foreach (DataRow row in table.Rows)
    {
        OriginalDates[row["TestID"].ToString()] = (DateTime)row[2];
        row[2] = ((DateTime)row[2]).ToShortDateString();
    }

    RadGrid1.DataSource = table;
}

The rest of the code is the same. Here is the additional GetSortOrderByFieldName() method definition:

private string GetSortedOrderByField(string fieldName)
{
    foreach (GridSortExpression expr in RadGrid1.MasterTableView.SortExpressions)
    {
        if (expr.FieldName == fieldName)
        {
            return expr.SortOrderAsString();
        }
    }

    return string.Empty;
}

Check it out.

All the best,
Veli
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

[Marco]

Hallo Veli!

Thanks for your post / help. That was exactly what I needed.

But there still is an issue:

In my application, I have - as described - a column that stores dates in exactly the same format as your demo does, except that my application does have much more dates for each group. When I sort descending, it works perfect but when I switch to ascending, the column results in unsorted data!

It works in your demo but in mine with more data not. Again, it works for descend direction but not for ascending direction.

In order to enable you to see the problem I have isolated the part from my entire solution. I can find no way how to upload it to this discussion. Can you tell me how to do tht or can I directly send it to you?

Kind regards,
Marco

[Veli]

Hi Marco,

Unfortuntely, you cannot upload in a forum post. You will need to open a regular support ticket to upload a sample project, but I see that your support period has ended. I suggest you go over your project and try to identify the reason this is not working. Particularly, investigate what happens after you intercept the date sorting in RadGrid's NeedDataSource when you manually sort your data. The logic of the sample application I sent you should be clear - for date sorting I sort my data ASC or DESC before passing it to RadGrid for databinding.

Please, share with me your observations.

All the best,
Veli
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.