Lucent Technologies CentreVu Call Management System Custom Reports Manual

							  Advanced Report Design CentreVu CMS Custom Reports
Including Exceptions Data in a Custom Report6-21
Including Exceptions Data in a Custom Report6
For historical custom reports only, you can design reports that include 
exceptions data. See the 
CentreVu CMS R3V8 Database Items and 
Calculations 
(585-210-939) document for a description of the exceptions 
tables and the data they contain.
The steps for creating a custom report with exceptions data are almost 
identical to the steps for creating any other custom report. As with any other 
type of data, you must specify the database item(s), the table(s), the row 
search conditions, and the report input fields. However, you cannot copy any 
standard exceptions report on the Screen Painter. The exceptions reports do 
not appear if you select List all in the Get Copy of Design window.
Also, in every exceptions table, the database item EXTYPE stores the types 
of exceptions that occurred. However, exception types are stored as 
numbers, not as character strings. If you want your report to list the types of 
exceptions that occurred, then the types must be listed as numbers (see the 
CentreVu CMS R3V8 Database Items and Calculations (585-210-939) 
document for a description of these numbers). The VDN Exceptions Report 
shown in Figure 6-4
 is an example of this.
Figure 6-4:  VDN Exceptions Report — Sample 1
If you designed a custom exception report to replicate the standard VDN 
Exceptions Report, then the design might appear as shown in Figure 6-5
.
VDN Exceptions
ACD: burbank Printed 04/10/93 04:35 PM
Date: 04/09/93
Time
Time VDN Vector Exception Limit Threshold
------------------------------------------------------------------------------------------
09:16AM Catalog Sales Weekday Sales Number calls abandoned in vector 20
09:24AM Catalog Sales Weekday Sales Calls in ACD split queue 10
10:02PM Catalog Sales Off-hour Sales Calls disconnected 50 
						

							  Advanced Report Design CentreVu CMS Custom Reports
Including Exceptions Data in a Custom Report6-22
Figure 6-5:  Custom Exceptions Report — Sample 1
Field Window
Select: vdnex.THRESHOLD_________________
________________________________
Field Window
Select: vdnex.STARTTIME________________
________________________________
Field Window
Select: vdnex.EXTYPE____________________
________________________________
Field Window
Select: vdnex.TIME______________________
________________________________
Field Window
Select: vdnex.VDN_______________________
________________________________
Field Window
Select: vdnex.VECTOR____________________
________________________________
Screen Painter
ACD: $$$$$$$$$$$$$$$$$$$$
Date: $$$$$$$$VDN Exceptions
Time VDN Vector Exception Limit Threshold
________________________________________________________________________________________
0vvvvv 0vvvvvvvvvvvvvvvvvv 0vvvvvvvvvvvvvvvvvv 0vvvvvvvvvvvvvvvvvv 0vvv 0vvv 
						

							  Advanced Report Design CentreVu CMS Custom Reports
Including Exceptions Data in a Custom Report6-23
Figure 6-6
 (shown on the previous page) is an example of the report that 
results from this design shown in Figure 6-5
.
Figure 6-6:  VDN Exceptions Report — Sample 2
The report would appear this way because 
CentreVu CMS exceptions tables 
store the exception types as numbers.
However, another way of designing an exception report would be to use 
count(*) for the exception field. You could then include a specific 
exception type as part of the row search (Figure 6-7
). CentreVu CMS would 
then count the rows that had that exception type and display the total in the 
report. 
VDN Exceptions
ACD: burbank Printed 04/10/93 04:35 PM
Date: 04/09/93
Time
Time VDN Vector Exception Limit Threshold
------------------------------------------------------------------------------------------
09:16AM Catalog Sales Weekday Sales 37 32 20
09:24AM Catalog Sales Weekday Sales 37 30 10
10:02PM Catalog Sales Off-hour Sales 76 73 50 
						

							  Advanced Report Design CentreVu CMS Custom Reports
Including Exceptions Data in a Custom Report6-24
Figure 6-7:  Custom Exceptions Report — Sample 2
 
07/19/93 00:30 AM CentreVu(TM) CMS Windows: 1 of 5 ^ 
Custom Reports: Hist: agent excepts
Split: Sales-West
Date: 06/08/92
Field Window
Select: count(*)______________________________
______________________________________
Row Search
Table: agex_____________________________
Select rows where: EXTYPE = 7 and SPLIT = $i_split Exception
Type Number
Time in ACW 11
Time in AUX 3
Time AVAIL 14
Time on Ext-in 10
and ROW_DATE = $i_date_________
Field Window
Select: count(*)______________________________
______________________________________
Row Search
Table: agex_____________________________
Select rows where: EXTYPE = 4 and SPLIT = $i_split
and ROW_DATE = $i_date_________ 
						

							  Advanced Report Design CentreVu CMS Custom Reports
Selecting Rows from More Than One Table6-25
In the example, the row search statement for the first field searches for 
exception type #4 (Time in ACW) for a particular date and split. Because the 
field is count(*), the field displays the total number of rows that had 
exception type #4 for the date and split.
Similarly, the row search statement for the second field searches for 
exception type #7 (Time in AUX) for a particular date and split. Again, 
because the field is count(*), the field displays the total number of rows 
that had exception type #7 for the date and split.
Selecting Rows from More Than One Table6
For historical reports only, you can merge data from two tables into a 
single report field. You can, for example, take the number of ACD calls a 
single agent handled (where data is taken from the dagent table) and 
divide by the total ACD calls handled by the agents split (where data is taken 
from the dsplit table). Or, you can take the ACD calls a split handled in an 
intrahour interval (where data is taken from the hsplit table) and divide by 
the total ACD calls the split handled for the day (where data is taken from the 
dsplit table).
To merge data from two tables into a single report field:
lThe two tables must have at least one database item in common. 
Typically, the database items in common are indexes.
lBoth table names must be entered in the Table field of the Row 
Search ID assigned to the field.
lFor custom historical reports — you can use data from more than one 
table and use the same row search ID for multiple tables.
lAt least one “join” clause must appear in the Row Search ID assigned to 
the field. A “join” clause searches for the same value for the same item 
in both tables. In this way, the data extracted from the rows in both 
tables will be related.
A “join” clause has the following format:
tablename1.item1 = tablename2.item1
Where item1 is a database item that the tables have in common.
NoteFor retrieval of data from an exceptions table, you can only 
retrieve data for those exceptions that have been turned on for 
the particular split, agent, etc., have actually occurred and have 
not been deleted from the database because they exceeded the 
storage parameters for exceptions. 
						

							  Advanced Report Design CentreVu CMS Custom Reports
Selecting Rows from More Than One Table6-26
lYou must prepend the table name to each database item included in 
the Select rows where: statement. This rule applies to all 
database items, even those that are not in a join clause.
lA join clause must use a database item that is also included with a 
regular “where” clause (one that directly assigns a value or variable 
name to the database item).
Often, you need several “join” clauses in a Row Search criteria statement. 
For example, you must first specify row search values for either the hagent 
or hsplit table if you define a report field that contains the following data 
expression:
hagent.ACDCALLS/hsplit.ACDCALLS
You might enter the following “where” clauses:
hsplit.SPLIT = $i_split and hsplit.ROW_DATE = $i_date and 
hsplit.STARTTIME = $i_time and hsplit.ACD = $acd
You must then specify join clauses so that the rows found in one table are 
related to the values found in the other table. In this example, you would add 
join clauses (shown in bold) for every regular “where” clause:
hsplit.SPLIT = $i_split and hsplit.ROW_DATE = $i_date 
and hsplit.STARTTIME = $i_time and hsplit.ACD = $acd 
and hsplit.SPLIT = hagent.SPLIT and hsplit.ROW_DATE = 
hagent.ROW_DATE and hsplit.STARTTIME = 
hagent.STARTTIME and hsplit.ACD = hagent.ACD
The report design shown in Figure 6-8
 illustrates how this row search 
selection would affect data in a report.
NoteThe syntax of a join clause is similar to that in standard 
INFORMIX SQL syntax. See the INFORMIX-SQL Relational 
Database Management System User Guide
 for INFORMIX SQL.
CautionIf you run a report that merges data from two tables (particularly 
tables with large amounts of data) into a single field and your 
Select rows where: statement is not specific enough, you 
may get an error message when you test the design. The 
specific cause may be that the number of selected rows is very 
large, and 
CentreVu CMS does not have enough space to 
create temporary files. If this is the case, you should add 
additional “where” clauses to the row search criteria.
NoteNotice that table names are prepended to each database item. 
						

							  Advanced Report Design CentreVu CMS Custom Reports
Selecting Rows from More Than One Table6-27
Figure 6-8:  Report Design with Data from Two Tables Merged in a Field
 
04/19/95 00:30 AM CentreVu(TM) CMS Windows: 1 of 5 ^ 
Custom Reports: Hist: agent excepts
Split: $$$$$$$$$$$$$$$$$$$$ Split ACD
Date: $$$$$$$$ Calls
Field Window
Select: hagent.LOGID__________________________
______________________________________
Add highlights
Bar
Copy block
Define input
Erase block
Field
Get copy of design
Move block
Row search
Save design
Test design
Upper/left no-scroll
Variable/time/date Time: $$$$$$$$$$
Percent
Agent ACD of Split
Agent Calls Calls
0vvv 0vvvvv 2vvvvvvv1xxxxx
Field Window
Select: 100*(hagent.ACDCALLS/hsplit.ACDCALLS)__
_______________________________________
Field Window
Select: hagent.ACDCALLS_______________________
______________________________________
Row Search Window
Row Search ID: 2
From table(s): hsplit, hagent
Select rows where: hsplit.SPLIT = $i_split and hsplit.ROW_DATE = $i_date
and hsplit.STARTTIME = $i_time and hsplit.ACD = $acd
and hsplit.SPLIT = hagent.SPLIT and hsplit.ROW_DATE =
hagent.ROW_DATE and hsplit.STARTTIME = hagent.STARTT
IME and hsplit.ACD = hagent.ACD
Field Window
Select: hagent.ACDCALLS_______________________
______________________________________ 
						

							  Advanced Report Design CentreVu CMS Custom Reports
Selecting Rows from More Than One Table6-28
Notice that the Percent of Split Calls field merges data as 
discussed in our example. Also notice that Row Search ID #2, which is 
assigned to the Percent of Split Calls field, contains the row search 
statement as discussed in our example.
To illustrate the effect of join clauses, the report design in Figure 6-8
 
includes the Agent ACD Calls and Split ACD Calls fields. Notice 
that each field uses a database item that is also included in the Percent 
of Split Calls field.
When the report is run, the Percent of Split Calls field, for each 
agent in the selected split, divides that agents ACD calls by the total ACD 
calls for the split, then multiplies by 100 to give a percentage. Thus, as in 
Figure 6-9
, if agent 1000 had 23 ACD calls, and the split Sales-West had 412 
ACD calls, the Percent of Split Calls for agent 1000 would be 
5.583 (100 * [23/412]).
Figure 6-9:  Sample Report with Merged Data
 
04/19/95 00:30 AM CentreVu(TM) CMS Windows: 1 of 5 ^ 
Custom Reports: Hist: split status
Split: Sales-West Split ACD
Date: 06/08/93 Calls
Time: 8:00am
Percent
Agent ACD of Split
Agent Calls Calls
1000 23 5.583
1004 18 4.369
1005 18 4.369
1009 21 5.097
1010 24 5.825
1012 23 5.583 
						

							  Advanced Report Design CentreVu CMS Custom Reports
Alternative Row Search Conditions6-29
Alternative Row Search Conditions6
For your row search conditions, you will most often use the “where” clauses 
described in Chapter 4
. However, several additional “where” clause formats 
are available. This section describes those formats.
Selecting 
Rows Based 
on a Range of 
Val ue s
6
If you want a range of splits included in the report, you might use two “where” 
clauses as in the following example:
Select rows where: SPLIT >= 1 and SPLIT 
						

							  Advanced Report Design CentreVu CMS Custom Reports
Alternative Row Search Conditions6-30
Using 
Apostrophes 
for Some 
Database Item 
Val u es
6
Some database items require that, if you hardcode values in a “where” 
clause, you enclose the values in apostrophes (), as shown in the following 
example.
Select rows where: ROW_DATE > 07/01/93
Standard database items whose values you must enclose in apostrophes 
are:
VDN (the value is a VDN number)
EXTENSION (the value is an extension number)
LOGID (the value is an agent login ID)
EQLOC (the value is a 9-digit trunk location number)
ROW_DATE (the value is a date in mm/dd/yy format)
CWC (the value is a call work code of 1 to 16 digits)
Also, any custom database items you define as CHAR or DATE columns in 
INFORMIX are items whose hardcoded values you must enclose in 
apostrophes.
For historical reports only, an alternative format for these database items 
is:
Expression matches value
In this type of clause, matches is the same as =. However, this type of 
clause lets you use wildcard searches. That is, within the apostrophes, you 
can use an asterisk (*) or a question mark (?).
The asterisk (*) matches any and all characters, including blanks and no 
characters. Look at the following examples:
Select rows where: EQLOC matches 01*
This clause finds all rows where the EQLOC value begins with 01, which 
would mean all trunks for module 01. Thus, 010012020, 010211110, and 
011023100 are values that would match.
Select rows where: ROW_DATE matches */01*
This clause finds all rows where the ROW_DATE value has 01 as its dd (day 
of the month). That is, the clause searches for the first day of each month. 
Thus, 01/01/93, 04/01/93, and 10/01/92 are values that would match.
NoteActually, */01* would also find all dates, if they had passed, in the 
year 2001 (for example, 01/22/01, 08/03/01, and 11/31/01).