Group Report By DateTime field

Hello - I’m trying to use SuiteCRM 7.8.3 LTS and create a report for events. I want to group the report by start date, but start date is a datetime field so the group function sees each entry as unique. I’d like to have all of a single days events group together. Is there a way to add this customization?

Look here :wink:


1 Like

So I was able to add a DATE reference to use the mysql DATE function to aggregate by DATE. However I’m getting duplicate grouped fields. It seems that if I have 2 records for July 11 then I get two sections for July 11 both showing both events.

I can’t seem to insert a picture or add an attachment to this post. UGH!

Still looking for help here:

I need to have a report that is grouped by date. I have tried a number of workarounds to get this to occur. Each attempt seems to yield a new issue.

First I use start_date which is a datetime field. Grouping on just the data portion of a datetime field does not appear to be supported and I get groups for each unique date and time.

I added a function I was given that allows for inclusion of a sql function to do the grouping by just the date portion however when running the report it comes up blank.

I was able to get what I wanted by including a field that is just the date and doing the group by on that. That requires the user to fill out two fields - the date field I added and the start_date field since that includes the time portion. To eliminate the double entry I’d like a workflow or something to automatically fill out the date field based on what was entered in the start_date field, but that doesn’t seem to be working either - my assumption is because the start_date field is a datetime and I’m trying to set a just date field.

I’m not sure what the best way to accomplish my goal of a report grouped by date is and as you can see I’ve tried a number of things.

Any help is greatly appreciated.

Is it possible in SuiteCRM to group chart by month? Ie date field is the source data?

It doesn’t look like it is. The only thing I can think of is create a custom field “month” and then have a workflow to populate it with “january, february” etc, then I can run a grouped bar graph on “month” field.

Am I missing something? Is this not a feature of SuiteCRM reports? (I know spots has this feature, but its a custom field so not in spots).

Did you ever get an answer for your question? I am looking to do the same

The only way I was able to do this was create a custom field called billing month, then create workflow with a calculation to populate it with “january” for example for a date of 01/15/18 let’s say. Then I group the report by billing month and sort it by date between a certain time period. Of course if you’re doing a multi-year report this will not work unless you populate the billing month field with January18 for example.

But no, I haven’t found any easy way to just group by month.

tks B-)