Home > Query and Report Design Download RBT v1.3.1

Filter Pane

The Filter pane provides an interface to setup data filtering and runtime parameter entry.

database sql query filter setup

Filter Icon Definitions

The Filter Pane icons are described below:

- add filter column, displays the Select Document Columns window

- sql, displays the SQL Select window showing sql that will be generated from the current document design

- run, runs the current document design and displays Document Results in browser pane

- edit settings, displays the Edit Document Settings window to modify document parameters

- save, saves current document

- logout, exits RBT Designer

- displays help page for current window

- delete, deletes column selection

- display the Custom Where pane to allow free form where value entry.

- value lookup, display the Add/Edit Lookup window to create document filter criteria selection

- date select, displays the Select Date pane

Selecting Filter Columns

Click the Select Filter Columns icon to display the Select Document Columns pane. Check the columns you wish to use in the filter. To add the same column multiple times, repeat the process described above. Click the plus to open a table to display table columns. Primary key columns are signified by the key icon . Columns selected for document display are indicated by a green check .

selecting database filter columns

Place the cursor over the column icon to display a tool tip with the column type.

displaying database column data type

To remove a filter column click the red

Selecting Filter Parameters

The RBT Designer defaults logical operations to and. To change the operation, click the Logical Operator dropdown and select the desired operator. The parenthesis dropdowns allow the user to create parenthetical operations such as column1 = value1 and (column2 = value2 or column2 = value3). Click the Comparison Operator dropdown to select a standard SQL comparison.

selecting the logical operator selecting opening parenthesis selecting closing parenthesis selection comparison operator

Entering Comparison Values

Simple comparisons can be entered in the Comparison Value field

simple comparison value entry display

You can also enter more complex SQL comparisons

comparison value entry using sql like opertor

comparison value entry using sql in operator

If the Comparison Value type is a Date or a Timestamp the Date Select icon will display and the date/timestamp is be selected using the Select Date pane.

comparison value date entry

Specifying a Custom Where Value

By default, date, time, timestamp and numeric where value entries are controlled at the input field to ensure that the user enters a valid parameter. Dates, Times and Timestamps must be selected with the Date Select pane and numeric entries will only accept numeric characters. A question mark is a valid entry as described in the next section. At times this limitation is too restrictive. In RBT, the designer can create a custom where clause that accepts free form text. It should be noted that there are no edit checks applied to the free form where clause entry - it will be submitted to the DBMS as entered. That said, if the designer is familiar with the DBMS-specific SQL syntax, this functionality provides a flexible means of creating complex filter logic. To add a custom where clause click the Custom Where icon custom where on a filter pane entry to display the Custom Where pane and enter the desired where value.

custom where pane

A custom where entry will display with a "[custom]" prefix in the filter pane

custom where prefix

Creating Runtime Parameter Entries

A designer can create filter comparisons that will prompt the user for input at document runtime. Runtime entries can be created in 2 ways. If a question mark (?) is entered in the Comparison Value field, a parameter entry window will display at document runtime to allow user entry.

configuring filter comparison values for runtime parameter entry

sql query filter parameter runtime entry pane

By default, when a runtime parameter entry field is created the column display name is displayed. To display a user-friendly input prompt, enter it in the User Prompt field

setting user-friendly runtime parameter display prompt

user-frienly runtime parameter entry prompt

A user can also create runtime parameter input select lists. The first step in this process is to create a document to load the desired data from the database - See the Welcome Pane for information on creating a new document The first field must be a unique id field and the second is the display field.

Now you can create a lookup parameter selection using the new document. Click the Lookup icon to display the Add/Edit Lookup window and select the document to use for this lookup.

creating a document for sql filter runtime parameter lookup selection

A Comparison Value field with a lookup assigned will display as shown below.

active query filter lookup display

Now when the document runs a dropdown select will display for user input.

document filter runtime parameter single section lookup

You can create a multi-select runtime parameter input by checking the Allow Multi-Select checkbox. Enter the desired number of display rows in the Rows input field. When the document is run a multi-select input parameter list will be displayed. When a multi select lookup is chosen the Comparison Operator will be set to in and will be read-only. To edit or remove a lookup just click on the Lookup icon

document filter runtime parameter multi-select entry

document filter runtime parameter multi-select entry

Displaying Document SQL Statement

To see the SQL that the current design will generate, click the Show SQL Select icon on the toolbar.

generated sql query statement

Edit Document Settings

Click the Edit Document Settings icon to modify the current document settings as described in Adding a New Document in the Welcome Help section.

Running Current Document

Click the Run icon to run document with current configuration.

Saving Document

Click the Save Document icon to save current document design. When blue, the save icon is enabled.