If a requirements exists for a specific report, and no view of the database exists then an SQL query may be written to access the data directly. To achieve this select Freehand SQL option when prompted for the builder you wish to use.
The list of database tables assists you to write your SQL. By expanding the node next to a table name you display the fields for the table. The list also provides an indication if the element selected is a table or a database view.
You can test your SQL statement by clicking the Validate link. If the SQL statement fails you will receive a message back that may contain a description of the error (this is database dependent)
If the SQL is successful the following message will be displayed:
Once you are happy with your SQL click the next icon to go to step 2 in the report builder wizard. From here you will have the report data page similar to a standard drag and drop report.
You can add charts, drill through and co-display reports. Some features such as filters and aggregations are not available since it is assumed that this will be contained within your freehand SQL.
To include user prompts or source filters into a SQL you will need to add script into your SQL query to indicate which columns to use.
select * from client where country = {?
}select * from client where country in ({?})
(Note the additional Brackets)