Lucent Technologies CentreVu Call Management System Custom Reports Manual

							  Advanced Report Design CentreVu CMS Custom Reports
Creating a Custom Data Table6-1
6 Advanced Report Design
Creating a Custom Data Table6
The section of the CentreVuä Call Management System (CentreVu CMS) 
database that stores historical ACD data uses the 
INFORMIX *SQL (ISQL) 
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.
* INFORMIX is a registered trademark of Informix Software, Inc.
NoteInstructions 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. If you purchased ISQL with the CentreVu CMS, this 
document is delivered with your 
CentreVu CMS software and 
documents.
CautionCentreVu 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 “System Setup” chapter in 
the 
CentreVuä CMS R3V5 Administration (585-215-820) 
document for more information on disk storage. 
						

							  Advanced Report Design CentreVu CMS Custom Reports
Creating a Custom Data Table6-2
To define custom data tables, use the steps described in the following pages.
CautionIf you back up data using the Maintenance — Backup Data window, 
the data stored in custom 
INFORMIX tables will be saved, but the 
custom table definitions (table names, column names, data 
types, and so on) will not be saved. 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 using 
the Backup Data window. Because the custom table definitions will 
be lost, you will not be able to restore the custom data that was 
saved using the Backup Data window.
For this reason, it is recommended that you make your own 
backups of the custom table definitions using the 
UNIXa 
system/Solarisb system. If you lose INFORMIX table definitions 
and/or custom data, you can restore the table definitions using 
UNIX and then restore the custom data. See the “UNIX 
System/
Solaris System” chapter in the CentreVuä CMS 
Administration (
585-215-820) document for procedures on backing 
up data via the 
UNIX system.
a.  UNIX is a registered trademark of Novell, Inc.
b.  Solaris is a registered trademark of Sun, Inc. 
						

							  Advanced Report Design CentreVu CMS Custom Reports
Creating a Custom Data Table6-3
Step 1: Access 
the 
CentreVu 
CMS Database 
in 
INFORMIX6
To access the CentreVu CMS database in INFORMIX, use the following 
steps:
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/isql. 
Press the   key.®The INFORMIX logo 
appears, followed by the 
INFORMIX Main Menu.
Commands
Return
Return
Return
 
INFORMIX-SQL: Form Report Query-language User-menu Database Table Exit
Run, Modify, Create, or Drop a form.
-------------------------------------------- Press CTRL-W for Help -------- 
						

							  Advanced Report Design CentreVu CMS Custom Reports
Creating a Custom Data Table6-4
Step 2: Build 
the Table
6
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.
NoteTo escape from a step and go back to the previous step, press   
or  , depending on your keyboard.
2a.At the 
INFORMIX Main Menu, 
select Table.®The Select Database 
screen appears.
Return
Delete
Del
 
SELECT DATABASE >>
Choose a database with the Arrow Keys, or enter name, then press Return.
------------------ cms --------------------- Press CTRL-W for Help --------
CMS
2b.Ty p e cms, and press  .®The Table menu appears.Return
 
TABLE: Create Alter Infor Drop Exit
Create a new table.
------------------ cms --------------------- Press CTRL-W for Help --------
2c.Select Create.®The Create Table screen 
appears. 
						

							  Advanced Report Design CentreVu CMS Custom Reports
Creating a Custom Data Table6-5
CREATE TABLE >>
Enter the table name you wish to create with the schema editor.
------------------ cms --------------------- Press CTRL-W for Help --------
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.”
Return
 
CREATE TABLE c_workcode: Add Modify Drop Screen Exit
Adds columns to the table above the line with the highlight.
------ Page 1 of 1 --------- cms -------- Press CTRL-W for Help --------
Column Name Type Length Index Nulls
......................................
..
......................................
2e.Select Add.®The Add Name screen 
appears.
 
ADD NAME >>
Enter column name. RETURN adds it. INTERRUP returns to CREATE/ALTER menu.
------ Page 1 of 1 --------- cms -------- Press CTRL-W for Help --------
Column Name Type Length Index Nulls
......................................
..
...................................... 
						

							  Advanced Report Design CentreVu CMS Custom Reports
Creating a Custom Data Table6-6
The available data types are in the following table.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.
Return
 
ADD TYPE c_workcode : Char Number Serial Date Money date-Time Interval
Prints any combination of letters, numbers, and punctuation.
------ Page 1 of 1 --------- cms -------- Press CTRL-W for Help --------
Column Name Type Length Index Nulls
cwc ...........
..
........... 
						

							  Advanced Report Design CentreVu CMS Custom Reports
Creating a Custom Data Table6-7
Table 6-1:  Data Types
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 decimal 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.
date-TimeNot supported by CMS Custom Reports. However, it 
is supported by INFORMIX. 
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.
IntervalNot supported by CMS Custom Reports. However, it 
is supported by INFORMIX. 
An interval column allows you to specify an 
interval 
qualifier of year, month, day, hour, or minute.
MoneyNot supported by CMS Custom Reports. However, it 
is supported by INFORMIX. 
If your data will be dollars and cents, you should use 
Decimal. 
						

							  Advanced Report Design CentreVu CMS Custom Reports
Creating a Custom Data Table6-8
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. 
Which prompts appear and what 
order they appear in depend on the 
data type you selected. Table 6-2
 
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 you want to add.
Table 6-2:  Prompts for 
INFORMIX Data Types
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.
DUPLICATESEnter 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.
Note: The DUPLICATES prompt displays if you 
answer yes to an INDEX prompt. 
						

							  Advanced Report Design CentreVu CMS Custom Reports
Creating a Custom Data Table6-9
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. Table 6-2:  Prompts for 
INFORMIX Data Types  — Continued
Prompt What to Do
2j.When you have added all columns, 
press the   key until the 
Create Table screen appears.®The Create Table menu 
appears.
Return 
						

							  Advanced Report Design CentreVu CMS Custom Reports
Creating a Custom Data Table6-10
 
ALTER TABLE c_workcode : Add Modify Drop  Screen Exit
Adds columns to the table above the line with the highlight.
------ Page 1 of 1 --------- cms -------- Press CTRL-W for Help --------
Column Name Type Length Index Nulls
cwc Char 16 Unique No
price Decimal 8.2 No
......................................
..
......................................
2k.Select Exit.®The Build-new-table menu 
appears.
 
Exit c_workcode: Build-new-table Discard-new-table
Builds a new table and returns to the Table Menu.
------ Page 1 of 1 --------- cms -------- Press CTRL-W for Help --------
Column Name Type Length Index Nulls
cwc Char 16 Unique No
price Decimal 8.2 No
2l.Select Build-new-table.®The Table menu 
reappears. If no errors are 
found, then your table has 
been added. If errors are 
found, then you must go 
back to the
 Alter Table 
screen and correct these 
errors. You must then 
repeat steps 2k and 2l until 
the
 Table menu reappears.
2m.Select Exit to return to the 
INFORMIX Main Menu.®
The INFORMIX Main Menu 
appears.