Reports

The Reports screen allowes you to create reports. You can save report definitions or resulting queries

You can switch to another Connection and/or TableSet on this screen. A report can only use tables from 1 table set at the time.

You can select a saved report from the dropdown list. This list will contain all previously saved reports that have been made using the same connection and table set.
Enter a name for your report and click on “Add/Update Report” to save it or save any changes you have made.
select a report and click on “Delete Report” to delete it.

The column “Available Fields” lists all fields from all tables in the table set. If you add a field to the Selected Fields”, The table will be automatically added to “Selected Tables” as well. For every field added to “Selected Fields” a row of options is added.

  • Show: Show this field in the output which means it will be in the SELECT part of the query.
  • Show Mode: SUM, MIN, MAX, AVG or COUNT; If one (or more) of your fields uses this option, other fields will automatically be added to a GROUP BY clause. Leave blank for normal select.
  • Sort: Defines the ORDER BY clause ASC or DESC. If Sort Order is defined, but no Sort is selected, ASC will be the default.
  • Order: Defines in what order the fields should be sorted. 1 for the first, 2 for the second etc. If no sort order is given, the option “Sort” is ignored.
  • Filter: Determines which fields will be in the WHERE clause (normal checkmark) or in the HAVING clause (greyed checkmark). The HAVING clause will use the Show Mode, if one is selected for this field.
  • Filter Mode: AND (NOT) or OR (NOT). An OR mode will always be grouped with the AND statement that precedes it. Arrange the order of your fields on the screen with the Up and Down buttons to group them as intended.
  • Filter Type: =; <>; <; <=; >=; >; LIKE; NOT LIKE; IS; IS NOT; IN; NOT IN. 
    This selection, together with the field type, also determines if quotes need to be used as delimiters.
  • Filter Text: The actual text to match to a field value. Use 1 for true or 0 for false when dealing with Boolean (BIT) values.

 Selected Tables

  • Use: Use this relation for the table in the FROM clause.
  • Source Field: The field that is the left side of the JOIN
  • Relation: The field that is the right side of the JOIN. Write this as [schema name].[table name].[Field name].
  • Join Type:
    • INNER: The value must exist in both tables to be shown.
    • LEFT OUTER: The value only needs to exist in the Source table to be shown.
    • RIGHT OUTER: The value only needs to exist in the Related table to be shown.
    • FULL OUTER: The value may exist in either of the tables to be shown.
    • CROSS: All values from both tables are joined resulting in may possible combinations.

Other options

  • Use Distinct: A DISTINCT clause will be added to the query, causing duplicate values to be filtered out.
  • Use TOP (number): A TOP <number> clause will be added to the query, limiting the result set to a maximum number or rows.

Create Report will show the query on the Result tab and immediately run the report and show the results, if any.
Show Query will show the resulting query on the Result tab, but not run the query.
Clear All will remove all selections, leaving you a blank report.
Revert Changes will reload your saved report definition and undo any changes you have made since the last time you saved your report.