WebFOCUS
Online Help > Managed Reporting Developer > Creating Graphs With Advanced Graph Assistant > Filtering Data for a Graph
Filtering Data for a Graph
When generating a graph and specifying which fields
to display, you may want to limit or filter the data that appears
in your graph. By including record selection criteria, you can filter
out irrelevant data and display only those field values that meet
your needs. You can select a subset of the data which can be easily
redefined each time the graph is generated.
When developing a graph, you can define
record selection criteria based on:
- The values of an individual field (using
a WHERE statement).
- The aggregate values of a field, for
example, the sum or average of a field's values (using a WHERE TOTAL
statement).
Creating Filtering Expressions
Filtering expressions enable you to display only those
records that pass your selection criteria. These expressions 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. The Filters icon in the Available Fields tab
enables you build WHERE and WHERE TOTAL expressions to create your
record selection criteria.
Procedure: How to Filter Data Based on Values, Parameters, Fields, or a Constant
-
Click
the Data selection tab.
-
Click
the Filters icon in the query pane below the
Available Fields tab.
-
Double-click
the field in the Available Fields list that you want to use for filtering
data.
or
Drag the field in the Available Fields list into
the pane below the graph preview area.
-
In the
pane below the graph preview area, select WHERE or WHERE
TOTAL from the drop-down list on the left.
The following image shows the result of adding the REGION
field to a filtering expression in the pane below the graph preview
area.
When
you have more than one filtering expression, the drop-down list
on the left has OR and AND as valid selection values, in addition
to WHERE and WHERE TOTAL.
-
Select
an operator (for example, EQUAL to) from
the drop-down list on the right.
The following image shows the list of available operators
you can use.
-
Click
the Select Value button.
The Select Value dialog box opens, as shown in the following
image.
-
Select
one of the following options in the Select Value dialog box to provide
the value for the filtering expression you are creating:
-
Constant. When
you select the Constant value option, enter a value in the input
area to the right.
-
Parameter. When
you select the Parameter value option, click the ellipse button
(...) to the right to open the Variable Editor dialog box, which
is used to create a parameter for the filtering expression. For
details, see Setting Parameter Properties.
-
Field. When
you select the Field value option, a list of the available fields
in the selected data source appears in the area to the right. Click
to highlight the desired field.
-
Values. When
you select the Values option, a list of valid data source values
associated with the field you selected appears in the area to the
right. Click to highlight the desired field value.
-
Click OK and
the WHERE or WHERE TOTAL filtering expression appears below the
graph preview area.
For example, if you selected the
Constant option and entered Espresso as the value, the expression
would appear, as shown in the following image.
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 Data selection 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
Value.
A dialog opens to select values.
-
Click
the import button, as shown in the following
image.
A
dialog box opens, as shown in the following image.
-
Select
one of the following File Format options:
- Flat file
- Excel Spreadsheet
(XLS)
-
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 values dialog.
-
Optionally,
you can remove specific values from the Multiple values 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 to use with your selection
criteria, you can set the properties for each 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 Variable Editor
-
When
the Variable Editor dialog box opens, enter appropriate text in
the Name and Description fields for the parameter.
These fields may be automatically filled in based on the
field you selected to create the filtering expression.
-
Choose
from the following Selection options:
-
Dynamic. The
Dynamic option is the default selection. The Data Source and Field
values, which you may change as needed, are selected by default
based on the field you selected to create the filtering expression.
Note that when working with a Reporting Object, you can only select
fields from the data sources the Reporting Object is defined to
access.
-
Static. The
Values area at the bottom of the Variable Editor is activated. Select
the Constant or Value option. For constant, enter a value or values.
For the value option, all of the values for the selected field appear
in the Value list box. Move the values you want to the Multiple
values entered area using the plus minus buttons.
-
Select multiple values at run time. Instead
of choosing the dynamic or static options, you can select to be
prompted for multiple values at run time.
The
following image shows the Variable Editor dialog box.
-
Click OK to
exit the Variable Editor dialog box.
-
Click OK to
exit the Select Values dialog box.
Combining Filtering Expressions
You can combine multiple filtering expressions to further
refine an existing WHERE or WHERE TOTAL statement to more precisely
select the desired data for your graph.
Procedure: How to Combine Expressions
-
Create an expression (WHERE or WHERE
TOTAL statement). For detailed instructions, see How to Filter Data Based on Values, Parameters, Fields, or a Constant.
-
When you create additional expressions,
they are combined with the default operator AND, as shown in the
following image, which displays two statements, the first beginning
with WHERE and the second with AND.
-
Select AND or select OR using the drop-down
list for all additional expressions added after the first WHERE
or WHERE TOTAL statement.
Note: You
can click any of the gray (inactive) parentheses to activate them
and group sections of an expression together to optimize your ability
to filter data. Parentheses should always be used in pairs.
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.
-
When
you are prompted to delete the expression, click OK.