Referencing Grid Level Expressions in Advanced Queries

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 Grid Level Expressions with Advanced Query in VDM, including setup steps and important limitations.

What Are Grid Level Expressions?

Grid Level Expressions are calculations created within the VDM Data Grid after query execution. These are processed client-side, meaning the database returns the base dataset, and VDM adds the expressions afterward.

Tip: Expressions are typically used for calculated columns like differences between fields, percent changes, or custom formatting that aren't handled by SQL alone.

Why Use (or Avoid) Grid Level Expressions in Advanced Queries?

While you can reference expressions inside an Advanced Query result set, there are significant limitations:

Important Note:
Grid expressions are added after the SQL is executed. If you don't explicitly tell VDM where to place them in the result set, the View may not behave as expected.

Best Practice:
If you're using Advanced Query for SQL scripting, we recommend avoiding Grid Level Expressions. Instead, use:

• Calculated Fields in Finished Reports

• Visualize expressions for frontend customization

How to Reference Grid Level Expressions in Advanced Query

Step 1: Create and Run Your Advanced Query

• Write your SQL in the Advanced Query editor

• Run the View to return data

Example SQL:

SELECT Orders.EmployeeID, Orders.CustomerID, Orders.OrderDate, Orders.ShippedDate FROM dbo.Orders Orders

 

Step 2: Create Grid Expressions

• In the Data Grid (after running the View), create your custom expressions

• Ensure the data types are set correctly

Example: A calculation of DATEDIFF between ShippedDate and OrderDate

IIF(ISNULL([Shipped Date]),0,DATEDIFFDAY([Order Date],[Shipped Date]))

Step 3: Add Expressions to the Advanced Query’s Field Selection

• Open the Advanced Query field list

• Add the expression names to the SELECT clause manually

If this step is skipped, VDM won’t know where to place the expressions in the final dataset.

Step 4: Save, Run, and Preview the View

• Save the View after verifying everything works as expected

• Run it again and confirm that:

  • Expressions appear in the Data Grid

  • Values are calculated correctly

  • Output structure is preserved

Article Summary

Grid Level Expressions in VDM can be referenced in Advanced Queries but require additional setup to define their place in the result set. Because Advanced Queries return SQL results before expressions are applied, VDM cannot auto-place expressions unless they are explicitly added to the field list. For most cases, it is recommended to avoid this setup and use Finished Reports or Visualize for post-query calculations instead.

Use Case (Not Recommended): You write a complex Advanced Query and want to calculate profit margins. Instead of using grid-level expressions, calculate the margin in SQL or use a calculated field in your report layout.