Like what you see? Have a play with our trial version.

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Migration of unmigrated content due to installation of a new plugin

Anchor
top
top

Table of Contents
classcontents

Overview

Styleclass
ClasstopLink

top

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 option when prompted for the builder you wish to use.

Create a Freehand SQL Report

Styleclass
ClasstopLink

top

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.

 

horizontalrule
Styleclass
ClasstopLink

top