WebFOCUS
Online Help > Managed Reporting End User > Creating a Report With Report Assistant > Selecting Records for a Report
Selecting Records for a Report
When generating a report and specifying which fields to display,
you may not want to show every instance of a field. By including
selection criteria, you can display only those field values that
meet your needs. In effect, you can select a subset of the data,
which you can easily redefine each time you issue the report request.
When developing a report request, you can define criteria to
select records based on:
- The values
of an individual field (using a Where statement).
- The aggregate
value of a field, for example, the sum or average of a field's values (using
a WHERE TOTAL statement).
The Selection criteria tab enables you to create Where or Where
Total statements for record selection.
Creating a Where or Where Total Statement
Where and Where Total statements enable you to display
only those records that pass your selection criteria. These statements
select the data source records to be included in a report. The data
is evaluated according to the selection criteria before it is retrieved
from the data source. Use the Selection criteria tab to create a
new Where or Where Total statement.
Note: In the Report Assistant, when you create a Where
statement in the Selection criteria tab for a range of dates using
FROM - TO, you will not be alerted if the TO field is chronologically
before the FROM field. For example, if you enter something like
"from 12/31/05 to 1/1/05", there will be no records in the resulting
report.
Procedure: How to Select Records Based on Values, Parameters, Fields, or a Constant
-
In the
Report Assistant, click the Selection criteria tab.
-
Select
a field from the Available Fields window and drag the field to the Screening
conditions pane.
or
Press Alt+M to go to the
Available Fields window, tab to the field and press Enter to
add the chosen field.
The field name and EQUAL to and Select
values appear in the Screening conditions pane.
-
Select WHERE or WHERE TOTAL.
-
Select
an operator from the drop-down list (for example, EQUAL
to) in the right pane.
-
Click Select
values to complete the expression.
-
Select
one of the following compare type option buttons:
-
Constant. Enter
a value that will serve as a constant (a value that will not change).
For example, enter vehicle.
After you click OK,
the constant, vehicle, appears in the WHERE statement which reads
BODYTYPE EQUAL to vehicle.
-
Parameter. Enter
the name of the variable. For example, moped as shown in the following
image with the Parameter option button selected and the variable
name entered as moped.
You
can set parameter properties, for details see Setting Parameter Properties.
After
you click OK, the parameter, &moped,
appears in the WHERE statement which reads BODYTYPE EQUAL to &moped.
Note: The
ampersand is automatically added by WebFOCUS.
-
Fields. Select a field from the
list.
After you click OK,
the field BHP appears in the Where statement which reads CAR.COMP.CAR
EQUAL to CAR.SPECS.BHP.
-
Values. Select
a value for the field you initially chose (in this case, car).
After
you click OK, the value, Audi, appears in
the Where statement which reads CAR.COMP.CAR EQUAL to AUDI.
Procedure: How to Create a Where Statement Using Date and Time Values
In
the Report Assistant,
-
Click
the Selection criteria tab.
-
Select
a field from the Available Fields window and drag the field to the Screening
conditions pane.
or
Press Alt+M to go to the
Available Fields window, tab to the field and press Enter to
add the chosen field.
-
Click EQUAL
to in the right pane to display a list of operators.
Click the operator of your choice.
-
Click Select
values to complete the expression.
-
Click Field to
access date and time values for that field. For example, EYEAR.
-
Click Values and
click the value of your choice. For example, 1989/01/18.
After you click OK, the value, 1989/01/18, appears
in the WHERE statement which reads START_DATE EQUAL to 19890118.
Importing Values From External Files for WHERE Statements
When developing a query, you can use a locally saved,
external file as selection criteria to limit query results. This
enables you to quickly build a query containing a large number of
WHERE statement values without having to manually enter repetitive
or readily available data.
Procedure: How to Create a Value List From an External File
-
Navigate
to the Selection criteria area.
-
Begin creating a WHERE statement
by selecting a field from the list of available fields.
-
Select
a data comparison option that can accept multiple values.
Valid options include EQUAL to, NOT EQUAL to, IN literal
list, NOT IN literal list, EXCLUDES literal list, and INCLUDES literal
list.
-
Click Select values.
The Values dialog opens.
-
Click the import button as shown
in the following image.
A dialog box opens
as shown in the following image.
-
Select either the Flat file or Excel Spreadsheet (XLS)
File Format option.
-
Click the Browse button.
A standard file selection dialog opens.
-
Select an external file to import from your local machine
or network.
Note: The external file must only contain text with
new line delimiters.
-
Click OK.
The values contained in the file are displayed in the Multiple
values entered list in the right pane of the dialog.
-
Optionally, you can remove specific values from the Multiple
values entered list or move values up or down within the list.
-
Click OK.
You can save the query for future use and reopen the query
to append new or remove existing values.
Setting Parameter Properties
When you create parameters for Selection Criteria (WHERE
statements) you can set the properties for the parameter. For example,
you can create a dynamic or static list of values to choose from
at run time.
Procedure: How to Set Parameter Properties
-
In the
Report Assistant, click the Selection criteria tab.
-
Create
a WHERE or WHERE TOTAL expression.
-
Select
an operator. Some operators are not supported with the Variable Editor,
see Supported Operators for the Variable Editor for details.
-
Click Select
values.
The Variable Editor dialog box opens.
-
Select
the Parameter option.
-
Click
the Auto Prompt button.
The Parameter Properties dialog box opens.
-
Verify the
Name and Description fields, which are automatically populated based
on the field you selected for the WHERE clause. You can edit the populated
values as needed.
Note:
- If the Description
is a single word and the parameter is for a numeric field, you must
add a space at the end of the description to avoid a parsing error.
- The Description
cannot contain a period (.) because a period is the delimiter to specify
the beginning and end of the description value.
-
Choose
the Selection type, Dynamic or Static.
The Dynamic option is selected by default.
Optionally,
choose the Select multiple values at runtime check
box to provide multiple values to the report from the auto prompt
page at run time.
-
Set the parameter values.
-
For the Dynamic selection type, the
Data Source and Field values are selected by default based on the
field you are screening on and can be changed as needed.
If you want to see the fully qualified
Field name, select the Display fully qualified field
name check box. This setting is for display purposes
only and is not preserved.
Note: When
working with a Reporting Object, you can select only fields from
data sources that the Reporting Object is defined to access.
-
For the
Static selection type, select the Constant or Show
field values option. For Constant, enter one or more values.
For Show field values, all of the values for the selected field
appear in the list box. Move the values you want from the selection
list to the Prompt values list.
-
Click OK to
exit the Parameter Properties dialog box.
Reference: Supported Operators for the Variable Editor
|
Operator
|
Enabled for the Variable editor?
|
|---|
|
Equal to
|
Yes
|
|
Not equal to
|
Yes
|
|
Greater than
|
Yes
|
|
Less than
|
Yes
|
|
Greater than or equal to
|
Yes
|
|
Less than or equal to
|
Yes
|
|
In literal list
|
No
|
|
Not in literal list
|
No
|
|
Missing
|
No
|
|
Not missing
|
No
|
|
From - to
|
No
|
|
Not-from - to
|
No
|
|
Includes literal list
|
Yes
|
|
Excludes literal list
|
Yes
|
|
Contains characters
|
Yes
|
|
Omits characters
|
Yes
|
|
Like character mask
|
Yes
|
|
Not like character mask
|
Yes
|
Use the right pane of the Selection criteria tab to
use or delete existing Where statements and to combine expressions.
Procedure: How to Combine Expressions
-
Create
an expression.
-
When
you create additional expressions, they are combined with the default
operator AND, as shown in the following image with two statements,
the first a WHERE and the second an AND statement.
-
Use
the drop-down list to select from AND, OR, WHERE, and WHERE TOTAL.
Procedure: How to Delete an Active Expression
-
Select
the check box next to the expression you want to delete. Click the Delete icon
above the box.
-
You
are prompted to delete the expression. Click OK to delete.
Grouping Expressions Together With Parentheses
You can use parentheses to group expressions together
to optimize the Where statement.
Procedure: How to Group Where Statements Together Using Parentheses
Click
the grayed out parentheses to activate the parentheses and group
expressions together.