Dynamically Modifying GROUP BY Clauses

For modifying structural SQL elements like the `GROUP BY` clause, the `&lexical` anchor is the appropriate tool, as it allows insertion of arbitrary SQL snippets . You should place the anchor, perhaps named `&group_by_vendor`, within your `GROUP BY` clause immediately preceding the mandatory columns, like `group by &group_by_vendor pha.currency_code` . The parameter associated with this anchor would have a SQL text containing the optional column list, such as `pv.vendor_id,` .

Yes, if the user leaves the parameter value blank, the core functionality of the `&lexical` anchor dictates that the corresponding reference is completely removed before the SQL is executed . Therefore, the runtime SQL would simply revert to `group by pha.currency_code`, which is perfectly valid SQL and ensures the intended aggregation occurs without the vendor dimension . This prevents syntax errors and optimizes performance.

Yes, you can use the same anchor multiple times within a single report SQL definition . For every occurrence of the anchor (e.g., `&group_by_vendor` in both `SELECT` and `GROUP BY`), Blitz Report will insert the corresponding parameter SQL text during runtime . This allows you to maintain consistency across different clauses based on a single parameter input.

To force the parameter value to be inserted as literal text (lexical text) instead of a bind variable, you must use the special placeholder “ within the parameter’s SQL text field . This method ensures that the final SQL executed includes the literal value, which can be useful when you purposefully want to enforce reparsing for different parameter values, as illustrated in the Discoverer EUL access example .