Additionally to database objects like tables ot stored procedures you can add an SQL statement to the report. It will be sent to the database with a WHERE 1=0 suffixed to it's end, and the columns received back by the database are the columns put into the resulting command table.

If you would like to manually enter an SQL select statement and use it as a table in your report, double-click on node "Command".

This will open up an SQL Editor window, where you can manually enter an SQL statement. If you want certain database fields that are in your report to be in your statement, you can double-click on them in the left part of the editor, and they will dynamically be added to your statement. This SQL statement will be sent to your database with a "WHERE 1=0" suffixed to its back, and the columns received back by the database are the columns put into the resulting command table.

Once you have created an SQL command, if you would like to edit its SQL statement, simply right click on the table of the SQL command and select the option "Edit SQL..." from the table's drop down menu. This will open up the SQL Editor and you can change the SQL statement at will.

It is now possible to convert all table sources in a single connection into one SQL command. This can simplify your report immensely, and allow for more complicated and efficient report design. To convert tables from a connection into a command, select a connection and click on the "To SQL" button at the bottom of the Wizard dialog. If you should happen to have more than one connection in your report, you will be asked to choose which connection you want to perform the conversion on.

This will open an SQL Editor box with the current SQL statement needed for this connection. You can either change this statement manually or simply accept it.

Either way, this statement will now be created as a command table source, and all database fields in your report will now refer to a column in this command.

Once you have created your report template, you can use the Database Wizard to see the actual SQL statement used by i-net Clear Reports to fetch the data for your report. To do this, unselect any SQL commands you may have created, and simply click on the "Show SQL" button at the bottom of the dialog. This will show you the SQL statement in a copyable window, enabling you to copy and paste the statement for your own use. Note that this window will be empty if you are not using any fields at all in your report, since this means you are not selecting any records.

• Ctrl. + Space - this will pop up a menu to auto-complete any SQL keywords you are typing.
• Ctrl. + Shift + F - this will auto-format the query, placing major keywords such as SELECT or FROM in new lines, and indenting lines in between the keywords.
• Ctrl. + Shift + L - this will toggle visibility of line numbers in the editor.
• Ctrl. + L - this opens up a dialog for navigating directly to a certain line number.
• Ctrl. + Z / Ctrl. + Y - Undo / Redo functionality.