Dynamically filtering queries

You can use parameter in a WHERE clause so that the query result can vary each time according to the entered parameter value. See the following example.

  1. Open the demo catalog SampleReports.cat, then in the Catalog Browser, expand the desired data source and create a type-in parameter named IDSet of Integer type with the default values 1, 10, and 20, and enable the Allow Multiple Values option.
  2. In the same data source, create a query CustomersInfo on the table Customers. Select all the fields contained in the table.
  3. Filter the records of the query by adding a condition as follows in the Search Condition dialog (for details, see Filtering with the filter format):

  4. Create a table report based on the query as follows: have the fields Customer ID, Customer Name, City and Phone displayed in the table and apply the default style.
  5. View the report. In the Enter Parameter Values dialog, click next to the value field of IDSet.
  6. In the Enter Values dialog, uncheck the All option, add the listed values 1, 10, and 20 to the right box, and then click OK. You will find that the records with Customer ID equal to 1, 10 and 20 have been retrieved.

  7. View the report again. This time, in the Enter Values dialog, remove 1, 10, and 20 from the Selected Values box first. Enter the values 3, 7, 9, 11, 15, and 25 in the Enter Values field and add them into the Selected Values box one by one. Then click OK. Now, the records for the specified Customer IDs are displayed.

    To view the report with full data, select the All option and then click OK in the Enter Values dialog.

Notes: