Grouping dates in reports not working as expected

Vicky Athersmith

We have started using the grouping function within several reports and grouping on a date field but we are coming across tasks with the same dates not appearing within the same grouping? We are not grouping by any other column and the formatting of all dates across all sheets is the same?

Has anyone else discovered this?

Any ideas why this might be happening?

Screenshot grouping of dates.png

Thanks

Vicky Athersmith

I may have found a solution!

@Paul Newcome made a suggestion on a different post to create an additional hidden date type column on your source sheet and use

=DATEONLY([Created (date)]@row)

Then include this column in your report (it can also be hidden in the report) and sort on that.

I tried this and it seems to have worked!! :-)

Thanks @Paul Newcome

https://community.smartsheet.com/discussion/74252/report-grouping-by-system-generated-date

Paul Newcome

@Vicky Athersmith I'm glad one of my other posts was able to help with a solution here.

@Katie Liestman Have you tried Vicky's suggestion?

Paul Newcome

Try adding another row in the EDL065 sheet with a date of Jan 26. Does that do the same thing?

Vicky Athersmith

Screenshot grouping of dates1.png

It is doing the same thing on other dates with the EDL065 sheet but this is not limited to this sheet. You can see it is also doing it on different sheets on Feb 2?

Vicky Athersmith

To throw in extra confusion, here you see two tasks from the same sheet EDL352 split on Feb 18th?

Screenshot grouping of dates2.png

Mark Cronk

Hi Vicky,

You have the time master helping you, but I'll ask are you calculating your due date ? If so, what formula are you using?

Mark

Paul Newcome

I would agree that we need to know exactly how your due date is being populated.

Are you able to provide screenshots of exactly how the report is built?

Vicky Athersmith

Hi Mark & Paul,

Appreciate both of your insight on this :-)

My Due Date column in all of my sheets is driven by the Duration column as all my sheets are dependency driven and I use a template when creating my sheets so I am certain they all have exactly the same column type. I have attached screen shots of the column type and project settings from a sheet.

I have provided screen shots of how the report is built also (see attached).

Due Date Column Properties.png

Report Columns - Due Date.png

Report Filters.png

Report Group.png

Report Sort.png

Report Summary.png

Sheet Project Settings - Dependency Settings.png

Thanks, Vicky

Paul Newcome

Have you tried building a new report that mimics this to see if it is just a bug in this one?

Vicky Athersmith

https://community.smartsheet.com/discussion/comment/276339#Comment_276339

Great suggestion Paul. I had not tried building a new report so just did so completely from scratch and it is identical with exactly the same splitting of dates.

I can only assume this is something to do with the dates being driven by the duration field and I am not sure how to test this without having to create some duplicate test sheets and changing the settings to remove the dependency so duration is not driving the dates? Although not sure this is something I want to spend time doing as if this is the cause it doesn't help me as I need the dependencies enabled on all sheets.

Paul Newcome

My suggestion at this point would be to log completely out of Smartsheet and close all windows. Open your browser. Clear your history, cookies, and cache. Log back in.

If that doesn't work, try taking a couple of the sheets that are getting dates split and putting them into a folder. Save the folder as new and you have effectively copied the sheets all in one go. Now you have some test sheets you can fiddle with. Try setting up a new report looking at these sheets. Maybe it was something on the back-end of the sheets themselves and creating copies of the sheets to replace the original ones is the solution. Try disabling the dependencies. Maybe insert a new column, entering your durations there then adjusting your dependency settings to look a the new column.

Either way, I suggest looping in support to see what they have to say about it.

Vicky Athersmith

Thanks Paul

I have loped in support also. I will do some more testing when I get some time and let you know how I get on.

Appreciate all your advice :-)

Paul Newcome

Sorry I couldn't be of more help. Hopefully it is in fact a bug and that support can easily correct it on the back-end.

Katie Liestman

I have the same issue going on - @Paul Newcome - were you able to solve it?

Vicky Athersmith

https://community.smartsheet.com/discussion/comment/299215#Comment_299215

Hi Katie,

I have not yet been able to solve this myself as the solution provided to me required me try a different browser and save the source sheets "as new" is not feasible whilst in the middle of projects (sheets) as they are linked out to so many different places.

Here is the information I was provided with from support, if this helps you.

"During my testing, I created a sheet with hierarchies and the required "date" type column for a date grouping. I applied the same day and different days to different rows on the sheet and created report. The days grouped together showing rows with the dates under the day's grouping in the report.

 I advise to attempt to remove the addition sorting/filtering criteria and just implement the day grouping. Then increate the criteria once the day grouping is displaying correctly to better assess what is causing your rows with the same due date to be grouped separately. 

If you find you are unable to get your grouping display correct, I would advise trying in a different browser and saving the source sheets "as new" and re-creating the report from the copies."

Please do let me know if you have any success in resolving this.

Thanks, Vicky

Vicky Athersmith

@Katie Liestman I believe the issue occurs when I am using hours as duration in my sheets. Are you using hours too?

Vicky Athersmith

I may have found a solution!

@Paul Newcome made a suggestion on a different post to create an additional hidden date type column on your source sheet and use

=DATEONLY([Created (date)]@row)

Then include this column in your report (it can also be hidden in the report) and sort on that.

I tried this and it seems to have worked!! :-)

Thanks @Paul Newcome

https://community.smartsheet.com/discussion/74252/report-grouping-by-system-generated-date