Lucent Technologies CentreVu Call Management System Custom Reports Manual

							  Advanced Report Design CentreVu CMS Custom Reports
Alternative Row Search Conditions6-31
The question mark (?) matches any single character. Look at the following 
examples:
Select rows where: EXTENSION matches 444?
This clause finds all rows where the EXTENSION value is four digits and has 
444 as the first three characters. Thus, 4441, 4440, and 4449 would match. 
However, 444 would not match.
Select rows where: LOGID matches ?000
This clause finds all rows where the LOGID value is four digits, begins with 
any number, and ends with “000.” Thus, 4000, 5000, and 9000 would match. 
000 would not match.
You can use not in a “matches” clause to exclude rows. For example, you 
may want to exclude a range of login IDs from your report with a clause like 
the following:
Select rows where: LOGID not matches 2*
This clause finds all login IDs except those starting with “2.” If you have 4-
digit login IDs, login IDs from 2000 to 2999 would be excluded.
Using
String-Value 
Database 
Items
6
String-value database items contain numerical data that the Dictionary 
subsystem translates to display current states or state changes. For 
example, a report field using the string-value database item WORKMODE 
displays AVAIL, ACD, ACW, and so on, depending on what state the 
particular agent is currently in. However, tables store states as numbers, not 
strings. When you run a report, 
CentreVu CMS simply substitutes the 
character strings for the numerical values. This process is identical to the 
substitution of names for split numbers, trunk group numbers, vectors, and 
so on.
If you want to use string-value database items in a “where” clause, then you 
must specify numerical values, not string values.
For example, if you want a current real-time agent report that lists data only 
for agents on extension-in or extension-out calls, then you can select the 
Current Interval Agent table and enter a statement as follows:
Select rows where: SPLIT = $splitvar and WORKMODE > 
10 and WORKMODE < 60 
						

							  Advanced Report Design CentreVu CMS Custom Reports
Alternative Row Search Conditions6-32
This statement finds rows for a user-specified split where the agent state is 
one of the following:
ACWIN (numerical value of 20)
ACWOUT (numerical value of 30)
AUXIN (numerical value of 40)
AUXOUT (numerical value of 50)
See the 
CentreVu CMS R3V8 Database Items and Calculations (585-210-
939) document for a complete list of row search values for string-value 
database items.
Other 
Available 
Formats for 
“Where” 
Clauses
6
For historical reports only, you can specify a list of hardcoded values using 
the following format:
Expression in (list of values)
As with a basic “where” clause, the Expression can be a database item or 
calculation. The values you list in the parentheses must be separated by 
commas. Also, you must use apostrophes for those values that normally 
require them. For example, the following “where” clause specifies three dates 
for the report:
Select rows where: ROW_DATE in (07/01/93, 
07/08/93,07/15/93)
The following “where” clause specifies three splits for the report:
Select rows where: SPLIT in (1,7,22)
For historical reports only, you can also exclude rows using a list of values 
by adding not to the clause. Using not can be extremely useful if you 
want to exclude, for example, lunch time intrahour intervals from your 
intrahour historical reports, as shown in the following example:
Select rows where: STARTTIME not in (1130, 1200, 
1230). 
						

							  Advanced Report Design CentreVu CMS Custom Reports
Repeating Aggregate Function Values6-33
Repeating Aggregate Function Values6
Normally, a field (or bar) that contains an aggregate function (sum, max, 
min, or avg) displays just one value. This is true no matter what your 
“Select rows where” criteria are. Therefore, you would normally select 
“discrete” as the field/bar type in the row search ID assigned to aggregate 
functions.
However, you may at times want 
CentreVu CMS to display multiple values 
for an aggregate function. In historical reports only, 
CentreVuCMS can 
display multiple values for an aggregate function. For 
CentreVu CMS to do 
this, the aggregate function must share a row search ID with one or more 
fields that do not have an aggregate function.
For example, you may want a report (Figure 6-11
) that shows the maximum 
(max) ACD calls in an interval for each of a variety of splits.
Figure 6-11:  Repeated Aggregate Function ReportDate: 07/01/93
Max ACD Calls-
Split Morning
1 652
2 491
3 297 
						

							  Advanced Report Design CentreVu CMS Custom Reports
Repeating Aggregate Function Values6-34
This report would have the following design.
Figure 6-12:  Sample Report Design for Repeated Aggregate Functions
According to the design, 
CentreVu CMS searches the Intrahour Split table 
(Figure 6-13
) for the splits the user ordered when running the report (Splits 1, 
2, and 3 in our example). 
CentreVu CMS also searches for date (07/01/93 in 
our example) and the maximum ACD calls (in boxes) in the selected range of 
intrahour intervals for each of the selected splits.
Field Window
Select: hsplit.SPLIT___
_______________
Screen Painter
Row Search Window
Table: hsplit
Select rows where: SPLIT = $i_split and ROW_DATE = $i_date Row search ID: 0
and STARTTIME = $i_time and ACD = $acd
Order by: _____________________ Sort type (Select one):
 Ascending
Field/bar type (Select one): < > Descending
< > Discrete
 Repeated vertically, spacing 1_
< > Repeated horizontally, spacing __
Field Window
Select: max(hsplit.ACDCALLS)
_______________ Date: $$$$$$$$
Max ACD Calls-
Split Morning
0xxxxxxxxxxxxxxxxxxx 0xxxxx 
						

							  Advanced Report Design CentreVu CMS Custom Reports
Repeating Aggregate Function Values6-35
Figure 6-13:  Sample Row Search of Grouped Aggregate Functions
CentreVu CMS would then display the values found in the report. However, 
notice in Figure 6-13
 that CentreVu CMS found four rows with the SPLIT 
value of 1, four rows with SPLIT value 2, and four rows with SPLIT value 3, 
but only displayed each value once in the report. This is because the 
aggregate function max(hsplit.ACDCALLS) field shares the same row 
search ID with the hsplit.SPLIT field.
0800
0800
0800
0900
0900
0900
1000
1000
1000
1100
1100
1100ABNTIME
. . .
. . .
. . .. . .
. . .
. . .. . .
. . .
. . .. . .
. . .
. . .. . .
. . .
. . .
.
.
.
.
.......
.
.
.
.......
.
.
.
.......
.
.
.
.......
.
.
.
.......
.
.
.
.......
.
.
.
.......
.
.
.
.......
.
.
.
.......
.
.
.
.......
.
.
.
.......
.
.
.
.......
.
.
.
.......
.
.
.
.......
.
.
.
..... ..
.
.
.
.......
.
.
.
.......
.
.
.
.......
.
.
.
.......
.
.
.
.......
.
.
.
.......
.
.
.
.......
.
.
.
.......
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
. .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
. .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
. .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
. .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
443
234
111
652
451
93
509
391
142
480
491
297
399
299
138
400
300
225
394
323
105
418
246
100
417
247
141
444
DATE
INTERVALSPLIT
ACDCALLS
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.48
37
20
59
32
11
43
31
10
39
22
15
36
20
13
46
33
12
40
34
14
41
30
18
34
24
14
43 ABANDONS
36898
20012
13111
53442
27635
15321
35401
19768
9786
33389
26789
12530
37651
29602
11523
36178
24303
15628
40002
29881
12115
34819
21173
10281
37856
26308
12567
39003
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
ACDTIME
988
777
400
1058
644
245
851
603
203
789
549
402
1452
7616
2569
1745
1109
367
1322
1188
704
1256
980
589
1340
1299
688
1001
.
.
.
.
.......
.
.
.
.......
.
.
.
.......
.
.
.
.......
.
.
.
.......
.
.
.
.......
.
.
.
.......
.
.
.
.......
.
.
.
.......
.
.
.
.......
.
.
.
.......
.
.
.
.......
.
.
.
.......
.
.
.
.......
.
.
.
..... ..
.
.
.
.......
.
.
.
.......
.
.
.
.......
.
.
.
.......
.
.
.
.......
.
.
.
.......
.
.
.
.......
.
.
.
...... .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
1
2
3
1
1
2
3
1
2
3
1
2
3
1
2
3
1
2
3
1
2
3
1
2
3
1
2
3
070193
070193
070193
070193
070193
070193
070193
070193
070193
070193
070193
070193
070393 070393 070393 070393 070293
070293
070293
070293
070293
070293
070293
070293
070293
070293
070293
070293
0800
0800
0800
0900
0800
0800
0800
0900
0900
0900
1000
1000
1000
1100
1100
1100 
						

							  Advanced Report Design CentreVu CMS Custom Reports
Repeating Aggregate Function Values6-36
If the hsplit.SPLIT field and the max(hsplit.ACDCALLS) fields were 
assigned different row search IDs (with each ID having identical criteria), the 
report for Splits 1, 2, and 3 would show data as shown in Figure 6-14
:
Figure 6-14:  Sample Display of Group Values
The display of a value only once to represent multiple occurrences of the 
same value is called grouping and can be done only when sum, max, min, 
or avg values are listed based on the unique values found for other fields. 
The rules for grouping data are:
lIf you assign a row search ID to one or more aggregate function fields, 
you may assign the same row search ID to a maximum of eight 
nonaggregate fields.
lAssigning the same row search ID to both aggregate functions and 
nonaggregate fields makes sense only if the nonaggregate fields 
contain identifier data (split numbers, login IDs, dates, interval start 
times, vector numbers, and so on).
lIf you assign the same row search ID to both aggregate functions and a 
nonaggregate field, the report displays a single value for each unique 
value found for the nonaggregate field (that also matches the row 
search criteria). If multiple rows contain the same value, then 
CentreVu 
CMS still lists the value once. For this reason, only identifier fields 
should have the same row search ID as aggregate functions.
lIf you assign the same row search ID to aggregate functions and more 
than one nonaggregate field, then the report displays a single row of 
data for each unique combination of values for the nonaggregate 
fields.Date: 07/01/93
Max ACD Calls-
Split Morning
1652
1
1
1
2
2
2
2
3
3
3
3 
						

							  Database Items and Calculations CentreVu CMS Custom Reports
General InformationA-1
A Database Items and Calculations
General Information1
Reference1Please refer to the CentreVu® CMS R3V8 Database Items and Calculations (585-210-
939) document, for full descriptions of the database items and calculations 
avaialable through the CentreVu Call Management System (CMS). 
						

							  Database Items and Calculations CentreVu CMS Custom Reports
General InformationA-2 
						

							  Glossary CentreVu CMS Custom Reports
GL-1
Glossary
Access permissionsPermissions assigned to a CMS user so that user can access different 
subsystems in CMS or administer specific elements (splits/skills, trunks, 
vectors, etc.) of the ACD. Access permissions are specified as read or write 
permission. Read permission means the CMS user can access and view 
data (for example, run reports or view the Dictionary subsystem). Write 
permission means the CMS user can add, modify, or delete data and execute 
processes.
Administrator, CMSA CMS user who has permissions for most or all parts of CMS. In Custom 
Reports, a CMS Administrator can access the designs of all custom reports, 
even those of which the administrator is not the owner.
Aggregate functionA prefix (avg, max, min, or sum) attached to a database item, calculation, 
parts of a calculation, or a calculation name. An aggregate function normally 
displays a single value that is determined from a group of selected values.
AscendingListed with the lowest values first and the highest values last. With time and 
dates, the oldest values are listed first.
Associated ACDThe ACD associated (or assigned to) an input field, as defined in the Define 
Input window. When an input field is associated with an ACD, the values the 
user enters into the field will apply to that ACD only. For example, if an input 
field requiring a split number also has associated ACD #1, the selected split 
number will be a split in ACD #1.
BarA representation of data in the form of a bar that gets longer or shorter as 
values go up or down. A horizontal bar grows and shrinks horizontally. A 
vertical bar grows and shrinks vertically.
BlockA rectangular area on the Screen Painter that you define and use to quickly 
rearrange report fields, bars, and text.
Block editingDefining and using a block to copy, move, or delete fields, bars, and text in a 
report design.
CalculationA calculation is also a formula consisting of database items, numbers, and 
arithmetic operators (+, -, /, *, and ( )). A calculation can also include 
constants.
Calculation nameA name for a calculation, either a standard CMS calculation name or a name 
that you define in the Dictionary subsystem. A calculation name can be 
useful if you need to use the same calculation for field/bar definitions in 
multiple custom reports. Then, if you need to change the calculation, you can 
change it once in the Dictionary, and all custom reports that use the 
calculation name will reflect the change. 
						

							  Glossary CentreVu CMS Custom Reports
GL-2
Call-based itemsThe category of database items in CMS that are committed to the database 
after the call completes. If a call starts and ends in different intrahour 
intervals, all of the call-based data is recorded in the interval in which the call 
completed. Most database items are call-based.
Call Management 
System (CMS)A software product used by business customers that have Lucent 
Technologies telecommunications switches/ECS and receive a large volume 
of telephone calls that are processed through the Automatic Call Distribution 
(ACD) feature of the switch/ECS. The CMS collects call-traffic data, formats 
management reports, and provides an administrative interface to the ACD 
feature in the switch/ECS.
CMSQLA tool that allows direct queries of the historical database. This tool is the 
interactive interface typically used to view the 
INFORMIX database. For CMS 
purposes, CMSQL is used instead of 
INFORMIX SQL.
ColumnA column is a part of a table that stores a particular type of data (a number of 
events of a certain type, a length of time spent on a certain type of event, the 
time an event happened, a numerical identifier of an ACD entity, or the 
current status of an ACD entity). Database items are the names of columns. 
Examples are ACDCALLS, ACDTIME, SPLIT, STARTTIME, WORKMODE, 
and so on.
ConstantA name you assign in Dictionary to a fixed numerical value. A constant can 
be useful if you need to use the same numerical value for field/bar definitions 
in multiple custom reports. Then if you need to change the value, you can 
change it once in Dictionary, and all custom reports that use the constant will 
reflect the change.
Count(*)An expression you can enter in the Select field in the Field or Bar window. 
count(*) tells CMS to count the number of rows in a table that match 
certain row search conditions (as defined for the assigned Row Search ID) 
and display the total in the field/bar.
Custom database 
itemA database item (column name) which you have included in a custom 
INFORMIX table and which you have identified to CMS in Dictionary.
Custom reportA report that you create and design using the Custom Reports subsystem.
Data expressionAn expression you enter in the Select field of the Field or Bar window to 
define the data and manipulation of that data for display in a field or bar.
DatabaseA group of files that store ACD data according to a specific time frame: 
current and previous intrahour real-time data and intrahour, daily, weekly, 
and monthly historical data.