Clicking through from a Dashlet bar chart to Calls grouped by Day:Start Date the results are always 1 day out?

Hi

I am setting up dashboards for the Sales Consultant role - and I want to show a dashlet on the Dashboard and also in side panels for Leads and Opportunities that will show Overdue calls, plus the next 30 days of calls Grouped by Date and Stacked by Call Subject (Our call subjects are predefined from a drop down so limited number) - I have created the report which shows this with no issue, and I can display it on the Dashboard and it looks great - but what I can't get past no matter what I try - is that when you click on a segment to drill down to the actual calls it represents - the calls displayed in the subpanel are always 1 day earlier than the date selected, even though the top of the screen shows the right date. Is there any way around this please? We are using Sugar Enterprise 9.01 (version is out of my control I am just the end user). I have tried every variant of report type but it makes no difference.

Many thanks

Parents
  • UPDATE - I have now discovered that calls scheduled on the 1st of March 21 or earlier do click through correctly - but any date after 1/3/21 exhibits the wrong date (trying to show the day before) error - is there anything in Sugar 9.01 that would cause this or is it likely to be something in dev code?

Reply
  • UPDATE - I have now discovered that calls scheduled on the 1st of March 21 or earlier do click through correctly - but any date after 1/3/21 exhibits the wrong date (trying to show the day before) error - is there anything in Sugar 9.01 that would cause this or is it likely to be something in dev code?

Children
  • don't know if that is relevant to this case - below is just a hypothesis to check

    It could be a timezone-related issue that happened on the way - AFAIK, the DateTime type values should be stored in UTC in the database, and Sugar automatically shifts them to the user's timezone when displaying.
    At the same time, for the Date type value a "time zone" entity is not applicable.

    So if your user's DateTime is 20.05.2021 01:00 in the interface, that is 19.05.2021 23:00 in UTC (in the database), and if the database value is cast to Date (where timezone is not applicable), then you will get 19.05.2021 date instead of 20.05.2021

    If your server is on-premise (as it should be for version 9) and you have a sandbox, you may try to set Grinvich time (UTC+0) for both server and as user's local time and check whether the issue disappear

    As an alternative to that, you may check whether the issue disappears if the report is built only for the records where timezone correction does not change the date -
    e.g. my time zone is +2 hours, so if the call start datetime is 20.05.2021 16:00 then it is stored in the database as 20.05.2021 14:00, therefore casting to Date will keep 20.05.2021 for that record

    Further investigation steps might depend on results

    Best Regards,
    Dmytro Chupylka

    integroscrm.com
    We make work in Sugar CRM system faster, more convenient and efficient