AACC\ACCS 7.X Custom reports will not retrieve data for the "time" field in MS excel

Doc ID    SOLN294589
Version:    1.0
Status:    Published
Published date:    15 Aug 2016
Author:   
joselag
 

Details

Custom reports will not retrieve data for the "time" field. Same Query appears to work in 6.4_SPXX

If you select "dSkillsetstat" in excel using the 6.4 system you are able to run the requested query, however you are not able to do the same query on 7.0 system
Error
[SQLCODE:<1>:<Invalide SQL statement>]
[Location:<prepare>]
[5msg:,Identififier expect, reserved word Time found ^SELECT dSkillsetStat, Timestamp, dSkillsetStat, SkillsetID,dSkillsetStat, Skillset, dSkillsetStat, ApplicationID, dSkillsetStat, Application, dSkillsetStat, ContactType,dSkillsetStat, ActiveTime, dSkillsetStat, AllAgentBustTime, dSkillsetStat, CallsAnswered, dSK
 


 

Problem Clarification

Custom reports will not retrieve data for the "time" field.

To access the Contact Center Manager Server database, you must define a connection to the server and define the Caché ODBC DSN. The name of the Caché ODBC DSN for CCMS must begin with CCMS_.

Procedure
1. On the client computer, click Start > Run.
2. In the Open box:
• If the operating system and ODBC driver are both 32–bit or 64–bit, type C:\Windows
\System32\odbcad32.exe
• If the operating system is 64–bit and the ODBC drivwer is 32–bit, type C:\Windows
\SysWOW64\odbcad32.exe
3. Click OK.
4. Click the System DSN tab.
5. Click Add.
6. Select Intersystems ODBC.
7. Click Finish.
The system displays the Intersystems Cache ODBC Data Source Setup dialog.
8. In the Name box, enter the required name of the DSN. For example, type
CCMS_PREVIEW_DSN.
9. In the Description box, enter a description of the DSN.
10. In the Host box, enter the IP address or computer name of the Contact Center server.
11. In the Port box, confirm that the port number is 1972.
12. In the Cache Namespace box, enter CCMS_STAT.
13. In the User Name box, type the user name of the desktop user account.
14. In the Password box, type the password of the desktop user account.
15. Click Test Connection to test the connection.
16. Click OK.
 

 

Cause

InterSystems is enforcing ODBC compliance in release 7.0 (database vendor used in product)

The failure reported by the database is caused by MS Excel not enclosing reserved word in double quotes.

Testing:


Testing shows the root-cause of the issue is not AACC, but rather it is in the way that Excel handles reserved words.
Excel fails to quote reserved words, and that is a requirement of the ODBC standard.

The example will show you why the failure is happening in 7 and not in 6.4

Our tests show; MS Excel creates the following query:
select dSkillsetStat.Time from dbo.dSkillsetStat

Tests using 6.4 cache:
a) select dSkillsetStat.Time from dbo.dSkillsetStat < This works
b) select dSkillsetStat."Time" from dbo.dSkillsetStat < This works
c) select "dSkillsetStat"."Time" from "dbo"."dSkillsetStat" < This works

Tests using 7.0 cache:
d) select dSkillsetStat.Time from dbo.dSkillsetStat < This fails
e) select dSkillsetStat."Time" from dbo.dSkillsetStat < This works
f) select "dSkillsetStat"."Time" from "dbo"."dSkillsetStat" < This works

Note: ODBC has always required reserved word Time to be enclosed in double quotes. And that was added to the view definition, however that is not the change that introduced the issue.

No relevant change to AACC was made
We did not change the database to make this fail, but after testing, we can see that the DB vendor (Intersystems) are using stricter ODBC compliance when reserved words are used in the new database version.
Basically; Cache introduced stricter enforcement of ODBC standard requiring reserved word to be quoted.

Examples b) c) e) and f) are compliant SQL as field names are quoted.
Examples a) and d) are not compliant SQL.

The root cause sits within MS Excel – MS Excel is not creating SQL with reserved word Time enclosed in double quotes.
NB: we cannot change this, as the root-cause is outside of AACC server, only the client can solve this as per examples from other clients like MS Access and WinSQL below. They must use legal SQL per ODBC standards .

Solution

Multiple workarounds within excel are available.

  1. Exclude the time from the Query
  2.  Run Query Excluding the time, select properties within MS Excel, Connection properties, Definition Tab, Highlight command text and insert Time making sure to use double quotes "time" re-run same query
  3. In excel select Data, From other sources, from Database Connection wizard, ODBC DSN, Connect to ODBC data source, select table complete wizard and run query.

Attachment File

Excel.png
0Bytes • < 1 minute @ 56k, < 1 minute @ broadband



Avaya -- Proprietary. Use pursuant to the terms of your signed agreement or Avaya policy