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.

With dynamic queries, each filter contains the following information:
  • 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

Valid filter operator values are derived from the Team API interface hcl.ccm.wvcm.stp.cq.CqQuery.Filter.Operation. BIRT provides a mechanism for exposing each operator value as a text string. The operator values must be enclosed in double-quotes and are defined as follows:
  • 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