Trouble viewing images? Click on images to enlarge.
What is a Custom Database Function?
A custom database function or field is special SQL syntax that your database allows within a SQL select statement. This is commonly used for Mathematical Computations, Concatenations, Substrings etc. Custom Database Functions and Field's syntax are passed directly to the database for evaluation and processing. So any supported function for a database platform is available, and varies by the provider (MS SQL, Oracle etc).
For supported Cache specific ODBC functions, check here.
Adding Custom Database Functions or Fields
Below are the steps for creating a custom Database Function or DBF.
To add a Custom Formula or Function to a View, Right Click on the Report Fields area on the Tables and Fields tab.
Next, type in your Custom Field or Function in the box below and Click OK.
When the Custom Field or Function is added to your View it will have the @DBF prefix in front of the item. To edit the Custom Field or Function, simply double click on it.
Example Stat Date:
This example has date values stored differently that need to be concatenated into a single date value.
ARCOSTATS.ARCOSMO (Months) is stored as a 1-12 value
ARCOSTATS.ARCOSDAY (Days) is stored as a 1-31 value
The following function will concatenate the Month, Day and Year into a mm/dd/yyyy format. It's important to note the added '-' in between each to separate the values being added to the date. In order to use this function you would need to replace the fields in orange with your fields.
This example returns a value that is 7 days ago.
Example DATEADD Output: