Lucent Technologies Centrevu Report Designer Version 8 User Guide

							  Edit | Inputs CentreVu Report Designer Version 8 User Guide
Viewing the Input Window6-15
Viewing the Input Window6
You can view what the input window will look like at any point during the 
process of designing the report by selecting Run from the Report menu.
To return to the design mode from the report input menu, select the 
Cancel button. 
						

							  Edit | Inputs CentreVu Report Designer Version 8 User Guide
Viewing the Input Window6-16 
						

							  Edit | Queries CentreVu Report Designer Version 8 User Guide
Introduction7-1
7  Edit | Queries
Introduction7
A report consists of fields, charts, and grids which display data that is 
retrieved from the Call Management System (CMS) database tables. The 
definition of what data is retrieved for a specific report is done using queries. 
This chapter teaches you to create queries for reports using the Query 
Assistant, which is accessed by selecting Query from the Edit menu. The 
sections of this chapter are:
lDefining Queries for a Report
lCreating a Real-Time or Historical Query
lCreating an Integrated Query
lEditing an Existing Real-Time or Historical Query
lEditing an Existing Integrated Query
lCopying a Query
lDeleting a Query. 
						

							  Edit | Queries CentreVu Report Designer Version 8 User Guide
Defining Queries for a Report7-2
Defining Queries for a Report7
To complete the definition of a reports grids, charts, or fields, you must 
define the queries that select the rows of the appropriate tables in the 
CMS database.
Queries are values for either database items or calculations. In most 
cases, the queries specify the input variables rather than specific values 
as criteria for retrieving information from the database. The input 
variables allow CMS to use the values entered in the Report Input 
window as the query criteria for the report. 
If you change the prompt in the Edit Inputs window, it is automatically 
updated in the query.
Queries tell 
CentreVu® CMS two things:
lWhere to get the data, and
lHow to use the data.
CentreVu CMS selects 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. 
CentreVu CMS 
identifies columns according to the data expression you define here.
For more information about SQL queries and the CMS Database Tables, 
refer to Chapter 18, “SQL Queries and CMS Database Tables Basics” 
and the 
CentreVu® CMS R3V8 Database Items and Calculations 
document (585-210-939). 
						

							  Edit | Queries CentreVu Report Designer Version 8 User Guide
Creating a Real-Time or Historical Query7-3
Creating a Real-Time or Historical Query7
To create a new query to be used in a Designer Report, complete the 
following steps from the Report Designer Design Mode window:
1. Select Query from the Edit menu.
The Query Select “Select an existing query or add a new one” 
window appears as shown below:
2. Select the N
ew button.
The Query Assistant “Select a database and one or more tables for 
the query” window in appears as shown below: 
						

							  Edit | Queries CentreVu Report Designer Version 8 User Guide
Creating a Real-Time or Historical Query7-4
3. Depending on the type of report you are creating, select the 
Database radio button next to Real-Time or Historical.
The tables that are available for the type of database (Real-Time, 
Historical, or Integrated) selected display.
For instructions on creating an integrated query, see the “Creating 
an Integrated Query” section in this chapter.
4. In the T
able list, highlight the name(s) of the table(s) which includes 
the database items that you want to include in the query. For Real-
Time reports, you can select only one table name. For Historical 
reports, you can select up to three table names.
Note that the calculations that are associated with the database 
items in the table(s) you choose will also be available for you to use 
in your query.
For information on the database items and calculations available in 
each table, see the 
CentreVu® CMS R3V8 Database Items and 
Calculations 
document (585-210-939).
5. Select the N
ext button.
The Query Assistant “Add the database items and calculations for 
the SELECT portion of the query” window displays, as shown below. 
On this window, you will add and remove database items and 
calculations that will be included in the query. 
						

							  Edit | Queries CentreVu Report Designer Version 8 User Guide
Creating a Real-Time or Historical Query7-5
6. In either the Database I
tems or Calculations box, highlight the first 
database item or calculation that you will reference in the query. To 
see a definition of the database item or calculation, click on the 
database item or calculation and then select the Data Item definition 
button.
In the O
ther box, you can type the names of Database Items or 
Calculations, strings, or constants that you want to use in the query. 
For instance, if you know that you would like the report to include 
information that subtracts the quantity of one database item from the 
quantity of another database item (for instance, ACDCALLS - 
ACDAUXOUTCALLS), then you could type a “-” in the Other box 
and add it to the list of Query Items. When you create the WHERE 
clause for the query, you can specify a calculation of ACDCALLS - 
ACDAUXOUTCALLS for the report.
Constants that have been defined in the Dictionary are listed in the 
C
alculations box in addition to the calculations.
7. Select the A
dd button, or double click on the item. You can view any 
database item or calculation by selecting the database item or 
calculation and then selecting the Data Item Definition button
The item is listed in the Q
uery Items box.
8. Repeat Steps 6 and 7 until all of the database items and calculations 
that you believe will be referenced in the query are listed in the 
Q
uery Items box.
You can remove an item from the Q
uery Items box by highlighting 
the item and selecting the Remove button.
You can rearrange the order of the items in the Q
uery Items box 
using the Move Up and Move Down buttons.
You can edit an item in the Q
uery Items box by highlighting the item 
and selecting the Edit button. When you select the Edit button, the 
following window appears as shown below:
An example of how you might want to edit an item would be to 
create a custom calculation in the edit box. For instance, you may 
add “callsoffered -” to the abncalls item shown above, so that the 
item would then read “callsoffered - abncalls” and that is the 
calculation that would be available for you to use in the query. 
						

							  Edit | Queries CentreVu Report Designer Version 8 User Guide
Creating a Real-Time or Historical Query7-6
9. Select the Next button.
The Query Assistant “Choose functions for the SELECT items” 
window appears as shown below:
If you are creating a query that uses Integrated Data, the steps differ 
from this point on. See the “Creating an Integrated Query” section 
for the correct steps.
You will use this window to assign aggregate (AVG, MAX, MIN, and 
SUM) functions to the Q
uery Items. A function is a prefix that is 
attached to a database item, a calculation, parts of a calculation, or 
a calculation name. CMS will display only one value on the report if 
an aggregate function (SUM, AVG, MIN, or MAX) is assigned to a 
database item or calculation.
10. Highlight the first query item to which you want to assign an 
aggregate function.
11. Highlight the Function that you would like to assign to the item.
The available functions are:
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 the following figure and you assigned the 
MAX function to the ACDCALLS database item, then ran the 
report for Split 1 for all intervals on 07/02/93, CMS would find 
all of the rows that are shown in bold. However, CMS would 
display only the value 418 (shown in the box), which is the 
maximum number of ACD calls in any single interval on 
07/02/93. Likewise, if you assigned the MAX function to the  
						

							  Edit | Queries CentreVu Report Designer Version 8 User Guide
Creating a Real-Time or Historical Query7-7
ACDTIME and ACDCALLS database items and then defined a 
where clause of ACDTIME/ACDCALLS for a report field, CMS 
would display the value 101.53 (which is the highest average 
talk time in any single interval on 07/02/93).
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 
assigned the MIN function to the ACDCALLS database item 
(instead of the MAX function) 
and then ran the report for Split 
1 for all intervals on 07/02/93, 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 above and you assign the SUM function to 
the ACDCALLS database item, then ran the report for Split 1 
and all intervals on 07/02/93, CMS would add all of the values 
for the ACDCALLS column on that date to display the value 
1611. 
lAV G  —  The AV G aggregate function retrieves the average of 
all values found over the time frame of the report. Using the 
sample table in above, if you assigned the AVG function to the 
ACDCALLS database item, and then ran 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, would be displayed on the 
report.
12. Repeat Steps 10 and 11 until you have assigned Functions to the 
appropriate Query Items.
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
4000229881 15628 24303
3617811523
29602
3765112530 26789
333899786 19768
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
. 
						

							  Edit | Queries CentreVu Report Designer Version 8 User Guide
Creating a Real-Time or Historical Query7-8
If you are creating a Real-Time query, you must assign Functions 
to all of the query items, or to none of the query items. Therefore, if 
any one of the query items does not have an applicable aggregate 
function, you cannot assign an aggregate function to any of the 
query items.
13. Select the N
ext button.
14. If you did not select more than one Historical database table for your 
query, skip to Step 19.
If you selected the Historical Database for your query and are using 
more than one table in the query, the “Specify all field joins for the 
tables” window appears as shown below:
This window allows you to require that items between two or three 
tables be equal in order to be used in the report. For instance, you 
might want to select the “acd” item from each agent and make a join 
clause to ensure that the report is for the same ACD information 
across the tables.
15. Highlight an item in one of the table lists.
16. Highlight an item in one or both of the remaining table lists.
17. Select the J
oin button. 
The join clause is shown in the Join C
riteria box.
18. Repeat Steps 15 through 17 until all of the necessary join clauses 
are listed.
19. The Query Assistant “Enter the SQL WHERE criteria” window 
displays, as shown below.