Lucent Technologies CentreVu Call Management System Custom Reports Manual

							  Defining the Data for a Custom Report CentreVu CMS Custom Reports
Defining the Rows of Data for a Report4-43
As an example of a basic “where” clause, if you are creating a real-time 
report using data from the Current Interval Split table and you have defined 
three report fields for the report:
Split: (the SPLIT database item)
ACD Calls: (the ACDCALLS database item)
Average Talk Time: (the calculation ACDTIME/ACDCALLS)
Your “where” clause might be:
Select rows where: SPLIT = 5
When you run the report, 
CentreVu CMS finds the row in the table 
(Figure 4-12
) for Split 5 and fills in the report fields with data from that row (in 
bold in the figure).
Figure 4-12:  Sample Current Interval Split Table
Thus, the report fields show the following data:
Split: 5
ACD Calls: 451
Average Talk Time: 61 (the result of 27635/451)
NoteThis example of row search criteria would also include the selection 
of an ACD. See “Where” Clause for Selecting Rows from an ACD
” 
later in this section.
54 3
2
9 8
789 10203 603
851 2456441058400
777 988
.
.
.
.
..... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
. .... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
..... ..
.
.
.
......
ACDTIME
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
333899786 19768
35401 15321
2763553442 13111
20012 36898
ABANDONS
39 10 31
43 11
3259 20
37 48
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
ACDCALLS SPLIT
480 142 391
50993
451652 111
234 443
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
. .
.
.
.
. .
.
.
.
..
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
..
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
..
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
. . .. . .. . .
. . .
. . . ABNTIME
1
7 6
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ... 
						

							  Defining the Data for a Custom Report CentreVu CMS Custom Reports
Defining the Rows of Data for a Report4-44
“Where” Clause 
with Variable
4
The previous examples create hardcoded row search conditions. That is, 
when you run the report, 
CentreVu CMS always searches for the values you 
entered in the “where” clause. However, instead of a hardcoded value, you 
can enter a variable name in a clause. A variable name tells 
CentreVu CMS 
to search for whatever value(s) you or another user choose when you run the 
report. You must define a variable on the Define Input window before using it 
in a “where” clause. The variable name then links a report input field to the 
“where” clause that uses the value(s) entered.
In a clause with a variable name, you must always enter a dollar sign ($) in 
front of the variable. The format is as follows:
Expression Relational Operator $variable
As with a basic “where” clause, the Expression can be a database item or 
calculation. The relational operators available are the same as those 
available for a basic “where” clause.
Look at the following “where” clause:
Select rows where: SPLIT = $splitvar
This “where” clause tells 
CentreVu CMS to search for rows with the Split 
value the user enters when ordering the report. The example clause 
presupposes that the variable name, “splitvar,” has been defined on the 
Define Input window.
Multiple 
“Where” 
Clauses
4
To put two or more clauses in a statement, use and or or. Use and to 
define two or more clauses where 
CentreVu CMS finds only rows that meet 
all conditions. For example, the following statement searches for rows where 
splits had an average speed of answer greater than 30 seconds and 
abandons greater than 100.
Select rows where: ANSTIME/ACDCALLS > 30 and ABANDONS > 100
Use or to define two conditions where CentreVu CMS finds rows that meet 
either condition but not necessarily both. For example, the following 
statement searches for rows where splits had too many abandoned calls or 
too many extension-out calls.
Select rows where: ABANDONS>15 or AUXOUTCALLS+ACWOUTCALLS>7
NoteTo see the variable names you previously defined on the Define Input 
window, select the List inputs action list option. See “Step 2: List 
the Report Input Variables.” 
						

							  Defining the Data for a Custom Report CentreVu CMS Custom Reports
Defining the Rows of Data for a Report4-45
“Where” Clause 
with a 
Range/List 
Var ia bl e
4
If a variable name, as defined in the Define Input window, has been assigned 
the Range/list option, your “where” clause must use the equals sign (=) with 
the variable name. 
“Where” Clause 
for Selecting 
Rows from an 
ACD
4
In your “where” statement, you must always include a clause to select the 
ACD. If you always want a report to find data for the users current ACD, 
append and ACD = $acd to the “where” statement, as shown in the 
following example:
Select rows where: SPLIT = $splitvar and ROW_DATE = 
$datevar and ACD = $acd
If you use the variable name acd, you do not need to define the variable in 
the Define Input window. 
CentreVu CMS always understands acd to be the 
current ACD. However, if you wish, you can define a different variable name 
for the ACD database item so the user can specify the desired ACD(s) when 
ordering the report. You can also hardcode the ACD in a “where” clause, as 
in the following example.
Select rows where: ACD = 1
“Where” Clause 
for Excluding 
Rows of Data
4
If you want to exclude Split 5 from the report, but include all other splits, you 
might enter a “where” clause as follows:
Select rows where: SPLIT != 5
or
Select rows where: SPLIT  5 
						

							  Defining the Data for a Custom Report CentreVu CMS Custom Reports
Defining the Rows of Data for a Report4-46
Step 6: Select 
a Field/Bar 
Ty p e
4
Enter an x to select one of the following field/bar types.
Discrete
Select the Discrete type if 
CentreVu CMS will find only one value for each 
report field/bar to which you are assigning this Row Search ID. 
CentreVuCMS will find only one value if the field/bar is an aggregate 
function (SUM, AVG, MIN, or MAX), or if both of the following conditions are 
true:
lYou enter a “where” statement that is so specific that CentreVu CMS 
finds only one row.
lOn the Define Input window, you select n for Range/list for all variables 
used in the “where” statement. For example, if you select the Daily Split 
(dsplit) table and enter the following “where” statement:
Select rows where: ROW_DATE = $datevar and SPLIT = 
$splitvar and ACD = $acd
and you select n for Range/list for both the splitvar and datevar 
variables, 
CentreVu CMS will find a single row containing the date and 
split the user enters when ordering the report.
Repeated vertically
Select Repeated vertically to display a column of multiple field values 
or a vertical series of bars, one for each value.
If you select Repeated vertically, you must also enter a number in the 
Spacing field. This number tells 
CentreVu CMS how many lines to go down 
to display each value. 1 means to display a value on every line. 2 means 
to display a value on every other line.
Repeated horizontally
Select Repeated horizontally to display multiple field values in row 
format or a horizontal series of bars, one for each value.
NoteIf you are assigning the row search ID only to fields and bars that 
contain aggregate functions (sum, max, min, or avg), you will normally 
select the Discrete type. See “Repeating Aggregate Function 
Values” in Chapter 6 of this manual. 
						

							  Defining the Data for a Custom Report CentreVu CMS Custom Reports
Defining the Rows of Data for a Report4-47
If you select Repeated horizontally, you must also enter a number in 
the Spacing field. This number tells 
CentreVu CMS how many characters 
to move horizontally from the beginning of one value to the beginning of the 
next. This means that the spacing you enter must include the blank spaces 
between fields/bars and the width of a field/bar. For example, if a field/bar is 
four characters wide and you enter 8 in the Spacing field, 
CentreVu CMS 
displays each value with four blank characters in between.
Repeated values, whether repeated horizontally or vertically, apply only if 
CentreVu CMS finds multiple values for the report field(s). CentreVuCMS 
finds multiple values if one of the following conditions exists:
lYour “where” statement specifies one or more ranges of values.
lOn the Define Input window, you entered y for Range/list for at least 
one variable.
lYour “where” statement is general enough to select multiple rows.
One example of a general “where” statement for the dsplit table 
would be:
Select rows where: ROW_DATE = $datevar and ACD 
= $acd
Since no split values are specified, 
CentreVu CMS will display, for a 
user-specified date, a value for every split in the ACD.
If, based on your “where” statement and Define Input entries, 
CentreVuCMS 
finds multiple values, but you select Discrete, the report displays only the 
first value 
CentreVu CMS finds for each report field.
NoteIf you select Repeated vertically, you cannot define any fields 
directly under a repeated field. If you select Repeated 
horizontally, you cannot define any fields directly to the right of a 
repeated field. 
						

							  Defining the Data for a Custom Report CentreVu CMS Custom Reports
Defining the Rows of Data for a Report4-48
Step 7: Select 
a Sort Type
4
Enter an x to select either ascending or descending order for values of the 
database item(s) in the Order by field. Ascending order means 
CentreVu 
CMS displays data from the lowest to the highest values. Descending order 
means 
CentreVu CMS displays data from the highest to the lowest values.
Continuing with the previous example, if the Order by database item is 
ROW_DATE and you choose a descending order, the data will be listed as 
shown below.
Figure 4-13:  Sample of Data Ordered by Date in Descending Order
For dates and times, lowest values are those dates/times farthest in the past.DATE SPLIT ACDCALLS
10/12/92 1 7
10/12/92 2 6
10/12/92 3 5
10/11/92 1 25
10/11/92 2 50
10/11/92 3 41
10/10/92 1 40
10/10/92 2 36
10/10/92 3 30 
						

							  Defining the Data for a Custom Report CentreVu CMS Custom Reports
Defining the Rows of Data for a Report4-49
Step 8: Select 
a Sort Order 
for Data
4
Enter a database item (with its table name) in the Order by field to specify 
how multi-row data should be sequenced in the report. 
CentreVuCMS 
orders the rows of data by the values for the database item. For example, 
you might enter dsplit.ROW_DATE in the Order by field. 
CentreVu 
CMS would then display data by date, as in the following report.
Figure 4-14:  Sample of Data Ordered by Date in Ascending Order
If, on the other hand, you enter dsplit.SPLIT in the Order by field, 
CentreVu CMS displays data by split, as shown in the following report.
Figure 4-15:  Sample of Data Ordered by SplitDATE SPLIT ACDCALLS
10/10/92 1 40
10/10/92 2 36
10/10/92 3 30
10/11/92 1 25
10/11/92 2 50
10/11/92 3 41
10/12/92 1 7
10/12/92 2 6
10/12/92 3 5
DATE SPLIT ACDCALLS
10/10/92 1 40
10/11/92 1 25
10/12/92 1 7
10/10/92 2 36
10/11/92 2 50
10/12/92 2 6
10/10/92 3 30
10/11/92 3 41
10/12/92 3 5 
						

							  Defining the Data for a Custom Report CentreVu CMS Custom Reports
Defining the Rows of Data for a Report4-50
If you leave the Order by field blank, CentreVu CMS displays data in the 
report using the same sequence 
CentreVu CMS uses to store the data in the 
table.
For historical reports only, you can enter more than one database item in 
the Order by field. The database items must be separated by commas. 
The effect of more than one sorting database item is that data will be ordered 
first by values found for the first sorting database item, then sorted by values 
for the second sorting database item, and so on.
For example, Order by: dsplit.SPLIT,dsplit.ACDCALLS would 
cause the data shown in the previous illustration to be ordered as follows:
Figure 4-16:  Sample of Data Ordered by Split and ACD Calls
Notice that the data is first sorted by split number, then by the number of 
ACD calls. As a result, the dates appear totally out of sequence.DATE SPLIT ACDCALLS
10/12/92 1 7
10/11/92 1 25
10/10/92 1 40
10/12/92 2 6
10/11/92 2 36
10/10/92 2 50
10/12/92 3 5
10/11/92 3 30
10/10/92 3 41 
						

							  Defining the Data for a Custom Report CentreVu CMS Custom Reports
Defining the Rows of Data for a Report4-51
Step 9: Save 
Yo u r  R o w  
Search 
Conditions
4
After you have successfully saved your row search conditions, you can then 
assign the row search ID to report fields/bars.
Assign a Row 
Search ID to 
Report Field(s) 
and Bar(s)
4
To complete the definition of data for a field or bar, you must assign a row 
search ID to the field/bar. Use the following steps to do this.Select the Modify action list option.®Successful 
appears in 
the windows status line to 
indicate the Row Search 
conditions have been 
added.
NoteIf a field already has an assigned row search ID (for example, a field 
that is part of a design you have copied), you must first remove the 
existing row search ID assignment. See “Changing the Row Search-
Field Assignment(s)” later in this section.
1.On the Screen Painter, place the cursor on a field to which you want to 
assign a row search ID. To assign a row search ID to several fields at 
one time, place the cursor in a position from which you can define an 
appropriate block.
2.Select the Row search action 
list option.®
The Row Search window 
appears.
3.Enter the desired row search ID 
and select the Find one action 
list option.®The row search conditions for 
the ID appear.
4.Select the Assign fields 
action list option.®The Row Search window 
disappears, the cursor moves 
to its original position 
Move 
cursor to define a 
block and press RETURN 
appears on the status line. 
						

							  Defining the Data for a Custom Report CentreVu CMS Custom Reports
Defining the Rows of Data for a Report4-52
If your row search conditions specify discrete fields/bars, assigned fields 
appear with lowercase xs and assigned bars appear with uppercase Xs 
(see Figure 4-17
).
Figure 4-17:  Sample Discrete Field and Bar Design
A report with the design in Figure 4-17
 might appear as shown in 
Figure 4-18
. 5.Move the cursor using the  , 
, or arrow keys. If the 
block you define contains one or 
more characters of a field/bar, 
CentreVu CMS assigns the row 
search ID to that field/bar.®
The cursor defines a block on 
your terminal in inverse video 
(or color).
6.Press . ®The ? in each field/bar 
changes to the row search ID 
to indicate the row search 
conditions have been 
assigned. Additional 
characters fill the field or bar 
as shown in the following 
illustrations.
Tab
ShiftTab
Return
 
04/19/95 00:30 AM CentreVu(TM) CMS Windows: 2 of 5 ^ 
Custom Reports: Screen Painter: split status: Hist
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 Split: $$$$$$$$$$$$$$$$$$$$
XXXXX
XXXXX
XXXXX
XXXXX
XXXXX
XXXXX
XXXXX
XXXXX
XXXXX
XXXXX
XXXXX
XXXXX
XXXXX
XXXXX
XXXXX
XXXXX 100%1xxxxx
Percent
Within
Service
Level