Using Advanced Filtering and VDM Expressions in a View

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

Explain how to use the Advanced Filtering feature in VDM to manually write SQL WHERE clauses and incorporate filters on VDM Expressions using special syntax.

What Is Advanced Filtering?

Advanced Filtering in VDM gives users full control over their filter logic by allowing them to directly script the SQL WHERE clause. This method bypasses the standard filter builder and is ideal for users comfortable with SQL or those needing precise control over data retrieval.

Tip: Advanced Filtering is ideal when standard filter options don’t support complex logic or expression evaluation.

Why Use Advanced Filtering?

• Enables full scripting of SQL filter logic

• Supports complex compound conditions

• Allows filtering on VDM Expressions, which standard filters can’t handle directly

• Empowers technical users to fine-tune data extraction

Steps to Use Advanced Filtering

Step 1: Enable Advanced Filtering

• Open VDM.

• Open an existing or create a new View.

• Select FSGS under Query Builder.

• Click the Adv Filtering button on the toolbar.

Step 2: Write Your Filter Statement

Type your SQL WHERE clause directly into the Advanced Filtering window.

Example:
 (dbo.Orders.EmployeeID IN ('1','2','3') And
 dbo.Orders.ShipCity LIKE 'L%' And
 dbo.Orders.ShippedDate IS NOT NULL)

Filtering with VDM Expressions

Expressions in VDM are not part of the actual SQL structure and must be handled after the main SQL query runs. To include expressions in advanced filters:

Use # to Wrap Expression Conditions

Enclose expression-based conditions in # to tell VDM to apply them after the SQL data is returned.

Important Note: Expression filters inside # are not sent to the SQL server. Instead, they are processed client-side after the initial result set is returned.

Syntax Example:

TRANS.Transaction_Code IN (1,2,351,361,411,413,422,424) AND TRANS.Client_ID = 'CLIENT1' #AND @VDMEX.TestAmount1 >= 200 AND @VDMEX.TestAmount2 <= 400#

How It Works Internally

• Sent to Server:
  TRANS.Transaction_Code IN (...) AND TRANS.Client_ID = 'CLIENT1'

• Client-Side Expression Filter:
  @VDMEX.TestAmount1 >= 200 AND @VDMEX.TestAmount2 <= 400

Tip: Expression fields must be referenced by their full expression name, prefixed with @VDMEX. and wrapped in #.

Article Summary

Advanced Filtering in VDM offers unmatched flexibility for crafting complex, SQL-style filters. When using expressions, wrap conditions in # so that VDM can apply them after the SQL execution. This hybrid server/client filtering model allows users to handle advanced logic that isn't supported through basic filter menus.

Use Case: You need to filter for transactions by type and client on the SQL server and apply client-side rules on calculated expression fields—Advanced Filtering makes this possible.