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-report-developer-guide/#Anchors_and_binds 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-report-developer-guide/#lexical 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-report-developer-guide/#Matching_Value List of Values (LOV) come in three types: https://www.enginatics.com/blitz-report-developer-guide/#Parameter_Type - 𝐎𝐫𝐚𝐜𝐥𝐞 𝐋𝐎𝐕: 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-report-developer-guide/#LOVs 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-report-developer-guide/#Dependent_Parameters 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.