Lucent Technologies Centrevu Report Designer Version 8 User Guide

							  SQL Query and CMS Database Table Basics CentreVu Report Designer Version 8 User Guide
CMS Database Table Basics18-9
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).
See the 
CentreVu CMS R3V8 Database Items and Calculations (585-
210-939) document for a description of database tables and items 
including the exceptions, forecast, and login/logout tables. 
Standard 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. The same data item may exist in several CMS 
database tables, with the same data item name used. If a data item exists 
in a real-time CMS table as well as an integrated CMS table, its value will 
usually be different depending on which table is queried. If data items in 
reports created with Report Designer are returning unexpected values, 
particularly in integrated reports, check that the correct table is being 
queried.
Custom Database 
Items
18
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.
When you create a custom database table, as described in this chapter, 
keep in mind that all custom database table names need to begin with the 
“c_” prefix. If you do not name the tables with the c_, they will not 
automatically be backed up and they will not show up in the Query 
Assistant.
Constants18A 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. 
						

							  SQL Query and CMS Database Table Basics CentreVu Report Designer Version 8 User Guide
CMS Database Table Basics18-10
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
.)
The 
CentreVu CMS real-time database allows only 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).
Calculations18A calculation is a combination of database items and arithmetic 
operators. You can also include constants in a calculation. The arithmetic 
operators are as follows:
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.+add
- subtract
* multiply
/divide
( ) perform first 
						

							  SQL Query and CMS Database Table Basics CentreVu Report Designer Version 8 User Guide
CMS Database Table Basics18-11
Calculation names18A 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 there are several different custom split reports, and in 
each report, a field uses the standard calculation name 
.  represents the 
calculation 100*((I_ACDTIME+I_ACWTIME)/I_STAFFTIME). This 
calculation, when assigned to the Intrahour Split table, finds the 
percentage of time a splits agents spent on ACD calls while logged in. 
The calculation includes talk time (I_ACDTIME
) and after-call-work time 
(I_ACWTIME
). If you no longer wish to include after-call-work time in the 
calculation, then you can change the calculation in the Dictionary 
subsystem such that  represents 
100*(I_ACDTIME/I_STAFFTIME). Any custom report that uses the 
calculation name PERCENT_ACD_TIME reflects the new calculation.
If 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.
You 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. 
						

							  SQL Query and CMS Database Table Basics CentreVu Report Designer Version 8 User Guide
CMS Database Table Basics18-12
Data from More 
Than One Table
18
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.
You cannot use calculation names for a field in which you merge data 
from two tables. 
						

							  SQL Query and CMS Database Table Basics CentreVu Report Designer Version 8 User Guide
Creating a Custom Data Table18-13
Creating a Custom Data Table18
The section of the CentreVuä Call Management System (CentreVu 
CMS) database that stores historical ACD data uses the 
INFORMIX *-
SQL Relational Database Management System. All historical ACD data 
available for use in custom reports is stored in tables in the 
CentreVu 
CMS database. You can access INFORMIX and the CentreVu CMS 
database to build your own data tables to contain financial information, 
schedule information, product or service information, or any other type of 
information you want. You can then design historical custom reports to 
display the data, with or without ACD data.
Instructions on the use of 
INFORMIX SQL appear in this document as a 
convenience. These instructions are not intended as a substitute for the 
INFORMIX documentation. Except where noted, the standard rules of 
INFORMIX SQL apply, as documented in the INFORMIX-SQL Relational 
Database Management System User Guide
 for INFORMIX SQL Version 
4.10. This document is delivered with your 
CentreVu CMS software and 
documents.
CentreVu CMS does not automatically check the database for disk space 
used by data in custom tables. As a result, you can inadvertently fill up 
your disk with custom data. When this happens, you can lose or damage 
custom data and ACD data. Therefore, if you create custom data tables, 
be careful to check the amount of disk space available regularly. See the 
CentreVu® CMS R3V8 Administration (585-210-910) document for more 
information on disk storage.
If you back up data using the Maintenance — Backup Data window, you 
will save the data stored in custom 
INFORMIX tables, but you will not 
save the custom table definitions (table names, column names, data 
types, and so on) unless the custom tables were named with the “c_” 
prefix. As a result, if you lose the custom table definitions because of a 
disk crash, power hit, or some other reason, you cannot restore these 
table definitions through the Backup Data window nor can you restore 
custom data saved through the Backup Data window.
Therefore, you should periodically back up data using the 
UNIX† system. 
Then, if you lose 
INFORMIX table definitions and/or custom data, you 
can restore the table definitions through 
UNIX, and then, if necessary, 
restore the custom data. See the 
CentreVu® CMS R3V8 Administration 
(
585-210-810) document for procedures on backing up data by way of 
the 
UNIX system.
* INFORMIX is a registered trademark of Informix Software, Inc.
† UNIX is a registered trademark of Novell, Inc.  
						

							  SQL Query and CMS Database Table Basics CentreVu Report Designer Version 8 User Guide
Creating a Custom Data Table18-14
To define a custom database table, complete the steps defined in the 
following sections.
Step 1: Access 
the 
CentreVu 
CMS Database in 
INFORMIX18
To perform these procedures, you must have purchased the INFORMIX 
SQL software.
To access the 
INFORMIX SQL software, you need to log into the CMS 
using 
CentreVu Terminal.
To access the 
CentreVu CMS database in INFORMIX, use the following 
steps:The 
CentreVu CMS supports only the following INFORMIX 
data types:
• SQLSMFLOAT
• SQLFLOAT
•SQLMONEY
• SQLDECIMAL
•SQLSMINT
•SQLINT
•SQLSERIAL
•SQLDATE
• SQLCHAR
• SQLNULL.
1a.Press the   SLK.®The Commands submenu 
appears.
1b.Select the UNIX 
option.®All windows and menus 
disappear, and the UNIX 
prompt appears.
1c.At the $ 
prompt, type the 
following command: 
DBPATH=/cms/db/inf.
 
Press the   key. Type 
export DBPATH. 
Press 
the   key.®The UNIX prompt 
reappears.
1d.At the $ 
prompt, type the 
following pathname: 
/usr/informix/bin/is
ql. 
Press the   key.®The INFORMIX logo 
appears, followed by the 
INFORMIX Main Menu.
Commands
Return
Return
Return 
						

							  SQL Query and CMS Database Table Basics CentreVu Report Designer Version 8 User Guide
Creating a Custom Data Table18-15
Step 2: Build the 
Ta b l e
18
In INFORMIX, you can select menu items in one of two ways:
lUse the arrow keys (® or ¬) to move the cursor to the menu option. 
Then, press  .
lType the first character of the menu option
To escape from a step and go back to the previous step, press the Delete 
key on your keyboard.
2a.At the 
INFORMIX Main 
Menu, select Table
.®The Select Database 
screen appears.
2b.Ty p e cms
, and press 
.®The Table
 menu appears.
2c.Select Create
.®The Create Table
 screen 
appears.
2d.Type a name of up to 18 
characters for the table you 
are building. The table name 
must begin with c_ (a lower-
case “c” and underscore). 
For example, type 
c_workcode
. Press the 
 key.®The Create Table
 menu 
appears with a highlighted 
box beneath “Column 
Name.”
2e.Select Add
.®The Add Name
 screen 
appears.
Return
Return
Return 
						

							  SQL Query and CMS Database Table Basics CentreVu Report Designer Version 8 User Guide
Creating a Custom Data Table18-16
The available data types are as follows:2f.Type a name of up to 18 
characters for the column 
you are adding. If desired, 
you can use names of 
standard 
CentreVuCMS 
database items only if you 
enter the name in lowercase 
letters in the Dictionary 
subsystem. The Dictionary: 
Custom Items window 
accepts only lowercase 
letters for custom item 
names that are the same as 
standard database items. 
Press the   key.®The Add Type 
screen 
appears.
CharCharacter (CHAR) columns store any combination of 
letters, numbers, and symbols.
NumberNumeric columns store numbers. The numbers 
stored can be one of the following five types:
•Decimal. A decimal is a number that contains a deci-
mal point.
•Smallint. A Smallint column stores integers from -
32,767 to +32,767.
•Integer. An Integer column stores integers from -
2,147,483,647 to +2,147,483,647.
•Smallfloat. A Smallfloat column stores floating point 
numbers with up to seven significant digits.
•Float. A Float column stores floating point numbers 
with up to 14 significant digits.
SerialSerial columns store a unique sequence number in 
each row of the table.
DateDate columns store calendar dates with the format 
mm/dd/yy or dd/mm/yy, depending on your 
Windows operating system.
date-TimeThe date-Time data type is not supported by CMS.
You must type T in order to select date-Time. 
A date-Time column allows you to specify a 
database qualifier of year, month, day, hour, or 
minute.
Return 
						

							  SQL Query and CMS Database Table Basics CentreVu Report Designer Version 8 User Guide
Creating a Custom Data Table18-17
Interval
The Interval data type is not supported by CMS. 
An interval column allows you to specify an 
interval 
qualifier of year, month, day, hour, or minute.
MoneyThe Money data type is not supported by CMS.
If you data will be dollars and cents, you should use 
Decimal.
2g.Select the type of data that 
the field will store.®The additional prompts that 
appear differ depending on 
your selected data type.
2h.Complete the definition of 
the column by responding to 
the additional prompts that 
appear. The prompts that 
appear and the order in 
which they appear in 
depend on the data type you 
selected The following table 
lists the possible prompts.®When you have responded 
to all of the prompts for the 
column, a new highlighted 
line appears in the table, 
and the
 Add Name field 
reappears at the top of the 
screen.
2i.Repeat Steps 2f through 2h 
for each column that you 
want to add.
Prompt What to Do
PRECISIONSelect either Smallfloat 
or Float
. 
PRECISION 
appears for the Float 
type.
INDEXEnter y if you want to make the column an index. 
INDEX 
appears for all types except Serial
. A 
column with the Serial 
data type automatically 
becomes an index.
Make the column an index only if the column will 
be used for row searches and the table will contain 
more than 200 rows of data. 
						

							  SQL Query and CMS Database Table Basics CentreVu Report Designer Version 8 User Guide
Creating a Custom Data Table18-18
DUPLICATES
Enter y 
if you want to allow the column to contain 
the same value in different rows. For example, if 
the column were to contain the last names of 
people, you would probably want to allow multiple 
entries (because, for example, you might have 
many different people with the last name of 
Smith).
However, if you were creating a column of social 
security numbers, then you would enter n 
to 
prevent multiple entries of the same number. 
DUPLICATES 
appears for all types except 
Serial
.
NULLSEnter y 
if you want to allow the column to have 
rows with no values (versus requiring values). For 
example, if your table is a list of customer data and 
the column you are adding is for the customer’s 
employer, you might want to allow the NULL 
value for the case where the person is 
unemployed. 
Enter n 
if you want to require a value in each row. 
NULLS 
appears for all types except Serial.
ADD STARTING 
NUMBEREnter the number that 
INFORMIX should use as a 
starting point for numbering rows. 
INFORMIX 
identifies the first row in the table with the number 
you enter. As each new row of data is added to the 
table, 
INFORMIX assigns that row the next 
number in the sequence. ADD STARTING 
NUMBER 
appears only for Serial
.
NUMERICType the first letter of the numeric you want: 
Integer, Smallint, Decimal, 
or Float
.
LENGTHEnter the number of digits the column will store for 
a single piece of data. LENGTH 
appears for 
Char 
and Decimal 
types
SCALEEnter the number of digits that should appear to 
the right of the decimal point. The decimal digits, 
but not the decimal point, occupy part of the field 
length you specified in the LENGTH 
field. SCALE 
appears after the LENGTH 
for Decimal
. Prompt What to Do