Query

The DBxtra Query is the core of all DBxtra objects.

No SQL or programming knowledge is required to design Queries.

The first step to design a Report Object like a Data Grid, Report, Chart or Pivot Grid is building the Query.

You can create Queries from database objects like tables and views and also from existing DBxtra Report Objects.

You can also create a new Query from combining database tables / views and existing DBxtra Report Objects.

Create a new Query

Select the Project.

Create a new Report Object.

To build the Query for the Report Objects click "Click here to create the Query".

The Query Builder window will open.

Query Builder Interface

Query Builder Panes

Object Path

The "Object path" shows the bread-crumb structure of the Report Object location.

Open Connection

Shows the belonging Data Connection of the Report Object.

Diagram Pane

The Diagram pane allows to visually select tables / views and its fields to be included in the Query.

Grid Pane

The Grid pane allows to select fields, assign expressions, sort orders, aggregate functions and apply criteria and parameters.

SQL Pane

The SQL pane allows to edit the SQL syntax manually.

Important! When working with complex queries and sub-queries, synchronization between panes might slow down your work . Therefore it is recommended to turn the SQL pane off. To turn the SQL pane off, unselect "View" and "SQL Pane" on the menu bar.

Query Synchronization

DBxtra allows full synchronization between Diagram, Grid and SQL panes.

Once you change any setting in any pane all other panes will synchronize automatically.

Tip! You can also write an SQL syntax manually or paste an existing SQL code and the Diagram and the Grid pane will synchronize.

Add Tables / Views and Report Objects to the Query

To add database tables / views click "Add Tables/Views" on the menu bar.

To add Report Objects click "Add Report Objects" on the menu bar.

When finished adding all required tables / views / Report objects click the "Close" button.

Sub-Query

When adding an existing DBxtra Report Object to the Query, the Sub-Query will be displayed in another tab.

Linked / Unlinked Sub-Query

By default a Sub-Query is linked to the original Report Object's underlying Query.

That means if you edit the original Query its changes will be reflected at all Queries which use it.

Edit Sub-Query

If you change the structure of the Sub-Query it will become "unlinked".

Note! When a Sub-Query becomes unlinked and the original Query changes it will not inherit those changes.

Joins - Relationships

Automatics Joins

DBxtra will add relationship joins automatically if detected in your database schema.

You can edit the relationship joins or add them manually.

Create Joins manually

Select the field to join from one table and drag it to the field to join of the another table.

This will create an INNER join.

Edit Joins

Double click the join line.

Choose between LEFT, RIGHT and INNER joins.

A INNER join will be presented like this:

A LEFT join will be presented like this:

A RIGHT join will be presented like this:

Remove Joins

To remove a join select the join line, right click and select "Remove".

Learn more about relationship joins.

Add fields

Select from Table - Diagram Pane

Add fields to the Query by selecting them from the table object.

Select from Table - Grid Pane

You can also select fields from within the Diagram pane.

Select the object name.

Then select the field to be included.

Alias names

DBxtra allows to assign friendly alias names to field names.

Note! Sometimes database fields have strange names and are hard to be identified by end-users.

Once an alias name is assigned it will be shown in all Report Objects instead of the real field name.

Expressions - Formulas

Add expressions - formulas by double clicking the "Table" column or by selecting "<Expression>" in the "Table" column.

You can enter an expression either manually or by double clicking the appropriate field and operator.

Note! DBxtra will allow to enter any expression supported by the database you are connected to.

Important! If your Query is built from more then one database then the MS Access SQL syntax is required.

Click "Ok" and the expression will be added.

Date Functions

DBxtra offers many preset date functions you can use.

Example! If you need to filter your Query or Report data from only "Last Month", entering a date function might be useful.

Note! Date functions can only be applied to "date" type fields.

To define a filter based on a DBxtra a date function select the field "Criteria" and double click it.

Double click the "Date Function" from the "Values" box.

Click "Ok".

Every time the Query; Data Grid, Pivot Grid or Report runs it will filter data which applies to the defined date filter.

Convert Field Type Expression

If you need to convert a field type to another one then you can use the "Convert Expression" builder.

Note! This functionality only applies to connections to MS SQL Server, MS Access, MySQL and Oracle databases.

Add a new expression.

Select the table and field.

Click "Convert Expression".

Select a field type to convert the expression to.

Click "Ok" to insert the expression.

Date Expression

If you need to convert a date value into a Year, Month, Week or Quarter value, DBxtra will automatically insert the required expression for you.

Note! This only applies to database connections to MS SQL Server, MySQL, Oracle and MS Access.

Add a new expression.

Select the table / view and field which holds the date value and click the "Expression - Date Function" button.

Select a "Date Function" and click "Ok".

Sort Orders

Add Sort order

Select the "Sort Type" column and assign the sort order either to "Ascending" or "Descending".

Change the sort order in the "Sort Order" column.

Clear Sort order

To clear a Sort order select the blank option on the "Sort Type" column.

Aggregate Functions

Group data and add aggregate functions by selecting it from the "Group By" drop down field.

Change any other "Group By" aggregate function by selecting it from the drop down field.

Criterias - Filters

DBxtra allows to add any criteria - filter supported by the database you are connected to.

Note: If your Query is built from more then one database then the MS Access syntax is required.

Select the field "Criteria" and double click it.

To add an "Operator" double click it.

You can enter values manually or use the "Values" functionality.

Click "Show Values" to load all values contained in the database.

To select a given value double click it.

Click "Ok" to add the expression to the Query.

Date Filter Function

DBxtra offers many preset "Date Functions" you can use.

Example! If you need to filter your Query data from only "Last Month", entering a date function might be useful.

Note! Date functions can only be applied to "date" type fields.

To define a filter based on a DBxtra date function select the field "Criteria" and double click it.

User and User Group Variables

Login Parameter

User Input Parameters

Define a Query as a parameter Query where the user will be asked to enter parameters each time the Data Grid, Pivot Grid or Report is opened.
You can assign unlimited parameters.

Select the field "Criteria/Parameter" and double click it.

Single select Parameter

Click the "Insert Parameter" button.

Multiple select (IN) Parameter

Click the "Insert Multiple Values Parameter" button.

Customize Parameter

To customize a parameter click the "Customize Parameter" button.

Alias name

Enter an "Alias" name to be displayed in the input field instead of the field name.

Default Value

Select a "Default Value".

You can either manually assign a "Default Value" or click the "List Values" button to choose from.

If you click on the "List Values" button then a new window will pop up.

You can select any given value from that window.

Option Show Values

Click "Option Show Values" and click the button "Configure List Value"

You can edit the underlying Query for the default values just as you would do in the Query builder.

Note! For example you could alter the sort order for values in the parameter window or assign a new filter for the parameter.

Option <All Values>

Select the option "<All Values>" check box if you want to show the "All Values" option in the parameter window.

Password Type

If you select "Password Type" then DBxtra will treat the field as such and will not the show the entered parameter value.

Add derived table

See example: Sales - Month - Last Year vs. Current Year

User – User Group Variables

Please read here about this feature

Query Properties

Click the "Properties" button.

To select only a certain number of top records click the "TOP" field and enter a value.

Assign "DISTINCT" or "DISTNCTROW" option.

Union Query

To create a union Query right click on the Diagram pane and select "Union".

Select "New union sub-query".

Select the left "Q".
.

Drop the table / view or Report Object to the Diagram pane.

Select fields to be queried.

Drop the table / view or Report Object to the Diagram pane.

Select fields to be queried.

Click on the "Union" symbol and select the union type.

Heterogeneous Query

DBxtra allows creating Queries form more than one Data Source and also from different databases.

For example you can create a Query mixing data from databases like MS SQL Server and Oracle.

Proceed as usual creating a Query.

Add tables / fields form one database and then from another database.
DBxtra will automatically detect and build the heterogeneous Query.

Important! When creating a Query from more than one database then you need to apply the MS Access SQL syntax.

Save a Query

To save the Query click "Save" on the "Query" menu.

Note! If the Report Object has not been saved yet you need to save it first before saving the Query.
DBxtra will ask you to save the Report Object first.