Variable Substitutions

Platform uses a $var$ convention to perform variable substitution, binding user-supplied (and sometimes other) values to these symbols at runtime. If you wish to use literal '$'s in your SqlDef, you can escape them using '\', e.g.: '\$ITEM_NAME\$' or '\$10.00'. The '\' character itself is not escaped, so if you wish to have a literal '\' before a '$', use SQL concatenation, e.g.: '\' || $ITEM_NAME$. In general, you will ask the user to supply a value for a column, and then substitute the users value in the WHERE clause. To do this, you need only put the variable $COLUMN_NAME$ in the SqlDef (for example, $ITEM_NAME$). The same convention is used when we may ask the user for a value that does not precisely map to a database table column. For example, when we want the user to be able to choose on which column to sort the results. You can replace the original SqlDef "order by" clause with the following: order by decode($REPORT_SORT$, 'ItemName', item.item_name, Description, item.description)

Variable Substitution for User Defined Fields(UDFs)

In Platform, UDFs are stored in generically named columns such as NUM_UDF1 and STRING_UDF10. To improve the readability and maintainability of the Custom Report SqlDef, you can use a variable to represent a UDF column.

The variable takes the form UDF:Level.Custom Model Name.UDF Name.

For example, given a UDF called ExtraInfo for the model Book in the Bookstore module, with Standard Book as the custom model name:

select item.item_name, item.description, item. ${UDF:ZBKS.Book.Standard Book.ExtraInfo}

Because UDF Dates are stored as milliseconds in NUM_UDF columns, you must call a function to convert an incoming Date filter parameter into milliseconds before comparing it with a database value. For example:

${UDF:ZBKS.Book.Standard Book.NextPromotionDate} < dtb.time_in_millis_from_timestamp($PROMOTED_AFTER$)