We’re trying to create a report that tally’s up all of the calls handled for a specific Group # (customvariable3) and displays it per contact center area. I’ve included the xml Report Template Definition from our 8.6 environment. The result looks similar to:
In CUIC 10.5, we need the report to have a date filter on it like this:
The problem is, the results we get don’t tally up because of the inclusion of the date field in the results (which is expected).
select sdt, business_unit, grp as group, count(grp) as calls_handled
from (
select startdatetime as sdt, 'Business Unit A' as business_unit, TRIM (LEADING '0' from UPPER(TRIM(customvariable3)))as grp
from contactcalldetail
where TRIM(LEADING '0' from TRIM(customvariable3))in ('1001','1002','1003','1004','1005')
and contacttype = 1
and contactdisposition = 2
and applicationname in ('900_CustSvc_1', '900_CustSvc_2','900_CustSvc_3')
UNION ALL
select startdatetime as sdt, 'Business Unit B' as business_unit, TRIM (LEADING '0' from UPPER(TRIM(customvariable3))) as grp
from contactcalldetail
where TRIM(LEADING '0' from TRIM(customvariable3)) in ('2000','2001','2002','2003','2004')
and contacttype = 1
and contactdisposition = 2
and applicationname in ('900_CustSvc_1', '900_CustSvc_2','900_CustSvc_3')
UNION ALL
select startdatetime as sdt, 'Business Unit C' as business_unit, TRIM (LEADING '0' from UPPER(TRIM(customvariable3))) as grp
from contactcalldetail
where TRIM(LEADING '0' from TRIM(customvariable3)) not in ('3001','3002','3003','3004','3005')
and contacttype = 1
and contactdisposition = 2
and applicationname in ('900_CustSvc_1', '900_CustSvc_2','900_CustSvc_3')
UNION ALL
select startdatetime as sdt, 'Business Unit D' as business_unit, TRIM (LEADING '0' from UPPER(TRIM(customvariable3))) as grp
from contactcalldetail
where ( TRIM(LEADING '0' from TRIM(customvariable3))= ''
or customvariable4 is NULL)
and contacttype = 1
and contactdisposition = 2
and applicationname in ('900_CustSvc_1', '900_CustSvc_2','900_CustSvc_3')
)
group by business_unit, grp, sdt
I've been able to get the report to do the following using the grouping option, but again it doesn't tally up the calls handled...
So the question is what is it that I'm missing? I think I don't quite understand how the grouping option works but I'm not sure if that's the whole issue. Any help would be very much appreciated.
Kris
Accepted Solutions
Hi Jameson,
I don't quite follow in regards to rewriting the query and substituting a CASE statement. I removed the start date time (sdt) from the outer query and tried out the Filter Field. It seems it gives me a "Dataset status is failed Database Error" as follows:
| Error information: |
| com.cisco.ccbu.cuic.businesslogic.datasource.CuicDbException: DbException: CuicDataSourceServiceManagerImpl.getDataSet() { Nested SQLException; SQLState: 42000 Vendor code: -201 Message: A syntax error has occurred. } at com.cisco.ccbu.cuic.businesslogic.datasource.CuicDataSourceServiceManagerImpl.getDataSetBuilder(CuicDataSourceServiceManagerImpl.java:952) at com.cisco.ccbu.cuic.businesslogic.engine.CuicReportEngineWorker.executeQueryUsingDatasetBuilder(CuicReportEngineWorker.java:80) at com.cisco.ccbu.cuic.businesslogic.engine.CuicReportEngineWorker.runReport(CuicReportEngineWorker.java:37) at com.cisco.ccbu.cuic.businesslogic.enginebase.Worker.run(Worker.java:329) at com.cisco.ccbu.infra.threads.InstrumentedRunnable.run(InstrumentedRunnable.java:92) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908) at java.lang.Thread.run(Thread.java:619) at com.cisco.ccbu.infra.threads.ThreadPoolThread.run(ThreadPoolThread.java:164) Caused by: java.sql.SQLException: A syntax error has occurred. at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:408) at com.informix.jdbc.IfxSqli.a(IfxSqli.java:3461) at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3774) at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2580) at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2496) at com.informix.jdbc.IfxSqli.executePrepare(IfxSqli.java:1360) at com.informix.jdbc.IfxPreparedStatement.e(IfxPreparedStatement.java:320) at com.informix.jdbc.IfxPreparedStatement.a(IfxPreparedStatement.java:300) at com.informix.jdbc.IfxPreparedStatement.(IfxPreparedStatement.java:170) at com.informix.jdbc.IfxSqliConnect.h(IfxSqliConnect.java:6537) at com.informix.jdbc.IfxSqliConnect.prepareStatement(IfxSqliConnect.java:2453) at sun.reflect.GeneratedMethodAccessor562.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at com.mchange.v2.c3p0.stmt.GooGooStatementCache$1StmtAcquireTask.run(GooGooStatementCache.java:525) at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547) Caused by: java.sql.SQLException at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:408) at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3779) ... 13 more |
No clue what that means... I'm assuming it doesn't like substituting the Filter Field date into the query for some reason?
Hi Jameson,
I got things close to working using your suggestion. Oddly enough, I get the following:
Looks like business_unit is returning a Boolean for some reason. It looked like it forced it to that when I ran the Create Fields button on the Data Source page. I can't seem to find a place to redefine it as a string instead and the Delete doesn't work to remove it either. Am I doing something wrong? I ran the same query through MS SQL Server Management Studio against the Informix DB and it came out with the proper string values Business Unit A, B, C in that first column... So does the reporting tool expect something different in the query?
