DBxtra .NET Version 4.1.6 - Last Updated: January 11, 2010
There are several ways to create a new Query
From database tables or views
From existing DBxtra objects like Queries and Reports
Combining database tables - views with DBxtra Queries and Reports
Load an existing DBxtra object (Query / Report), make some changes to it and save as a new Query (Save As)
The following instructions apply for all these variations.
Select the New Object tab.
Or click New on the Object menu.
DBxtra will insert a new Object Tab.
When you click New on the Object menu at the"Select Connection" window will pop up.
Select the Data connection and double click.
To view all Data connections click the button View All Connections.
Click Cancel if you want to add objects manually.
Tables/Views: Select the database tables / views and double click to add it to the Query.
MS SQL Server Stored Procedures: Select the Stored Procedures double click to add it to the Query.
DBxtra Objects: Select the DBxtra Objects and double click to add it to the Query.
Click Close if you want to add objects manually.
Select the object from the Project Explorer - Connection - Database and drag & drop it to the Diagram Panel.
You can right click an object and select Add Object.
To add Tables/Views click the Add Tables/Views on the menu bar.
Select a table and click Add.
To add DBxtra Objects click the Add DBxtra Objects on the menu bar.
Select a DBxtra Object and click Add.
DBxtra will attempt to create a relationship between objects automatically.
By default it will establish the relationship as INNER join.
To add a relationship join drag and drop the field to join from one object to the other object field.
To edit the relationship settings Double Click the join line.
To set the join to a LEFT or RIGHT join edit the proper check box.
A INNER join will be presented like this:
A LEFT join will be presented like this:
A RIGHT join will be presented like this:
You can also set an operator for the join.
Select fields to add to the Query by clicking the check box.
You can also add a field by selecting the object in the "Table" column and the Field in the "Column".
Add expressions by either "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.
If your Query is built from more then one database then the MS Access SQL syntax is required.
When finished click Ok and the expression will be entered to the Query.
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 Date Function select the field Criteria and double click it.
Select the Operator and double click the Date Function from Values.
Click Ok.
Every time the Query or Report runs then it will filter data which applies to the defined Date Filter.
Note! Queries with DBxtra Date Functions also apply to Reports and Charts.
If you design a Report based on a Query with DBxtra Date Functions then it will act as such.
If you need to convert a date value into a Year, Month, Week or Quarter value DBxtra will automatically insert the correct 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 Expression.
DBxtra will insert the data expression for you.
If you need to convert a field type to another one then you can use the Convert Expression builder.
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 here to learn about User Input Parameters
DBxtra .NET allows you to assign friendly Alias names to fields.
Once you enter an Alias name it will be shown in all Queries and Reports instead of the real field name.
Select the Sort Type column and assign the sort order either Ascending or Descending.
Change the Sort order in the Sort Order column.
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.
Select the field Criteria and double click it.
DBxtra allows to add any criteria 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.
To add a operator double click it.
Click Show Values to load all values contained in the database.
To select a given value double click it.
This will insert the value into the expression.
Click Ok to add the expression to the Query.
Right click inside the Parameter Panel and select Edit.
To select only a certain number of Top Records click the TO" field and enter a value.
Choose between PERCENT and WITH TIES.
Optionally select GROUP BY.
Right click on the Diagram Pane and select Union.
Select New union Sub-Query.
Select the left 
.
Drop the table or Object to the Diagram Pane.
Select fields to be queried.
Drop the table or Object to the Diagram Pane.
Select fields to be queried.
Click on the Union symbol and select the UNION type
Click Save on the menu.
Select the folder where to save the Query.
You can create a new folder by clicking the New Folder button.
The Query will be saved in the new folder.
You can also assign a comment for each saved object.
Assign the Query to an existing user groups with privileges to open the object.
This option allows to open a Web report without logging in.
Sometimes it might be useful to save different variations from a Query or Report.
To save a Report with another name click the Save As button. This will create a new object.
Note! If you save an existing Report object as a new object you have the choice to save only the Query of it and not the Report itself.
To do so check the Save Only the Query option.
Movies
Related
QUICK MENU
Watch
Video