Complex Runtime Filters

There are occasions when you might want a user to run a report that has a complex set of advanced filters, including the use of several and and or conjunctions. For example, you might want to pose the following query filters (this is not a real query, but it is useful as an example):

   (Customer = * Ask at runtime * AND Status = Open)
OR (Customer = * Ask at runtime * AND Status = Fixed)
OR (Customer = * Ask at runtime * AND Status = Duplicate)

This would be represented in the report editor like this:

Setting up a complex query with runtime filters

When the report is run, the user will see the following screen:

Running a complex query with runtime filters

When the user selects a customer from the top list, all the bottom filters that require a Customer will be set to use the value set from the top list. To accomplish this type of query with complex runtime filters, you set up Session Variable type fields in the Data Dictionary. Session Variable fields are created as follows:

  • Administration menu
  • Site Configuration tab
  • Data Dictionary menu
  • Session Variables tab
  • Add button
  • Provide a Fixed Name for the field
  • Provide a Title for the field. It is helpful to use a title like Choose the XXXXX so that the runtime filter will show a useful title. Obviously, XXXXX represents the title of the filter field
  • Choose the Display Type for the field. It is important to understand that you need a session variable field for every filter that you will use in your complex queries, and that the display type must match the display type of the filter field it is being used with
  • Add the field to the data dictionary.

Once this has been done, you will see that the advanced filter lists look something like this to the end users:

Session Variable field to be used as a runtime filter

Note that the Session Variables appear at the bottom of list fields. If you are using the Session Variable as a filter within a text, date or number field, you should enter it as $$FIELD_NAME$$, substituting FIELD_NAME with the name of your Session Variable field.