Lucent Technologies Centrevu Report Designer Version 8 User Guide

							  Edit | Queries CentreVu Report Designer Version 8 User Guide
Creating a Real-Time or Historical Query7-9
If you are creating a Historical query, a Unique check box displays 
next to the S
ELECT box. Select this check box to make this query 
unique in the current report. What this means is that, if the query 
returns multiple rows of the same data, then only one row of the data 
will display on the report.
This window is where you will define the SQL “where” clauses that 
will be used to retrieve data for the report. The SQL where clause 
tells the CMS to retrieve the data defined in the S
ELECT box that 
matches the criteria defined in the W
HERE box. The SELECT box 
specifies which 
columns of data to consider in the database table(s). 
The W
HERE box species which rows of data to consider in the 
database table(s).
For more information on SQL where clauses, see Chapter 18, “SQL 
Query and CMS Database Tables Basics” in this document.
This window includes the following elements:
Item Purpose
S
ELECT 
boxShows the Query Items that were defined on the 
previous windows, including the database items and 
calculations and any associated functions assigned to 
the items, and the database table from which the 
database items and calculations are to be retrieved.
The information displayed in the S
ELECT box cannot be 
edited. 
						

							  Edit | Queries CentreVu Report Designer Version 8 User Guide
Creating a Real-Time or Historical Query7-10
The ACD operand is automatically added to the beginning of each 
W
HERE clause.
20. In the Operand1
 box, highlight the first database item or calculation 
which you want considered in the W
HERE clause.
21. In the O
perator box, highlight the appropriate operation.
22. In the Operand2
 box, highlight the second item (input, database 
item, calculation, or item defined in the Other box on the “Add the 
database items and calculations for the SELECT portion of the 
query” window) which you want considered in the W
HERE clause.
For example, if you wanted the report to select the data from the 
table when the number of ACDCALLS is greater than the number of 
ACWINCALLS, then you would select acdcalls in the Operand1 
box, the > sign in the Operator box, and ACWINCALLS in the 
Operand2 box.
23. Select the AND or the OR button, as appropriate.
If you place “and” between clauses, then both of the clauses must 
be true in order for CMS to retrieve the SELECT data from the table. Operand1
 
boxLists the database items and calculations that can be 
used in the W
HERE clause.
O
perator 
boxLists the standard mathematical operations that can be 
used in the W
HERE clause.
Operand2
 
boxLists the database items, calculations, and inputs that 
can be used in the W
HERE clause and as inputs for the 
input window.
W
HERE 
boxShows the current WHERE clause.
AND 
buttonPlaces an AND before the currently selected Operand1, 
Operator, and Operand2.
OR buttonPlaces an OR before the currently selected Operand1, 
Operator, and Operand2.
New InputTakes you to the Edit Inputs window, where you can add 
new input fields for the Report Input window. Any new 
inputs you add will appear in the Operand2 box.
Te s t  
buttonAllows you to test the query for basic SQL syntax errors. Item Purpose 
						

							  Edit | Queries CentreVu Report Designer Version 8 User Guide
Creating a Real-Time or Historical Query7-11
If you place “or” between clauses, then either of the clauses can be 
true in order for CMS to retrieve the SELECT data from the table.
24. Repeat Steps 20 through 23 until you have completed the query.
You can add your own text in the W
HERE clause (such as 
parenthesis and numbers) by placing your cursor where you would 
like the text to appear and then typing in the text.
If you are creating a report that will display data for multiple 
splits/skills or VDNs on multiple ACDs, you can type MULTI_ACD in 
the where clause, instead of entering each individual OR statement. 
This will only work if you have correctly set up the inputs for the 
multi-ACD report. See Chapter 6, “Edit | Inputs,” for information on 
setting up the inputs for a multi-ACD report.
25. Select the Test button.
CMS checks the syntax of the query and returns a message with 
any errors.
For information on the most common query errors, see Chapter 16, 
“Error Messages,” in this document. 
Note that some errors are not detected until you run the report.
26. Correct any of the errors detected in the Test.
27. Select the N
ext button.
The Query Assistant “Enter a name for this query” window appears:
28. In the N
ame text box, type the name that you would like to assign to 
the query you created.
29. Select the D
one button. 
						

							  Edit | Queries CentreVu Report Designer Version 8 User Guide
Creating an Integrated Query7-12
Creating an Integrated 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 window appears:
2. Select the N
ew button.
The Query Assistant “Select a database and one or more tables for 
the query” window in appears:
3. Select the Database radio button next to Integrated.
The tables that are available for integrated reports display. 
						

							  Edit | Queries CentreVu Report Designer Version 8 User Guide
Creating an Integrated Query7-13
4. In the T
able list, highlight the name of the table which includes the 
database items or calculations that you want to include in the query.
For integrated reports, you can select one table per 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.
6. In either the Database I
tems or Calculations box, highlight the first 
database item or calculation that you will reference in the query.
7. Select the A
dd 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. 
						

							  Edit | Queries CentreVu Report Designer Version 8 User Guide
Creating an Integrated Query7-14
9. Select the Next button.
The Query Assistant “Enter the start time for this integrated query” 
window appears:
10. Select the I
nput Start Time or Select Start Time Now radio button. 
If you select the Select Start Time N
ow radio button, you need to 
define the time that the integrated data will always begin 
accumulating for this report.
If you select the Input Start Time, a “Start Time” field will be added to 
the report input window and users can individually define the time 
that data will begin accumulating for the report. 
						

							  Edit | Queries CentreVu Report Designer Version 8 User Guide
Creating an Integrated Query7-15
11. Select the N
ext button.
The Query Assistant “Select the input used to indicate the xxx for 
this query” window appears:
On this window, you can choose the input item that will be used for 
the query.
12. Highlight the input(s) you would like to use as criteria for the query. 
You can create a new input by selecting the New Inp
ut button, 
which takes you to the Edit Inputs assistant.
Only the inputs that apply to the table you selected for this query will 
appear.
13. Select the N
ext button. 
						

							  Edit | Queries CentreVu Report Designer Version 8 User Guide
Creating an Integrated Query7-16
The Query Assistant “Enter a name for this query” window appears:
14. In the N
ame text box, type the name that you would like to assign to 
the query you created.
15. Select the D
one button. 
						

							  Edit | Queries CentreVu Report Designer Version 8 User Guide
Editing an Existing Real-Time or Historical Query7-17
Editing an Existing Real-Time or Historical Query7
To edit an existing real-time or historical query 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 window appears:
2. In the Q
ueries box, highlight the name of the query you would like to 
edit.
3. Select the E
dit button.
The Query Assistant “Add the database items and calculations for 
the SELECT portion of the query” window appears. The Database 
Items and Calculations that are already included in the query are 
listed in the Query Items list. 
						

							  Edit | Queries CentreVu Report Designer Version 8 User Guide
Editing an Existing Real-Time or Historical Query7-18
4. If you would like to add database items or calculations to the Query 
Items list, highlight the item in the Database I
tems or Calculations 
box and then select the A
dd button.
If you would like to remove a database item or calculation from the 
Q
uery Items list, highlight the item in the list and then select the 
R
emove button.
If you would like to edit a database item or calculation on the Q
uery 
Items list, highlight the item in the list and then select the E
dit 
button. The following window appears:
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.
5. Repeat Step 4 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.
6. Select the Where button.