Home › Discussions › Questions & Answers › Multi-Value Parameter Functionality and Limitations

Multi-Value Parameter Functionality and Limitations

Avatar photoCustomer Questions & Answers 7 replies

When creating a report parameter for filtering by multiple Inventory Item numbers, I used the SQL text `msiv.segment1 != :item_number`. If the user selects the ‘Multiple Values’ checkbox and provides several item numbers, will the system correctly generate a `NOT IN` clause?

7 replies

  • Support Enginatics

    Yes, Blitz Report automatically supports the conversion to an `IN-clause` (or `NOT IN-clause`) when the user selects the ‘Multiple Values’ checkbox . This automated replacement works for restrictions using the ‘not equal’ operator (`!=` or “), as well as ‘equal’ and ‘like’ operators . During SQL execution, the restriction is replaced with the equivalent `IN-clause` that includes all selected values .

  • Avatar photoCustomer

    What is the required format for the parameter SQL text to ensure the automated IN-clause replacement is enabled?

  • Support Enginatics

    A critical requirement for the automatic IN-clause replacement to function is that the parameter SQL text must have the table column name on the left side of the comparison operator and the `:bind` variable on the right side . For example, `column_name = :bind_variable` works, but a structure like `:bind_variable = column_name` will not enable the multiple values functionality .

  • Avatar photoCustomer

    If I need to filter a date range, using `>= :start_date`, and the user needs to select multiple date ranges, will the multi-value feature support this comparison operator?

  • Support Enginatics

    No, if you use complex operators such as `>= :bind_variable`, the automated IN-clause replacement is not possible, and consequently, the ‘Multiple Values’ checkbox will not be available for that parameter . This feature is restricted to equality and simple likeness comparisons for clean conversion to an `IN` clause .

  • Avatar photoCustomer

    Does the multi-value feature work if I use a simple `:bind` anchor instead of `n=n` or `&lexical` anchors?

  • Support Enginatics

    The multiple values functionality is only available for parameters defined with the anchor styles `1=1` (or `n=n`) or `&lexical` . It is explicitly not supported for parameters defined using the simple `:bind` anchor style .

  • You must be logged in to reply to this post.