Dependent LOVs based on Multi-Select Parameters

Customer July 20, 2020 at 2:42 pm

I have an AP Invoice report where the ‘Supplier Name’ LOV must be filtered based on the ‘Operating Unit’ selected by the user. If the user selects *multiple* Operating Units, the Supplier LOV should show suppliers from all selected units. How do I configure this parameter dependency to handle multi-selection?

Viewing 8 reply threads

Author
Replies
- Support July 21, 2020 at 10:58 pm
  When dealing with parameter dependencies where the parent parameter supports multiple selections, you must use the `xxen_util.contains` function within the dependent LOV query . The standard dependency syntax, `:$flex$.parameter_or_lov_name`, is used to reference the parent parameter (e.g., `:$flex$.operating_unit`) . The `xxen_util.contains` function ensures that the dependent LOV query correctly filters results by checking if each potential supplier’s operating unit ID is present within the comma or semicolon-separated list of multiple values passed by the parent parameter .
- Customer July 23, 2020 at 6:02 pm
  If I use a standard comparison operator in the parameter SQL text, like `vendor_id = :bind_variable`, and the user checks the ‘Multiple Values’ box, will the system automatically convert it to an `IN` clause?
- Support July 24, 2020 at 2:33 am
  Yes, Blitz Report offers automated Multiple Values functionality . If the parameter SQL text uses a bind variable restriction with an ‘equal’, ‘not equal’, or ‘like’ operator (e.g., `column_name =:bind_variable` or `upper(column_name) like upper(:bind_variable)`), and the user checks the ‘Multiple Values’ checkbox, the restriction is automatically replaced with an `IN-clause` during SQL execution , . This automation is only available for parameters using `1=1` or `&lexical` anchors, not simple `:bind` anchors .
- Customer July 25, 2020 at 9:46 pm
  If my SQL text is complex, perhaps using a greater-than operator (`column >= :bind_variable`), will the automated IN-clause replacement still work?
- Support July 27, 2020 at 11:01 am
  No, the automated IN-clause replacement will not work for complex SQL text or if a different comparison operator is used, such as `>=:bind_variable` . For the multi-value functionality to be available, the parameter SQL text must strictly adhere to the requirement of having the table column on the left side and the `:bind` variable on the right side of the comparison operator .
- Customer July 28, 2020 at 10:26 am
  When creating the LOV query for the Supplier Name, what mandatory columns must the LOV SQL select?
- Support July 29, 2020 at 5:14 am
  All Blitz Report List of Values queries must select two mandatory columns: `value` and `description` . Optionally, the LOV SQL may also include an `id` column at the beginning of the `SELECT` clause . If an `id` column is selected, that `id` value is used to bind the parameter restriction for the main report execution; otherwise, the `value` column is used as the bind value .
- Customer July 30, 2020 at 12:53 am
  I plan to save this custom LOV for reuse in other reports. How can I ensure this LOV is centrally stored and accessible?
- Support July 30, 2020 at 5:45 pm
  If you initially define the parameter using the ‘LOV Custom’ type, you can save it as a shared LOV for reuse across multiple reports . After setting up the query, click the ‘Save as shared LOV’ button in the LOV definition window . If you reference an existing shared LOV (type ‘LOV’) and later modify its query, those changes will immediately affect *all* other parameters referencing that shared LOV .
Author
Replies

Viewing 8 reply threads

You must be logged in to reply to this post.