Trouble Viewing Images? Right-click on any image and select "Open in new tab" to view a larger version. You can also zoom in using Ctrl + Mouse Wheel for easier readability.
Article Goal
Learn how to create and manage Custom Database Functions (DBFs) in VDM Views using SQL syntax that is passed directly to the database for processing.
What Are Custom Database Functions?
Custom Database Functions—also referred to as DBFs—allow you to write database-specific SQL logic such as:
Mathematical computations
Concatenations
Date manipulations
String operations
These functions are inserted into Views and executed directly by the database engine (e.g., SQL Server, Oracle, Cache).
Note: Function syntax depends on your database provider. Always use the correct syntax for your environment (e.g., DATEADD for SQL Server).
For Cache-specific ODBC functions, refer to the Cache function documentation (link to be inserted).
Video Tutorial:
Download Sample View:
Click here to download the sample view and follow along.
Steps to Add a Custom Database Function or Field
Step 1: Open the Tables and Fields Tab
- Right-click inside the Fields area
Select Add Custom Field or Function
Step 2: Enter Your SQL Function
In the input box, type your custom SQL logic
Click OK to add it to your View
Tip: Custom fields added this way will be prefixed with @DBF. in the field list.
Step 3: Edit or Update the DBF
Double-click the field under
@DBFor in the fields section to modify it
Important:
To save your custom DBF locally, click Save / Update Locally when adding or editing the function. This ensures it's preserved for reuse.
Common Use Case Examples
Example 1: Building a Custom Date from Separate Fields
Scenario:
You have month, day, and year stored in separate fields and want to combine them into a proper date format.
Field Sources:
ARCOSTATS.ARCOSMO= Month (1–12)ARCOSTATS.ARCOSDAY= Day (1–31)ARCOSTATS.ARCOSYR= Year
Database Function:
Example 2: DATEADD — Calculate 7 Days Ago
Database Function:
Purpose: Returns today’s date minus 7 days.
Example 3: Concatenating Date Parts
Database Function:
Purpose: Outputs the first day of the month based on an order date (e.g., 3/1/2025).
Article Summary
Custom Database Functions (DBFs) in VDM allow users to directly write database-native SQL logic within their Views. These functions are evaluated on the server, making them powerful tools for manipulating data at runtime. Whether combining fields into a date or using built-in functions like DATEADD or CONVERT, DBFs unlock flexible, advanced reporting capabilities.
Use Case: Build custom dates, calculations, or text formats at the database level and use them seamlessly in reports—without needing to change the source schema.
Comments
4 comments
There are no examples of of the content that might be used in the filter. I have tried to copy simple SQL statements and this fails every time. For example, maybe I want the year from @LASTMONTH. I would assume it to be SELECT DATEPART(year, @LASTMONTH)
but that doesn't seem to work. Examples would help.
Hello Jeremy,
Database Functions code is sent directly to the database to evaluate. @LastMonth is not a valid expression to send to a SQL or Cache database, @LastMonth is a reserved word for VDM used specifically for filtering. To get the results from last month your database function would return a date value. You'd then apply a filter for the database function where it is equal to @LastMonth.
I agree - having documentation to reference re: commonly used syntax would be very handy. (Cache).
Hello Robert,
Jeremy's question was related to Reserved Words and Special Data Filtering Options. These are processed locally and then sent to the database as actual dates. Additional documentation and examples of Special Date Filter Options is available here. https://support.bridgeworksllc.com/hc/en-us/articles/200375239-Special-Date-Filtering-Options.
As for supported Cache specific ODBC functions. Please check out the link below on InterSystems website. 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).
https://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_FUNCTIONS
Please sign in to leave a comment.