Dynamic SQL

• Dynamic SQL
  • Using Groovy
    • Accessing parameter values
    • Examples
  • Using XML tags
    • Operators
    • Examples

Dynamic SQL

It is possible to create dynamic SQL queries, allowing you to modify the structure of the query based on user parameters, possibly running different queries depending on the user input.

Using Groovy

You can use groovy in the SQL source section to achieve dynamic sql. Set the Use Groovy field of the report to specify that you are using groovy in the sql source.

The groovy script may return a string that has the sql to be executed or it may return a java.util.List of Map<String, Object> or GroovyRowResult objects that contain the data to be output. If using a Map it would be better to use a LinkedHashMap in order to have predictable order of columns. If returning a List of objects, the following options can be specified in the Options field of the report.

Property	Data Type	Description
columns	String[]	Names of columns that should be included in the output. This is optional and may be useful to specify the order of columns to be displayed.
columnDataTypes	Object[]	Each object specifies a column name and the data type of that column e.g. [{"col1": "numeric"}]. The possible data types are string, numeric, date and datetime. The default is string.
columnLabels	Object[]	Each object specifies a column name and the heading to be used for that column e.g. [{"col1": "Column 1"}]. If not specified, the column name is used as the heading.

The groovy code is run through a sandbox and depending on the code in the script, you may need to specify additional classes in the WEB-INF\groovy-whitelist.txt file. If you get a security exception when running the report, add the indicated class to the groovy whitelist file and run the report again. You can also disable the sandbox completely by modifying the WEB-INF\art-custom-settings.json file and setting the enableGroovySandbox property to false. Modifications of the art-custom-settings.json file require clearing the Custom Settings cache in order to take effect.

Accessing parameter values

To access parameter values in groovy code, use the name of the parameter and call the value method e.g. param1.value. For File parameters, use the parameter name directly instead of calling the value method. Also, file parameter values are always passed as a list so access the parameter variable using list syntax e.g. fileParam1[0]. If the file parameter has Multiple Files enabled, this list may have more than one value.

Examples

Integer parameter

def sql

//assumes the report has an integer parameter named id
if(id.value == 0) {
    sql = 'select id from users'
} else {
    sql = 'select name from users'
}

File parameter

//assumes the report has a file parameter named importFile
if (importFile == null) {
    println('report run from url or file parameter not available')
} else if (importFile[0].size == 0) {
    println('empty file or no file selected')
} else {
    InputStream inputStream = importFile[0].getInputStream();
    Scanner sc = null;

    try {
    sc = new Scanner(inputStream, "UTF-8");
        while (sc.hasNextLine()) {
            String line = sc.nextLine();
            println(line);
        }
    } finally {
        if (inputStream != null) {
            inputStream.close();
        }
        if (sc != null) {
            sc.close();
        }
    }
}

The demo database contains some sample reports that use groovy in the sql source to execute different queries based on parameter input.

Using XML tags

You can use xml tags in the SQL source section to achieve dynamic sql. The syntax is as follows. Tag names are case sensitive.

    <IF>
    <EXP1>value1</EXP1> <OP>operator</OP> <EXP2>value2</EXP2>
    <TEXT> ... </TEXT>
    <ELSETEXT> ... </ELSETEXT>
    </IF>

ART parses the query and leaves only the text in the <TEXT> tag if the condition (value1 operator value2) is true or the text in the <ELSETEXT> tag if the condition is false. The EXP1 or EXP2 contents can be static values, single-value parameters or :tags, while the OP content is an operator (see supported list below).

For example in the following query:

    SELECT *
    FROM <IF><EXP1>#level#</EXP1><OP>equals</OP><EXP2>summary</EXP2>
    <TEXT> orders_summary </TEXT>
    <ELSETEXT> orders_details </ELSETEXT>
    </IF>
    WHERE VENDOR like #vendor#
    <IF><EXP1>#date#</EXP1><OP>is not null</OP>
    <TEXT> AND order_date > #date# </TEXT>
    </IF>

if the #level# parameter is set to "summary" and the #date# parameter is not null, ART rewrites the query as:

    SELECT *
    FROM orders_summary
    WHERE VENDOR like #vendor#
    AND order_date > #date#

if the #level# parameter is not set to "summary" and the #date# is null, ART rewrites the query as:

    SELECT *
    FROM orders_details
    WHERE VENDOR like #vendor#

Operators

Operator	Description
eq or equals	equals (case insensitive)
neq or not equals	not equals (case insensitive)
ln	less than (numbers)
gn	great than (numbers)
la	less than (alphabets) (case insensitive)
ga	great than (alphabets) (case insensitive)
is blank or is null	returns true if EXP1 is blank (EXP1 can never be the null object)
is not blank or is not null	returns true if EXP1 is not blank (EXP1 can never be the null object)
starts with	returns true if EXP1 string begins with EXP2 (case insensitive)
ends with	returns true if EXP1 string ends with EXP2 (case insensitive)
contains	returns true if EXP1 string contains EXP2 string within it (case insensitive)
eq cs or equals cs	equals (case sensitive)
neq cs or not equals cs	not equals (case sensitive)
la cs	less than (alphabets) (case sensitive)
ga cs	great than (alphabets) (case sensitive)
starts with cs	returns true if EXP1 string begins with EXP2 (case sensitive)
ends with cs	returns true if EXP1 string ends with EXP2 (case sensitive)
contains cs	returns true if EXP1 string contains EXP2 string within it (case sensitive)

Examples

Dynamic OR/AND selection:

    SELECT *
    FROM orders_summary
    WHERE VENDOR like #vendor#
    <IF><EXP1>#logic#</EXP1><OP>equals</OP><EXP2>OR</EXP2>
    <TEXT> OR country = #country# </TEXT>
    <ELSETEXT> AND country = #country# </ELSETEXT>
    </IF>

Dynamic ORDER BY: The order by part is driven by the user input

    SELECT *
    FROM orders_summary
    WHERE VENDOR like #vendor#
    <IF><EXP1>#sortby#</EXP1><OP>equals</OP><EXP2>Vendor</EXP2>
    <TEXT> ORDER BY 1 </TEXT>
    <ELSETEXT> ORDER BY 2 </ELSETEXT>
    </IF>

Dynamic query selection: A different query is executed depending on user input

    <IF><EXP1>#showaddr#</EXP1><OP>equals</OP><EXP2>Y</EXP2>
    <TEXT>
    SELECT name, code, address, phone FROM VENDOR
    WHERE VENDOR like #vendor#
    </TEXT>
    <ELSETEXT>
    SELECT name, code, vat, pay_term FROM VENDOR
    WHERE VENDOR like #vendor#
    </ELSETEXT>
    </IF>

Dynamic SQL with tags: The condition is verified only if the date supplied by the user (#date#) is earlier than the system date at report execution time (:DATE tag)

    SELECT *
    FROM orders_summary
    WHERE VENDOR like #vendor#
    <IF><EXP1>#date#</EXP1><OP>la</OP><EXP2>:DATE</EXP2>
    <TEXT> AND order_date > #date# </TEXT>
    </IF>

Check user input: Show a warning instead of executing the query

    <IF><EXP1>#value#</EXP1><OP>ln</OP><EXP2>10000</EXP2>
    <TEXT> SELECT ... </TEXT>
    <ELSETEXT> SELECT 'Value too High' "Warning" </ELSETEXT>
    </IF>

    the select statement to use to show the warning depends on the DBMS (for example in
    Oracle you should use SELECT 'Value too High' "Warning" FROM DUAL)

Dynamic WHERE condition: E.g. to drill down on null values

    SELECT * FROM customers
    WHERE 
    <IF><EXP1>#email#</EXP1><OP>equals</OP><EXP2>null</EXP2>
    <TEXT>customer_email is null</TEXT>
    <ELSETEXT>customer_email=#email#</ELSETEXT>
    </IF>