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. 

Advanced Authoring

In the Create lightbox, there will be an Advanced Authoring option available to you if you have the correct permissions. You will need to enable this option in order to use any of the advanced report creation methods, rather than the standard Drag & Drop Builder.

From here, you will need to select the Freehand SQL option from the Authoring Methods drop down.

Finally, you will need to select the view you wish to use and proceed to the Freehand SQL builder by clicking on the Create Analysis button.

Freehand SQL

You will now be taken to the SQL step of the report builder. Here you will see a space to enter your SQL query on the right of the page.

The left side panel will display a list of tables and fields made available through your selected view.

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 button. 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. To link a filter parameter to an access filter, navigate to the Data tab. In the filter list, you will see the filter parameter that you added, for example:
   
   

   Click on the Advanced settings link.

   In the popup, click on User Prompt next to the filter that you want to link.
   
   Now select the Access Filter radio button option, and select the appropriate Access Filter from the drop down list: