Dynamic query use in building data-pull reports
HCL Compass lets
you construct queries with dynamic filters that prompt for parameter
values at runtime. However, when you use dynamic queries to build
data-pull reports, you must use a workaround to achieve the IS_BETWEEN
and IS_NOT_BETWEEN
operator
behaviors.
- An operator that specifies the filter operation
- A value that limits the selection criteria
For example, consider the sample query /Public Queries/Reports/State Query. This query includes a dynamic filter that lets you specify one or more state values. Each HCL Compass client provides an interface to prompt for the operator and filter values. In the data-pull reporting model, the HCL Compass Reporting Driver also needs a mechanism for retrieving this information.
Because dynamic filters prompt for two pieces of information, the HCL Compass Reporting Driver treats each filter as two separate parameters. At runtime, you specify the filter operator and values. The samples included on the distribution include a BIRT library that contains a sample operator parameter that you can use for displaying operators. You can use the sample parameter directly, or copy the controls for your own reports.
Filter operator values
HAS_NO_SUBSTRING
HAS_SUBSTRING
IS_EQUAL
IS_GREATER_THAN
IS_GREATER_THAN_OR_EQUAL
IS_IN_SET
IS_LESS_THAN
IS_LESS_THAN_OR_EQUAL
IS_NOT_EQUAL
IS_NOT_IN_SET
IS_NOT_NULL
IS_NULL
Special filter expressions for data-pull report design
The IS_BETWEEN
and IS_NOT_BETWEEN
operators
present a challenge when using the data-pull reporting model because
a total of three arguments (operator, low-operand, and high-operand)
are required. The HCL Compass Reporting
Driver does not directly support the IS_BETWEEN
and IS_NOT_BETWEEN
operators.
However, you can achieve the behavior of these operators by creating
a HCL Compass query
that uses two filters grouped with an AND
in the
query builder. At runtime, the HCL Compass Reporting
Driver creates four parameters for the two filters, simulating the IS_BETWEEN
and IS_NOT_BETWEEN
operators.
In the following examples, the text strings in bold are the values that you supply at runtime.
Consider the following filter expression:
DateField
IS_BETWEEN
12-12-2005
AND
12-12-2007
The same results can be achieved by using two filters, as shown in the next example:
DateField
IS_GREATER_THAN_OR_EQUAL
12-12-2005
AND
IS_LESS_THAN_OR_EQUAL
12-12-2007