Hi Francesca Shiekh,

Would you be able to provide a list of your filters, display summaries, and group by selections that I can replicate the report locally to investigate.

thanks Liam.

FrancescaS

Hi Francesca Shiekh,

There is no reason that I can see here why it would be shown twice as you only have one group by column. On the 'Report Details' step would you be able to tick the 'Show Query' checkbox (seen under the Report Name field) and then preview the Report. This will show a list of queries under the Report (in this instance under the summation table) that shows what the Report is using the generate the results.

Yes, that's what I did and how I got the query to run in mysql, that's where things get strange, the same exact query gives different results in reports than it does in straight sql.

The only oddity in the query is this portion of the where statement:

OR (cases_cstm.case_closed_reason_c IS NULL AND 'Spam' IS NOT NULL)

this is the line item query from show sql:

SELECT DATE_FORMAT(cases.date_entered - INTERVAL 360 MINUTE,'%Y-%m-%d') cases_day_date_entered,DATE_FORMAT(cases.date_entered,'%Y-%m-%d %H:%i:%s') cases_dayreal_date_entered,COUNT(cases.id) cases__allcount, COUNT(DISTINCT cases.id) cases__count

FROM cases

LEFT JOIN cases_cstm cases_cstm ON cases.id = cases_cstm.id_c

WHERE (((cases_cstm.case_department_c = 'TS'

) AND (cases_cstm.case_closed_reason_c <> 'Spam' OR (cases_cstm.case_closed_reason_c IS NULL AND 'Spam' IS NOT NULL)) AND (cases.date_entered >= '2016-02-17 06:00:00' AND cases.date_entered <= '2016-03-18 04:59:59'

)))

AND cases.deleted=0

GROUP BY DATE_FORMAT(cases.date_entered - INTERVAL 360 MINUTE,'%Y-%m-%d')

  ORDER BY cases_day_date_entered ASC

and for the totals:

SELECT COUNT(cases.id) cases__allcount, COUNT(DISTINCT cases.id) cases__count

FROM cases

LEFT JOIN cases_cstm cases_cstm ON cases.id = cases_cstm.id_c

WHERE (((cases_cstm.case_department_c = 'TS'

) AND (cases_cstm.case_closed_reason_c <> 'Spam' OR (cases_cstm.case_closed_reason_c IS NULL AND 'Spam' IS NOT NULL)) AND (cases.date_entered >= '2016-02-17 06:00:00' AND cases.date_entered <= '2016-03-18 04:59:59'

)))

AND cases.deleted=0

I can reproduce the problem consistently in V7.6.2.1

It appears to be a display issue, not a query issue.

I think it's a bug in Reports. Alex Nassi do you know of any Reports bugs that might account for this?

thanks,

Francesca

May be related to:

Defect 75640: Report Summation with Details showing duplicate groups: SugarCRM, Inc.

Will be interesting to see what Alex Nassi says as I have tried this on a fresh demo instance for version 7.6.2.1 Enterprise that has no filters and simply groups on the day of the Date Created, and it correctly shows a single row for each date of:

• 20/09/2013 -> 247 records
• 11/06/2015 -> 2 records
• 15/06/2015 -> 4 records
• 17/06/2015 -> 4 records

Francesca,

Just taking a quick glance at this, is it possible that there is a GMT offset that is occurring when you run the query via the UI but then that offset is not occurring when you run the query directly in the database?

Kind Regards,

Lori F

Thank you Lori F, this doesn't explain why the group appears twice in the report

I think Lori F's theory is that if you have say +5 hours on your profile then it might be causing your profile to read the date as tomorrow, with the database still thinking it as today.