Freehand SQL Reports

Overview

To write freehand SQL reports you will need to be granted specific user access. SQL reports require knowledge of 'Structured Query Language' and a sound knowledge of the data source that you wish to query.

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' when prompted for the report writing mechanism you wish to use.

Create a Freehand SQL Report

In select the SQL version all filters and conditions will have to be contained within the SQL. Type in your SQL statement into the text box provided.

Database Table List

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.

Validating SQL

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:

Formatting your SQL Report

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.

User Prompts & Source Filters

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.

1. The inclusion of user prompt or source filter in your SQL reports is done by adding a {?} parameter.
   For example: select * from client where country = {?}
   If inserting a source filter with multiple possible input parameters use: select * from client where country in ({?}) (Note the additional Brackets)
2. Clicking on "Validate" will inform you if the SQL syntax is valid, as well as the number of parameters present.
3. The remaining steps from now on will be similar to generating a standard report with drag and drop, except that all of your SQL user prompt parameters go straight into the "Filters" stage of the report builder.
4. At the "Report Filter" page, you will be able to set whether the filter is a user prompt or source filter.