Lucent Technologies CentreVu Call Management System Custom Reports Manual

							  Defining the Data for a Custom Report CentreVu CMS Custom Reports
Defining Report Fields4-13
Step 1: Define 
the Position 
and Length of 
a Field
4
Figure 4-5:  The Field Window 1a.On the Screen Painter, position 
the cursor where you want a field 
to begin, and select Field from 
the action list.®
The cursor returns to its 
original position, and the 
following message appears
: 
Move cursor to define 
opposite corner of 
field and press 
RETURN.
1b.Move the cursor using the arrow 
keys to define a field length, and 
press  . Be sure the field is 
long enough to contain the data. 
If the field is too short for a value, 
the report shows asterisks (*) in 
the field or, if the data is a word, 
cuts letters off.®The field appears as a 
question mark (?) followed by 
xs. The question mark 
indicates that you have not yet 
assigned a Row Search ID to 
the field. The Field window 
also appears.
Return 
						

							  Defining the Data for a Custom Report CentreVu CMS Custom Reports
Defining Report Fields4-14
Step 2: Define 
the Fields 
Data 
Expression
4
In the Select field (shown in Figure 4-5), enter a data expression to tell 
CentreVu CMS two things:
lWhat table column(s) should supply data to the field.
lHow to manipulate that data.
CentreVu CMS picks out values from a table with both row and column 
identifiers. 
CentreVu CMS identifies rows of data according to the users 
inputs and the row search conditions you define (see the “Defining the Rows 
of Data for a Report” section in this chapter). CentreVu CMS identifies 
columns according to the data expression you define here.
You can enter the following types of data expressions:
Database Items4A database item is the name of a column of data in a table, either standard or 
custom. When you enter a database item, you must always add the name of 
a table and a period (.) as a prefix. The format is as follows:
.
Examples:
dsplit.ACDCALLS
hagent.STARTTIME
ctkgrp.NUMINUSE
Standard database items
Standard database items are listed in the Dictionary subsystem as having all 
upper-case letters (as in the preceding examples).
A standard database item can store:
lIdentifiers (for example, SPLIT, VDN, LOGID, and so on)
lTimed data (for example, ACDTIME, ABANTIME, AUXOUTIME, and so 
on)
lEvent counts (for example, ACDCALLS, INTERFLOWCALLS, ABNCALLS, 
and so on)
lFor real-time and agent trace reports, current state data (for example, 
WORKMODE, DURATION, NUMINUSE, and so on). 
						

							  Defining the Data for a Custom Report CentreVu CMS Custom Reports
Defining Report Fields4-15
See Appendix A
 for a description of database tables and items including the 
exceptions, forecast, and login/logout tables. See Chapter 6, Advanced 
Report Design, for more information about custom reports that include 
exceptions and forecast data.
Custom database items
You must enter a custom database item, with the custom table name as a 
prefix, exactly as you defined it in the Dictionary subsystem. The data 
identified by a custom database item depends entirely on the data you 
entered for the item in the custom table (see Chapter 6, Advanced Report 
Design).
Constants4A constant is the name of a fixed numerical value (whole number or decimal) 
that you define in the Dictionary subsystem. Constant names can be up to 20 
characters long. A constant could represent a per-minute usage rate for 
trunks, a daily or hourly wage rate, or a service objective (like number of 
abandons, number of ACD calls, or percent within service level). A constant 
could also represent an average for the estimated dollar loss of an 
abandoned call, which could then be used to calculate daily loss of revenue 
due to abandoned calls. No standard constants exist in 
CentreVu CMS when 
it is first installed. Therefore, you must define every constant you want to use.
Using constants makes sense only if you have a fixed value that you want to 
use under one or both of the following conditions:
lThe constant is a value that you will use in a number of different custom 
reports (for example, an average wage rate).
lYou would not be able to remember the numerical value, but could 
remember a name assigned to the value (for example, for the $9.00 
hourly wage rate for an agent called Smith, you could have a constant 
called Smithwage.)
NoteStandard database items are often shared by more than one table. 
For example, ABNCALLS can identify a column in the Current 
Interval Split, Daily Split, or Intrahour Agent tables (or many other 
tables). 
CentreVu CMS can determine the exact database item only when 
it is identified with a table.
NoteThe CentreVu CMS real-time database only allows whole 
numbers in queries. If you need a value to be a decimal (for 
example, 9.5), use whole numbers and division to arrive at the 
correct number (so, in order to have 9.5 in a query, you would use 
19/2 as the query entry). 
						

							  Defining the Data for a Custom Report CentreVu CMS Custom Reports
Defining Report Fields4-16
Calculations4A calculation is a combination of database items and arithmetic operators. 
You can also include constants in a calculation. The arithmetic operators are:
Some examples of calculations are:
dsplit.ACDCALLS/dsplit.ACDTIME
hagent.AUXOUTTIME+hagent.ACWOUTTIME
100*((cagent.I_ACDTIME+cagent.I_ACWTIME)/
cagent.I_STAFFTIME)
Arithmetic operations are generally performed in order from left to right. 
However, multiplication and division operations are performed before 
addition and subtraction operations, unless the addition or subtraction 
operations are enclosed in parentheses. Operations in parentheses are 
always performed first. If more than one set of parentheses is used, the 
operation in the set farthest to the left is performed first. If one set of 
parentheses is inside of another set, the operation of the inner set is 
performed first.
Calculation 
names
4
A calculation name is a name, as defined in the Dictionary subsystem, that 
can substitute for the actual calculation. The calculation name can be a 
standard name (used in standard reports) or a name you define. You cannot 
append a table name to a calculation name. Therefore, you must specify a 
table name in the Table for calculations field.
A calculation name normally reflects the purpose of the calculation. As a 
result, entering a name is an easier, more meaningful way to define data for a 
report field. More importantly, if you use a calculation name in many custom 
reports and later decide to change the calculation, you can simply make your 
changes once in the Dictionary subsystem. 
CentreVu CMS then applies 
those changes to every report that uses the calculation name.
For example, if you use the standard calculation name 
, which represents the calculation +add
- subtract
* multiply
/divide
() perform first
NoteCentreVu CMS differentiates between uppercase and lowercase 
letters in calculation names. Therefore, be sure you enter the desired 
calculation name 
exactly as it appears in the Dictionary subsystem. 
						

							  Defining the Data for a Custom Report CentreVu CMS Custom Reports
Defining Report Fields4-17
100*((I_ACDTIME+I_ACWTIME)/I_STAFFTIME), but do not want to include 
the ACWTIME in the calculation, then you can change the calculation in the 
Dictionary subsystem so that  represents 
100*(I_ACDTIME/I_STAFFTIME). Then, any report that uses the calculation 
name PERCENT_ACD_TIME reflects the new calculation.
At times, using calculation names helps save space in the Select field so 
you can create more complex calculations. For example, if you wanted to 
know the average time agents spent on all extension calls (both incoming 
and outgoing), you might have to add the following calculation:
(csplit.ACWINTIME + csplit.AUXINTIME + csplit. 
ACWOUTTIME + csplit.AUXOUTTIME) / (csplit.ACWINCALLS + 
csplit.AUXINCALLS + csplit.ACWOUTCALLS + 
csplit.AUXOUTCALLS)
Unfortunately, the Select field is not long enough to enter the complete 
calculation. However, you could define in the Dictionary subsystem two 
separate calculation names for each half of the calculation.
For:
(csplit.ACWINTIME + csplit.AUXINTIME + csplit.ACWOUTTIME 
+ csplit.AUXOUTTIME),
you could enter, in the Dictionary subsystem, the calculation name:
TIME_ON_NON-ACD_SUM with the assigned calculation 
(ACWINTIME + AUXINTIME + ACWOUTTIME + AUXOUTTIME).
Likewise, for:
csplit.ACWINCALLS + csplit.AUXINCALLS + 
csplit.ACWOUTCALLS + csplit.AUXOUTCALLS),
you could enter the calculation name:
NON-ACD_CALLS_SUM with the assigned calculation 
(ACWINCALLS + AUXINCALLS + ACWOUTCALLS + AUXOUTCALLS).
As a result, you can enter the following calculation in the Select field:
TIME_ON_NON-ACD_SUM/NON-ACD_CALLS_SUM
CautionIf you change the calculation for a standard calculation name, 
the change affects any standard report, as well as any custom 
report, that uses that calculation name. 
						

							  Defining the Data for a Custom Report CentreVu CMS Custom Reports
Defining Report Fields4-18
Then, you can enter the table name csplit in the Table for 
calculation field.
Aggregate 
Functions
4
An aggregate function is a prefix attached to a database item, a calculation, 
parts of a calculation, or a calculation name. When you define an aggregate 
function, you must place the database item or calculation in parentheses [as 
in, max(dsplit.ACDTIME/dsplit.ACDCALLS)].
An aggregate function can be one of four types. Each type retrieves a 
different value from the data.
lmax 
The max aggregate function retrieves the highest value for a calculation 
or database item over the time frame of the report.
For example, if the Intrahour Split table contained data as shown in 
Figure 4-6
 and you entered max(hsplit.ACDCALLS) for a field in a 
report, then ran the report for Split 1 for all intervals on 07/02/93, 
CentreVu CMS would find all rows shown in bold. However, CentreVu 
CMS would display only the value 418 (shown in the box), which is the 
maximum ACD calls in any single interval on 07/02/93. Likewise, if you 
entered max(ACDTIME/ACDCALLS) for the field, 
CentreVu CMS 
would display the value 101.53 (which is the highest average talk time in 
any single interval on 07/02/93).
CautionYou should not add table names to your custom calculations in the 
Dictionary subsystem. Doing so makes the assigned calculation 
name less flexible for use in custom reports. Also, if you append 
table names to the Dictionary calculation and then also assign a 
table name to the calculation name in the Field window, the report 
will fail.
NoteIn real-time reports, fields with aggregate functions cannot share a 
set of row search conditions with nonaggregate fields. And for 
historical reports, special considerations exist when you assign the 
same row search conditions to both aggregate functions and other 
types of data expressions. See “Repeating Aggregate Function 
Values” in Chapter 6 of this manual. 
						

							  Defining the Data for a Custom Report CentreVu CMS Custom Reports
Defining Report Fields4-19
Figure 4-6:  Sample Intrahour Split Table Data
lmin
The min aggregate function retrieves the lowest value for a calculation 
or database item over the time frame of the report.
For example, using the previous example, if you entered 
min(hsplit.ACDCALLS) instead of max(hsplit.ACDCALLS) for 
the field and then ran the report for Split 1 for all intervals on 07/02/93, 
CentreVu CMS would display only the value 394 which is the smallest 
number of ACD calls in any single interval on 07/02/93.
lsum
The sum aggregate function retrieves the sum of all values for a 
calculation or database item over the time frame of the report.
For example, if the Intrahour Split table contained data as shown in 
Figure 4-6
 and you enter sum(hsplit.ACDCALLS) for a field in a 
report, then for Split 1 and all intervals on 07/02/93, 
CentreVuCMS 
would take the values for hsplit.ACDCALLS and add them up to 
display only the value 1611. Likewise, if you entered 
sum(hsplit.ABANDONED+hsplit.ACDCALLS) for the field, 
CentreVu CMS would display only the value 1774, which is the total of 
all ACD calls and abandons for Split 1 on 07/02/93.
DATE
STARTTIMESPLIT
ACDCALLSABANDONSACDTIMEABNTIME
34 18 30
4114 34
4012 33
4613
20
3615
22 39 10 31
24
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
..
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
..
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
..
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
..
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
. .... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
. .... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
.
.... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
. .... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
.
. . .. . .. . .
. . .. . .. . .
. . .. . .. . .. . .
. . . 391
142
480
491
297
399
299
138
400
300
225
394
323
105
418
246
100
417
247 2
3
1
2
3
1
2
3
1
2
3
1
2
3
1
2
3
1
2 1000
1000
1100
1100
1100
0800
0800
0800
0900
0900
0900
1000
1000
1000
1100
1100
1100
0800
0800 070193
070193
070193
070193
070193
070293
070293
070293
070293
070293
070293
070293
070293
070293
070293
070293
070293
070393
070393
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
1299 1340589 980
1256704 1188
1322367 1109
17452569
7616
1452402 549
789 203 603
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
26308 37856 10281 21173
3481912115
40002
29881 15628 24303
3617811523
29602
3765112530 26789
333899786 19768
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
. 
						

							  Defining the Data for a Custom Report CentreVu CMS Custom Reports
Defining Report Fields4-20
lavg
The avg aggregate function retrieves the average of all values found 
over the time frame of the report. Using the sample table in Figure 4-6
, if 
you enter
 avg(ACDCALLS) for a field and run the report for Split 1 for all 
intervals on 07/02/95, the value 402.75, which is the average of 399, 
400, 394, and 418, is displayed.
count(*)4The count(*) expression tells CentreVu CMS to count the number of 
rows in a table that match certain row search conditions (as defined in the 
Row Search window). For example, if you want the number of agents in a 
split that had more than five extension-out calls, the Row Search window 
would have a row search statement like:
Select rows where: SPLIT = 1 and 
(ACWOUTCALLS+AUXOUTCALLS) > 5
This statement means, “Find rows of data where the SPLIT value is 1 and 
total extension-out calls, for both ACW and AUX states, is greater than 5. 
Then, by entering count(*) in the Select: field, the report field would 
count the number of rows that match, and display the number in your report.
Do not append a table name to the beginning of count(*).
count(*) is always a number of matching rows and makes sense only if you 
want to track some specific measure of performance by ACD elements (for 
example, the number of agents currently logged into a split, the number of 
trunks that were occupied for more than 80% of the time, the number of 
VDNs that had over 30 abandoned calls in an intrahour interval).
count(*) allows you to create fields that act as exception counts.
Data from More 
Than One Table
4
A calculation can merge data from more than one table in a report field.
For example, you may want the percentage of a splits ACD calls that an 
agent handled in a day. Thus, you can enter a calculation that merges data 
from the Daily Agent and Daily Split tables, as in the following example.
dagent.ACDCALLS/dsplit.ACDCALLS
When you merge data from two tables, you must define your row search 
conditions in a special way. See “Selecting Rows from More Than One Table
” 
in Chapter 6
 of this manual.
NoteYou cannot use calculation names for a field in which you merge data 
from two tables, and you cannot merge data in real-time reports. 
						

							  Defining the Data for a Custom Report CentreVu CMS Custom Reports
Defining Report Fields4-21
Step 3: Define 
the Table(s) for 
Calculation 
Names
4
Enter a table name in the Table for calculations field only if you 
entered a calculation name in the Select field. The table name tells 
CentreVu CMS in what table to look for the database items in the calculation.
For example, look at the following entries:
Select: AVG_POS_STAFF
Table for calculation: hsplit
These entries mean “Take the calculation defined in the Dictionary 
subsystem for , which is 
I_STAFFTIME/(INTERVAL*60), and apply the hsplit table name to the 
database items.” In effect, the two fields make the calculation 
hsplit.I_STAFFTIME/(hsplit.INTERVAL*60).
Step 4: Justify 
Data in the 
Field
4
Select, from the Justification list, the way you want CentreVu CMS to 
line up data when the data appear in the field. Normally, numerical data is 
right-justified so that the right hand side lines up in a column. Names are 
normally left-justified so that the first character of each name is lined up. 
However, you may choose any of the three options for any type of data.
NoteThe Table for calculations field is necessary because you 
cannot append a table name to a calculation name in the Select 
field. 
						

							  Defining the Data for a Custom Report CentreVu CMS Custom Reports
Defining Report Fields4-22
Step 5: Define 
the Field 
Format
4
Enter an x to select an item in the Field Format list. You must also 
complete the field associated with the list item.
The format type and the format you specify in the associated field tell 
CentreVu CMS how to display the values it finds for the field. However, the 
format you select depends on the type of data 
CentreVu CMS will display. 
The format options are as follows:
NumberSelect Number if the field will display a number of events, 
an average, or a percentage. You must also specify a 
number of decimal places for the field. Enter 0 if you do not 
need decimal places displayed. If the fields expression were 
ACDCALLS, you would select Number and enter 0 in the 
field. However, if the field expression were to generate an 
average, such as the average staffed positions per interval, 
I_STAFFTIME/(INTERVAL*60), you may want to include 
decimal places.
When you run the report, the decimal point and the decimal 
places will use up spaces in the field. For example, if the field 
contains six spaces and you specify three decimal places for 
the field, then data will appear with two characters to the left 
and three characters to the right of the decimal point (for 
example, 12.344).
DateSelect Date if the field expression is ROW_DATE. You must 
also specify a date format, with appropriate punctuation. You 
can select a single format or a combination of formats. The 
available formats are:
Table 4-2:  Date Formats for Report Fields
mm= Numerical month (for example, 12 for 
December)
MMM= Month represented by three letters (for example, 
APR for April).
yy= Year as two digits (for example, 94).
YYYY= Year as four digits (for example, 1994).
dd= Numerical day of the month (for example, 31).
jjj= Day of the year in the Julian calendar (for 
example, 151 for May 31).
www= Day of the week as three letters (for example, 
THU).