Macros

Macros are used to define reusable pre-processing instructions for generating SQL queries, thus optimizing the performance of SQL statements with optional filter fields. They extend the features of Custom Reports beyond pure SQLs.

For example, let's say that when a user gives you a NAME parameter, you want to filter EMPLOYEE by NAME. However, if no parameter is given, you don't want to filter at all. Using simple SQL with parameter bindings, this can be achieved as follows:

SELECT NAME FROM EMPLOYEE WHERE $NAME$ IS NULL OR NAME LIKE $NAME$

However, there are times when this strategy results in poor performance, particularly in a query which includes many different filters, inner queries, etc. (The database may wind up making comparisons on a row-by-row basis rather than optimizing out the portion of the query which is known at parameter-bind time.) An alternate approach is to use a macro to generate a more optimal query before it is ever handed off to the database:

SELECT NAME FROM EMPLOYEE WHERE ${filterIfNotNull:NAME,NAME LIKE $NAME$}

The syntax of a macro is of the form:

${macro}

Review the javadocs for com.onenetwork.platform.data.sql.SqlService for a complete list of macros.