Skip to content

Adding Columns and Criteria

Adding Columns and Criteria

Once you have selected a Source, the system will then pull a list of any tables that stem from this Source, and all data fields available within to report on in the 'Columns' drop-down.

The table icon corresponds to a secondary table which stems from the Source, for example in the Members Source, you can also access the 'Nationalities', 'Colleges', 'Schools' and 'Courses' tables. The '#' icon relates to a Column name within that table. The list of broken down level by level according to how it joins on. When adding a field into the report builder, it will be named according to the route taken to reach the field. For example, if you navigated to the 'Nationalities' table from the Members Source and selected the id field, this would be added to the report as 'members.nationalities.id'.

Note - the ID field of the Source table will be added by default as this is the identifying field for the Source, so this does not need to be added as a Column. For example, if you use the 'Transactions' Source, the Transaction ID will automatically pulled through to the report. You may need to add it, however, if the Transaction ID makes up the Criteria for the report, for example if you wanted to pull a report of the totals for specific transactions.

To Add and Delete a Column:

  1. Click on a data field within a table, for example within the top level 'Members' source, you could select '#title'.

  2. Click the 'Add as Column' button to move the field to the 'Columns' section of the report builder.

  3. By adding a field as a Column, this information will be pulled into the report for the end user to see. For example, if you added '#title' to the report along with '#student_id', this would pull a list of all student records and present the user with the students' title and Student ID number.

  4. You can then give the field an 'Alias' which is what it will be displayed as to the user upon submitting. For example, if you added the 'members.nationalities.id' field to the report, you could enter the Alias 'Nationality' which makes more sense to the end user.

  5. Select an 'Operator'. This allows you to customise the field further and can be used to give a count, minimum, maximum, average or sum. If nothing is selected here, the fields will be pulled as normal.

  6. Select a 'Format'. This is useful if the field you have chosen contains dates or prices as it converts the data to be pulled in its most suitable format. For example, a timestamp can be converted to the DD/MM/YYYY format. Again, if nothing is selected here the data will be pulled as normal.

  7. A Column can be deleted by pressing the red 'Delete' button on the right-hand side.

To Add and Delete Criteria:

  1. Click on a data field within a table.

  2. Click the 'Add as Criteria' button to move the field to the 'Criteria' section of the report builder.

  3. By adding fields as Criteria, they will act as the conditions for which data is pulled into the report. Once a Criteria is added, you can select one of the 'Operators':

    • Equals: This is used for a single value. Using the field example of '#title', you could select 'Equals' and enter 'Miss' as the Value.
    • Not Equal: This acts as the opposite of the 'Equals' operator.
    • In: This is used for multiple values. For example, you could select 'In' and enter 'Miss','Mrs''Mr'. Note that Values must be separated by a comma and no space.
    • Not In: This acts as the opposite of the 'In' operator.
    • Less Than: This can be used to pull values that are less than something, for example a time or an ID number.
    • Greather Than: This can be used to pull values that are more than something, for example a time or an ID number.
    • Starts With: This will return data that begins with a certain character. Note - This is case sensitive.
    • Ends With: This will return data that ends with a certain character. Note - This is case sensitive.
    • Contains: This will return data that contains a string of characters. For example if you have a product for the Cloakroom, you could enter a criteria to pull all products with a name containing the word 'Cloakroom'.
    • In Database: This is explained in a separate part of the guide.
    • Not In Database: This is explained in a separate part of the guide.

In order to submit a report, one of the Columns you have added must be selected as the 'Order By'. This determines how you wish the data to be organised.

Updated on 18th December 2019