Like what you see? Have a play with our trial version.

 

Overview

Table conditions permit you to limit the data returned from a specific table. For example you may wish to limit your query of a sales table to specific Product transactions rather than allowing a user to query all data in the table.

 

Condition Builder

  1. The item that has been selected on the report data page for filtering. E.g. Cost.
  2. The Operator drop down lets you select what type of filter to be used. For example if the date is greater than 0
  3. The Value entered for the condition
  4. The brackets allow for greater complexity in data filtering by using AND and OR with the where clause operator.
  5. The where clause operator allows the user to set multiple filters using AND or OR. For example filter all People where AGE is Greater than 50 or Gender = Female.
  6. The move up and down buttons allows the user to build complex bracketing filters. The level of a statement within the bracket will affect the overall filter statement and impact upon the data returned in the query.
  7. The condition logic display allows a user to read a summary of the filter that they have defined. Click the refresh link to update the logic if conditions have changed in the form.

When defining a filter initially set the operator drop down to the desired value. The options within the drop down will vary based upon the type of field that the filter is to be applied to.

Possible values for the operator include:

Operator

Description

Equal to

Equal to a single alphanumeric or string value

Greater than

Greater than a single alphanumeric or string value

Greater than or equal to

Greater than or equal to a single alphanumeric or string value

Less than

Less than a single alphanumeric or string value

Less than or equal to

Less than or equal to a single alphanumeric or string value

Different from

Not equal to or different from a single alphanumeric or string value

Between

Between variable 1 and variable 2 these will need to be legitimate parameters such as date, age etc.

Not Between

Not between variable 1 and variable 2 these will need to be legitimate parameters such as date, age etc.

In List

One or more alphanumeric or string values

Not In List

Two or more alphanumeric or string values

Is Null

Record contains no value for selected attribute. No Parameter can be set

Is Not Null

Record contains a value for selected attribute. No Parameter can be set

Like

Records that contain the same letter or letters.

Starts With

String starts with letter or letters

Ends With

String end with letter or letters

 

Create New Basic Condition

  1. Select the table you wish to apply a condition to and click the properties link. This will open the table properties in the View Option Panel.
  2. Click the conditions expand icon to show the conditions and click the add link. This will open the conditions popup.
  3. Click New to create a condition, select the column you wish to place a condition on. Select the operator and enter a value. Click Add to add the condition into the list.
  4. Continue to add conditions in this manner – use the AND OR operators and bracketing to create complex conditions.
    Click the OK link to close the popup window and apply the changes to the conditions section of the table properties.

 

Current Date Conditions

Often it is useful to limit the result set in a view by a data condition. For example to only return the last 90 days data. This can be achieved through a Current Date condition on a view table.

  1. On the condition popup select the date that you wish to filter on. Based on selecting a date the current date option will be displayed.
  2. Select the + - for setting the condition and the number of days which are relevant.
  3. Click Add to add the item to your condition list. Click OK to save and close the popup.

 

Dynamic Conditions

A dynamic condition is a condition that is applied based on a user’s preference. For example a user may want to view currency data in their preferred currency. If you have a currency conversion table you will be able to create a calculated field based on a preferred currency.

Dynamic conditions can be used in a variety of ways such as converting metric into empirical measures, converting currencies or converting product descriptions into preferred languages.

How they Work

The dynamic condition works by allowing you to join you data to a translation table e.g. Currency. This table will have multiple values for each currency of interest – so normally you would create multiple records if the join had been applied by with no condition.

A dynamic condition is then applied to ensure that for each user only one possible currency is used. This privatises the data based on the currency selection in the user profile.

Create a Dynamic Condition

To create a dynamic condition you will need to do the following.

  1. Begin creating a condition as outlined earlier
  2. Tick the Dynamic option when creating a condition, you will now notice different options available:
  3. You will need to create a set of codes for each possible value to be used, much the same way you would create an Org Ref Code, by clicking on the Create Dynamic Filter Code link.
  4. You will now need to create a record for each unique code available, in this example, we create a record for each possible currency we have data for. The first value should be the Code, or the value found in the database table. The second value should be a Description, which is what users will see when they can select their preferred option. The third value can be used to provide a custom sort order, sometimes used to ensure the most commonly selected values are listed at the top to save time.
  5. This is what your list of codes will look like complete. Click Save & Close to complete.
  6. You will now need to select your code from the Dynamic Filter Codes list, and select a default value from the list (this is used as the default if the user has not selected a preferred value in their profile). Save.
  7. Your condition will now display like the one shown here

 

Defining a User's Value for a Dynamic Condition

Once a Dynamic Condition has been created, a user will be able to select which value they wish to use for it:

  1. Navigate to the User Settings by clicking on your User Name in the main navigation bar, and select Settings
  2. You will now have any available Dynamic Conditions. The example here has Currency (the name of our Dynamic Filter Code) and a drop down list with possible values. Select the appropriate value for the user.
  3. Save.

 

Clear Conditions

To remove or clear the conditions from a table select the table properties and click the Clear link. This will remove all the conditions associated for that table.