--- title: "Virtual Tables" canonical: "https://wiki.yellowfinbi.com/space/yfcurrent/2196622/Virtual%20Tables" format: markdown --- > Macro (anchor) > Macro (toc) ## Overview A virtual table allows you to insert an SQL statement into the view which brings back a set of derived fields and used as a logical table in the view builder. Virtual tables have the following advantages: - **Reducing the amount of data returned in a query.** You can include complex calculations and functions in a virtual table. These operations are performed before the result set is returned to a report, which saves time and reduces report complexity. - **Reducing maintenance of database summary tables.** Virtual tables can, in some cases, replace aggregate tables stored on a database. These aggregate tables are costly to maintain. Derived tables can return the same data and provide real time data analysis. - **Combining data from more than one fact table.** Virtual tables can also be used to avoid circular join references when writing queries that combine data from more than one fact table. The fact tables can be combined in the virtual table definition using a union clause. This will ensure the correct results are returned in your query. ## Create Virtual Table 1. To create a new virtual table drag the virtual table icon from your table list onto the canvas. 2. The virtual table has an additional SQL link, click this to open the SQL popup. You can update the name of the table and insert the SQL to create your derived fields. **Note:** Your SQL cannot contain order by statements as this will cause the reports to fail. For aggregate columns ensure that you define the column name (as statement). This is required by Yellowfin to generate column names. 3. Click the ‘Validate SQL’ Link to test your SQL against your target database. 4. Click Save to save you SQL. This will show you a list of columns returned from your SQL statement. ## Edit Virtual Table SQL To edit the SQL of your virtual table click the edit link on the virtual table summary page. This will open the SQL editor. ![image](media://c690bd19-c3c1-4310-bdde-de31634fc8f3) If you have already attached columns from your Virtual table to your view you will have to be aware of the impact that any changes you make will have. For example you may drop columns or rename ones that are already in use. ## Virtual Table SQL Templating ### Overview Virtual Table SQL Templating lets you embed dynamic logic directly into the SQL of a Virtual Table using Apache Velocity — a widely-used open-source templating engine. At report run-time, Yellowfin resolves the template and sends the resulting SQL to the database, enabling: - Injecting parameter or filter values anywhere in the query, including sub-queries and WHERE clauses. - Conditionally including or excluding whole blocks of SQL based on parameter, filter, or database-type values. - Writing a single view that works correctly against multiple database platforms. This feature was introduced in v9.17.1. The templating engine used internally is Apache Velocity. Full Velocity syntax is available at the [Apache Velocity Engine User Guide](https://velocity.apache.org/engine/1.7/user-guide.html) — however, a small number of directives are disabled in Yellowfin for security reasons (see the [Limitations & Disabled Directives](https://wiki.yellowfinbi.com/space/yfcurrent/2196622/Virtual+Tables#Limitations-&-Disabled-Directives) section). ### Setting Up Variable Names Before you can reference a variable in a Virtual Table query, you must assign a variable name to the corresponding parameter or filter group in the View Builder. Variable names are alphanumeric only — no spaces or special characters are permitted. #### Parameter Name 1. Open the View Builder and navigate to the Prepare tab. 2. Locate the parameter and click Edit Format. 3. Enter a unique value in the Parameter Name field (e.g. SalesRegion). This is the name you will reference in SQL using the $ prefix. #### Filter Group Name 1. On the Prepare tab, locate the filter group and click Edit Format. 2. Select the specific filter within the group that you want to expose as a variable. 3. Enter a unique value in the Filter Name field (e.g. CampRegion). - Variable names must be unique across all parameters and filter groups in a view. If two parameters or filters share the same variable name, an error will occur when the Virtual Table is saved or validated. Duplicate names are flagged at save time. ### Enabling Templating in a Virtual Table To activate the templating engine for a Virtual Table query, the SQL must begin with -- TEMPLATING on the very first line. This is a strict match — any variation in spacing or capitalisation will cause the template to be treated as plain SQL. ### Injecting Variable Values Reference a variable anywhere in the SQL using the $ prefix followed by the variable name defined in Section 2. #### EQUALS (single-value) injection Use this pattern when the parameter or filter holds a single value — for example a text, numeric, date, or timestamp parameter. #### IN LIST (multi-value) injection Use this pattern when the filter holds a list of values. The variable must appear inside parentheses ( ) in the SQL — Yellowfin expands it into a properly-quoted, comma-separated list at run-time. #### Handling unset variables When no value has been entered for a parameter or filter, the variable resolves to NULL. An unguarded IN (NULL) or = NULL will not match any rows. Use an #if check to make the clause optional: ### Conditional Logic Use Velocity directives #if, #elseif, #else, and #end to include or exclude SQL blocks at run-time. #### Basic if / else #### Conditional JOIN You can include entire JOIN clauses conditionally, which is useful for injecting joins only when a particular filter has been set: #### Database-type branching The built-in variable $DB holds the database type of the data source. Use it to write SQL that adapts across platforms. The comparison is case-sensitive. Supported $DB values: `SQL_SERVER` Microsoft SQL Server `POSTGRESQL` PostgreSQL `MYSQL` MySQL `ORACLE` Oracle Database `HSQLDB `HSQLDB  ### Limitations & Disabled Directives For security and integrity reasons, the following Velocity directives are disabled in Yellowfin's templating engine and cannot be used: `#parse()` — includes and parses an external template file `#include() ` — includes the raw content of an external file `#evaluate()` — evaluates a string as a Velocity template at run-time ### Areas That May Be Affected Templating modifies the SQL that is executed against the database at run-time. Any Yellowfin functionality that relies on a Virtual Table to execute SQL may be affected, including: - Loading values for cached filters that reference Virtual Table fields. - Running reports that include columns from a templated Virtual Table. - Applying filters whose values are referenced as variables inside the template. When a filter is applied as a template variable, Yellowfin will use the end-user's prompt value rather than the default value defined on the view. Ensure your SQL handles the case where no value is supplied (see [Handling unset variables](https://wiki.yellowfinbi.com/space/yfcurrent/2196622/Virtual+Tables#Handling-unset-variables) section). ### Tips & Best Practices - Always start your SQL with `-- TEMPLATING` on its own line — extra whitespace or different capitalisation will disable the engine. - Keep variable names short, descriptive, and alphanumeric (e.g. `SalesRegion, DateFrom`). Spaces and symbols are not allowed. - Never create two parameters or filter groups with the same variable name in the same view — a duplicate will cause an error. - Guard multi-value IN LIST clauses with a null check (`#if($Var != "NULL"`)) so that unset filters return all rows rather than no rows. - Test your template against each target database type if you use `$DB` branching — the comparison is case-sensitive. - You can use parameters and filter variables freely within arbitrary SQL positions (including sub-queries and HAVING clauses) — the engine simply replaces the token with the resolved value. ### Quick Reference `-- TEMPLATING` Required first line to activate the engine `$VarName` Injects the resolved value of the named variable `($VarName)` Injects an IN LIST of values — include the parentheses in the SQL `#if(condition) Begins a conditional block` `#elseif(condition)` Alternative branch condition (single level only) `#else` Default branch when no condition matched `#end` Closes an #if block `$DB` Built-in variable — current database type (e.g. SQL_SERVER) `$Var != "NULL"` Null-check pattern — tests whether a variable has been set ### Examples Example 1 ``` -- TEMPLATING SELECT AGEGROUPATCAMP FROM ATHLETEFACT WHERE AGEGROUPATCAMP IN ($FILTER1) ``` This example demonstrates **variable injection (replacement)**. - The `$` symbol indicates that a variable is being injected. - `$FILTER1` represents the variable name, which corresponds to a **parameter** or **filter name** defined in Yellowfin (as mentioned in the previous section). - For **IN LIST** operations, the variable must be wrapped in parentheses `( )`. During the templating process, `$FILTER1` will be expanded into a list of values. For example: ('0 - 14', '15 - 19', ...) **Example 2** ``` -- TEMPLATING SELECT DEMOGRAPHIC FROM ATHLETEFACT #if($DB=='HSQLDB') WHERE DEMOGRAPHIC = $Filter1 #else WHERE DEMOGRAPHIC = $Filter2 #end ``` This example demonstrates the use of **control flow** in SQL templating, specifically using conditional logic. - The `#if`, `#else`, and `#end` directives are part of Apache Velocity and allow dynamic branching within the SQL. - `$DB` is a predefined variable in Yellowfin that represents the **database type** of the source. In this case: - If the database type is **HSQLDB**, the query will resolve to: `WHERE DEMOGRAPHIC = $Filter1` - Otherwise, it will resolve to: `WHERE DEMOGRAPHIC = $Filter2` This example also illustrates how the **EQUALS** operation should be used: - Similar to the **IN LIST** example earlier, the variable (e.g. `$Filter1`) represents a parameter or filter. - Users must explicitly include the `=` operator to indicate that an **EQUALS** comparison is being performed.