CUIC 8.5(4) Daily Summary Report Doesnt Time Zone Adjust On Display

[Faisal Zaidi]

User configured in a EST (GMT -5) the system is in CET (GMT +1)

When the report is run the DateTime passed to the query is adjusted, ie, if an American user select 21st Oct 00:00:00 to 21st Oct 23:59:59 (ie the data for the 21st Oct only is saught) the query run converts the user enetered DateTime to the corresponding system DateTime, ie, 21st Oct 06:00:00 to 22nd Oct 05:59:59.

However, the data returned to screen doest combine the data for the 21st only, there are columns output for the 21st and the 22nd also. The Columns returned are correct in that they are for 21st Oct 06:00:00 to 22nd Oct 05:59:59, but they should be displayed in a single column for the user entered date 21st Oct only.

This only seems to affect the Summary reports available to download here and not the CUIC stock reports which display correctly. Please complare the 'Agent Summary - Daily' summary report with 'Agent Historical' stock report - screen shots can be provided if required.

Is there a simple way to ensure the data rendered on screen displays the user entered date only or will this require manual manipulation of the SQL to achieve?

Thanks! 

[Jameson Gagnepain]

Because the SQL query is doing the summarization, and has no knowledge of the user's Time Zone, there is no easy way to overcome this. The only way I can think of to get around this limitation in an automatic way is to make custom SQL queries for that site.

Personally I've never noticed this issue because all of our site's business hours fall within the same "day" on the server.

[Faisal Zaidi]

Thanks James. That is what I expected but what I don't understand is how the stock reports display correctly. If you look at the raw data returned by a stock reports SQL, that too is spread over 2 days for the intervals in question but CUICs UI somehow figures out that according to the users time zone they all the data relates to the same dau for them. Any ideas?

[Jameson Gagnepain]

The stock reports give a full DateTime value, which CUIC knows how to translate into the user's local time zone.

With the summary reports, the summarization is in the SQL query, so CUIC is only getting the one or two rows back that you're seeing... it then translates the returned DateTime value in those rows to the user's local time zone, but the Report view is typically built such that it only displays the Date portion of the full DateTime value.

If you want to see the difference between what the SQL is returning and what CUIC displays, take the SQL that CUIC generates (from the SQL button in the report results), and run that directly against your UCCE server.

[Faisal Zaidi]

Hi Jameson

That is usually the case but not always.

See the attached document for an example using the stock “Agent Historical All Fields” report.

Again the CUIC user is in EST and system in CET.

The SQL query sent does modify to the allow for time zone adjustment, the SQL results however return the expected intervals over 2 days but the UI somehow knows to display all of the results in the users time zone, ie all under 1 day (21st Oct 2013).

Modifying the SQL is possible but considering global deployments (various time zones) and when to apply DST (EST is already in summer time where CET is not etc) this becomes very complex. I am hoping that however CUIC is making this adjustment can also be applied to the summary reports as this is probably the most reliable way…

[Jameson Gagnepain]

This is standard CUIC behavior:

• User inputs filter criteria (DateTime, etc). CUIC translates user-specified date-time range into server date-time.
• SQL query is run.
• SQL results returned to CUIC.
• CUIC displays results, translating all "DATETIME" type values back into the user's time zone.

The problem with summary reports is entirely to do with what SQL returns to CUIC. In the case of a daily summary, run from a Oct 21 6:00 to Oct 22 5:59 period as in your example:

• The SQL query for the daily report summarizes based on server DateTime values, so one row per server "Day".
• In this example, it generates two rows, because the server sees two seperate days in the time period. Rows are generated as "Oct 21 0:00" and "Oct 22 0:00".
• Results are returned to CUIC. CUIC is returned two rows, so it displays two rows. It normally translates the DATETIME values back into the user's time zone... however, the report is only set to display the Date portion, so just shows "Oct 21" and "Oct 22".

SQL is returning the rows to CUIC, CUIC has no control over how many rows it's getting back.. If you want it to summarize differently, the only way to do that is custom SQL.  The CUIC stock reports display correctly because none of their data is summarized across date lines.

[Faisal Zaidi]

Thanks Jameson

What I’m particularly interested in is your 4th bullet:

• CUIC displays results, translating all "DATETIME" type values back into the user's time zone.

So, how does CUIC do this, is it part of the report definition perhaps? And is this something that can also be applied to the summary reports?

[Jameson Gagnepain]

Every field definition in a Report Definition has a data type to it, corresponding to whatever data type SQL returns. Unless you're casting data differently in the SQL query, these types will correspond directly to the types in the DB table definitions. "DATETIME" is one of those types, and that is what you'd generally get back for any DateTime, DBDateTime, etc fields in call center.  The "Format" of that field in CUIC is what determines how CUIC displays it. If that format is set to display only the Date (as it is set for the summary reports), no Time Zone translation is done. If the format of the field shows a Time, then the Time Zone translation is applied to that field.

I think you're missing an important piece here: CUIC will not change summarization of data that SQL returns to it! It can add summary rows, sure, but it won't change row content besides the obvious formatting changes like with DateTime fields. In the summary reports, the data is already summarized by the time CUIC gets a hold of it. CUIC would have no way to tell how much of that summarized data is for one day or another in the user's time zone.  CUIC is largely a fancy frontend for SQL queries... it can do format changes, but data is data. It's not going to combine rows for you. If you want the row divisions to change, you need to do it in the SQL.

[Faisal Zaidi]

But CUIC appears to do exactly that – did you receive the example word doc I sent? The query clearly returns 2 days data but CUIC shows all of the data as 1 day as it would be for the users time zone… how?

[Faisal Zaidi]

also if

“The "Format" of that field in CUIC is what determines how CUIC displays it. If that format is set to display only the Date (as it is set for the summary reports), no Time Zone translation is done. If the format of the field shows a Time, then the Time Zone translation is applied to that field.”

Then dont we just need to apply the same format as the stock reports?

[Jameson Gagnepain]

That's not a change in summarization, it's a formatting on the DateTime field within CUIC. CUIC isn't changing the number of rows, or the data within the rows... it's just doing a direct translation of the contents of a DATETIME field into a different time zone.

If you change the format of the "DateTime" field that the summary reports give, to show a Date and Time instead of just the Date, you'll still get your two rows, it will just show them as Oct 20 18:00 and Oct 21 18:00. Remember I said:

• In this example, it (SQL) generates two rows, because the server sees two seperate days in the time period. Rows are generated as "Oct 21 0:00" and "Oct 22 0:00".

Those 0:00 generalizations of the date will get translated to your local time zone if your CUIC field formatting includes the Time portion of the returned DateTime, which in this case would be 6PM the day before.

No formatting changes are going to combine rows together!

[Jameson Gagnepain]

I was just looking at the guides for CUIC 10.0, and it looks like you can do daily summaries with Time Zone adjustments in that version... take a look at the section on Grouping:

http://www.cisco.com/c/en/us/td/docs/voice_ip_comm/cust_contact/contact_center/intelligence_suite/intelligence_suite_1001/user/guide/CUIC_BK_C8B3A26D_00_cuic-customization-guide/CUIC_BK_C8B3A26D_00_cuic-customization-guide_chapter_0111.html#CUIC_TK_G...

CUIC 10.0 can group by day, and then show only the summary... so you'd be able to have a stock All Fields report with a daily summary view, and the data would be in the proper time zones because the SQL query would not be doing the summarizing.  Pretty slick, sounds like a geat reason to upgrade.

[Faisal Zaidi]

Thanks for looking into this further.

I still have some doubts about this as a way forward as there is still nothing adjusting the rows returned by SQL to the users time zone (and hence the same day for the users locale). So, if there are 2 days returned by the SQL (Mon 21 Oct and Tue 22 Oct) then grouping by day would still give a row for each day returned, hence, 2 days in SQL = 2 grouped rows in CUIC unless some time zone adjustment is performed. Is this correct?

We upgraded to 8.5(4) under the impression that time zone adjusting is automated for reports. The reality is that 8.5(4) only performs user time zone adjusting for stock reports only. What should/needs to happen is that ALL reports should automatically time zone adjust for the users locale regardless of if they are out-of-the box or bespoke.

[Jameson Gagnepain]

I keep trying to tell you... CUIC DOES adjust rows returned by SQL to the user's time zone, as long as you're displaying the full Date and Time in the report output. If you build a custom report definition that includes Date and Time in the output as a DATETIME data type, CUIC will change that result into the user's Time Zone. I've built a few reports like this myself, it's very easy.  To prove my point... attached is a report I built a few months back which takes Call Type data across whatever dates you supply, and summarizes by half-hour intervals (ignoring the Date portion of DateTime... great for showing busy hours). ALL data returned is in the user's Time Zone.

If the SQL is returning two DAYS, and you're displaying them as DAYS, Timezone is pretty meaningless on that summarized data.  CUIC should never arbitrarily combine 21 Oct and 22 Oct, because it has no idea how much of the data in each of those rows actually belongs to the user's "21 Oct", and how much actually belongs to the user's "22 Oct".

A more extreme example... what about the Monthly reports? What if you're running a Monthly summary report across 2 months of user time (say September-October 2013)? How would you expect CUIC to shift some of the values from the first few hours of October into the totals of September? If SQL is summarizing the data, CUIC has absolutely no knowledge that there were even different hours of the day involved in the Month calculations.

The issue here has nothing to do with Stock reports vs Custom reports, it's to do with {SQL summarizing DateTime} vs {SQL returning all DateTime values and CUIC summarizing DateTime after TimeZone adjustments}. My point is that in 10.0, you can build reports whose SQL returns half-hour summaries, then let CUIC do the Time Zone adjustment, then CUIC summarizes based on the Date portion of the adjusted DateTime value, and shows only those summaries instead of the half-hour data. This achieves exactly the result you're looking for.

Plain and simple, if you want CUIC to do TimeZone adjustment on returned DateTimes, you need to return a DateTime, not just the date.

[Faisal Zaidi]

Sorry Jameson – I think I understand what it is that you are saying now – thanks..

Let me work through your example and a few of my own and get back to you.

Thanks again!