7. Advanced parameters, dynamic SQL, LOVs and parameter dependencies
How to use advanced Blitz Report parameter features, such as the different anchor types, dynamic SQL, list of values, parameter dependencies or dynamic parameter value defaults.
00:00 Introduction to different parameter anchors
02:22 1=1 anchor example
04:31 &lexical anchor example
10:05 How to use the 'Yes' list of values
11:01 Use of direct :binds in the SQL
14:21 Dynamic SQL with different SQL text for the same parameter
18:34 Matching values
21:47 Dynamic &lexical SQL text replacement based on an SQL query
25:08 Different list of value types
25:42 LOV Oracle
26:50 LOV custom and how to use the Id and Value columns
28:55 Reusable LOV
29:32 Filter Before Display LOV property
31:30 Used By button
32:22 List of values full text search
32:50 Validate From List LOV property for wildcards
35:28 Dependent parameter list of values
37:50 Dependencies on multiple parameter values using the xxen_util.contains function
39:45 Dependencies in parameter default values
41:24 Dynamic parameter default values based on functions or SQL queries
42:56 Hidden parameters with negative display sequence
44:08 Resequence parameters
𝐒𝐮𝐦𝐦𝐚𝐫𝐲:
The video covers advanced features and functionality of Blitz Report parameters, starting with different anchor styles. The three main types are 1=1 anchors (used for where-clause restrictions), &lexicals (for dynamic SQL text replacement), and direct :binds (for parameter values). The 1=1 anchor style is preferred for optional parameters as it maintains better query performance by avoiding NVL constructs that could prevent index usage. https://www.enginatics.com/blitz-repo...
Lexicals are particularly powerful for dynamic SQL implementation, allowing developers to show/hide columns or modify queries based on user selections. A single parameter can reference multiple lexicals in different positions of the query, enabling complex dynamic behaviors. For example, a "show contacts" parameter could inject both additional columns and join conditions through different lexicals. https://www.enginatics.com/blitz-repo...
The matching values feature provides flexibility in SQL text insertion based on user parameter selections. It functions similarly to a case statement, where different SQL text can be inserted depending on the parameter value chosen. This is particularly useful for reports that need to display different levels of detail, such as header, line, and distribution information. https://www.enginatics.com/blitz-repo...
List of Values (LOV) come in three types: https://www.enginatics.com/blitz-repo...
𝐎𝐫𝐚𝐜𝐥𝐞 𝐋𝐎𝐕: Uses standard Oracle value sets but has limited flexibility
𝐂𝐮𝐬𝐭𝐨𝐦 𝐋𝐎𝐕: Allows for report-specific query modifications
𝐁𝐥𝐢𝐭𝐳 𝐑𝐞𝐩𝐨𝐫𝐭 𝐋𝐎𝐕: Reusable across multiple reports
All LOVs require 'value' and 'description' columns, and can optionally include an ID column. Two important LOV settings are "Validate From List" (controls wildcard usage) and "Filter Before Display" (manages performance for large datasets). https://www.enginatics.com/blitz-repo...
Parameter dependencies enable dynamic relationships between parameters using the :$FLEX$ syntax. This allows parameters to reference other parameters or LOVs, supporting multiple value selections and complex parameter interactions. The system also includes features for default values, hidden parameters (using negative display sequences), and parameter resequencing for maintenance. https://www.enginatics.com/blitz-repo...
The video demonstrates these concepts using practical examples, showing how they can be combined to create sophisticated, dynamic reports that maintain good performance while providing flexible user options.