Zoomed Image
USA
800 691 9120
UK
01225 704844

Advanced Query Techniques

xAssets Configuration Guide
Queries and Reports

Advanced Query Techniques

This page covers advanced query features including custom SQL queries, the relationship between queries and transformations, and the calling menus tab.

System and User SQL Queries

System Generated SQL

The System Generated SQL tab is used to view the SQL that would be generated by the query builder. The SQL in this box cannot be edited, and it is only of use to copy the SQL as a starting point to develop a SQL-based query. This is helpful when you need to understand what SQL the query designer has constructed, or when you want to start building a custom User SQL query based on the existing design.

User SQL

The User SQL tab is used to develop queries which are too complex to be automatically built in the xAssets query designer. The query can be a sequence of SQL statements and can include temporary tables. As soon as a user starts to type into this box, they will be prompted to convert the query to a User SQL query. The filter for the query will then be disabled and must be created as part of the User SQL.

Building Columns from User SQL

Before running the SQL query in a results screen, it is necessary to click Auto Generate Query Columns from SQL Fields. This runs the SQL statement and builds the field list in the "Query Designer" tab. The field list can then be edited in the "Query Designer" tab to adjust display names, column widths, and display types.

Converting Back to System SQL

A query can be converted back to being a system-generated SQL query by deleting the entry in the User SQL tab. This will re-enable the standard query filter and column builder.

Transformations Tab

The Transformations tab lists any transformations that use this query as their data source. Check this tab before modifying a query to ensure you do not break dependent transformations.

Click a transformation name to open it for editing.

Calling Menus Tab

The Calling Menus tab shows every menu item in the system that displays this query. This is useful for understanding where a query is used before making changes.

From this tab you can:

  • Click a menu name to edit that menu item
  • Create a new menu item that runs this query
  • Delete an existing menu link to this query

Tip: Always check the Calling Menus tab before deleting or significantly changing a query. If the query is referenced by multiple menus, your changes will affect all of them.

Warning: Converting a system-generated query to User SQL disables the visual filter builder. All filtering must be done within the SQL itself. If you later decide to revert, you can convert back by deleting the User SQL content, but you will lose any custom SQL logic.