Filtering Systems Using an SQL Statement

As an alternative to filtering the data using the data band, the desired data can be filtered by applying an SQL statement to the data source. The advantage is that less data needs to be loaded from the database and therefore less memory is used.

This example is based on the report from the Filtering a single system example. To save the selected value from the dialog, right-click in the Dictionary panel and select New Variable. The New Variable dialog opens. Assign the name DfvSelectedSystem to this variable.

Now, save a value to the newly created DfvSelectedSystem variable. To do so, first double-click the OK button in the form. This opens the editor for the click event. Enter the following text in this editor:

DfvSelectedSystem = cbxSystems.SelectedKey

This assigns the selected value from the cbxSystems lookup box to the newly created DfvSelectedKey variable.

Instead of adding a filter to the data band as described in the Filtering a single system example, you can now use an SQL statement to filter the tHosts table on the selected value.

To adapt the SQL statement, right-click the tHosts table in the Dictionary panel, and then select Edit from the context menu. In the SQL statement, the Where clause can be used to filter the HostID on the value of the DfvSelectedSystem variable:

Select * from tHosts where tHosts.HostID = {DfvSelectedSystem}

To reference a variable in the SQL statement, enclose its name by braces { }.

The report will then only output the data for the selected system.