CUIC summary reports showing multiple dates per day due to time zone

Version 1
    This document was generated from CDN thread

    Created by: Luis Yrigoyen on 14-12-2012 04:15:11 PM
    Hi, I'm running CUIC version 9.0.1.
    I downloaded the supplemental summary reports from the CDN site but I'm running into issues.
    UCCE and CUIC are in UTC.  CUIC data sources are in UTC.  Users are in Mexico/General (UTC – 6).
    Basically, when we run summary reports from a system in UTC and users in UTC – 6, the summary reports split the days showing two dates per day and affecting some of the columns' numbers.
    CUIC is designed to adjust to the user's timezone.  When the users run the "detail" reports (the reports showing the day's intervals i.e. 15 min or 30 mins) the data is displayed with the user's local date and time.  The user sees his/her local day displayed from 0:00 hours to 11:59 PM when in reality in the DB the data is 06:00 hours ahead--this works fine. 
    It's only when we run "summary" reports (Daily, Weekly, Monthly)  that the date mask gets distorted.
    The summary reports are basically the same reports as the "All Fields" reports but with the time zeroed out to be able to group
    them i.e. DATEADD(day,DATEDIFF(day,0,DATE FIELD,0).  CUIC doesn't seem to like this.
    I've also tested with CONVERT(datetime, CONVERT(varchar(10), Date Field, 112)) but no go.

    A colleague was able to fix this issue in a CUIC 8.5 server by converting the date to string so that CUIC will not try to change it but that didn't work.
     
    In the Call Type Historical All Fields Daily
    chaged this:
    Interval=dateadd(day,  datediff(day,0,  CTHH.DateTime)
    to this:
    Interval=convert(varchar(12),dateadd(day,  datediff(day,0,  CTHH.DateTime), 0),107)
     
    thanks
     

    Subject: RE: CUIC summary reports showing multiple dates per day due to time zone
    Replied by: Luis Yrigoyen on 27-12-2012 09:14:26 AM
    I was able to fix this issue by:
    1.Making a copy of the interval definition (the all fields stock definition) and renaming it - Dialy or weekly, etc.
    2. In the new definition, change the Interval field (DateTime) format to mm/dd/yyyy.
    3. Create a new report and point it to the new definition
    4. In the grouping section, use the Interval as one of the grouping fields and choose one of the built in summarization filters (Daily, Weekly, Monthly)
    5. IN the same grouping section check the "Show Summary Only" check box