Conditional Parameter Insertion using Matching Values

Customer December 10, 2024 at 11:52 pm

I need to configure a single report parameter, ‘Process Type’, such that if the user selects ‘ORDER’, one specific WHERE clause snippet is used, but if they select ‘RETURN’, a completely different WHERE clause is inserted. How can I manage this conditional logic within the Blitz Report parameter setup?

Viewing 8 reply threads

Author
Replies
- Support December 12, 2024 at 7:50 pm
  You can achieve this conditional logic efficiently using the Matching Value feature within the parameter definition . You would create two separate entries for the ‘Process Type’ parameter name: one entry defined with a Matching Value of ‘ORDER’ and its corresponding SQL text, and a second entry with a Matching Value of ‘RETURN’ and its corresponding, distinct SQL text. Blitz Report’s runtime SQL generation logic ensures that only the SQL text associated with the matching value entered by the user is dynamically inserted into the main report SQL .
- Customer December 14, 2024 at 9:54 pm
  Does this require two entries with the same parameter name, and if so, how does the system know which one to display to the user on the run window?
- Support December 17, 2024 at 12:01 am
  Yes, this setup requires multiple entries for the same parameter name, each with a different matching value or anchor . To manage the display on the run window, only one of those entries should have a defined Display Sequence, Parameter Type, and List of Values (LOV) setup . The other entries, used purely for defining conditional SQL injection via matching values, should have their display sequence left blank .
- Customer December 18, 2024 at 11:21 pm
  Can I use wildcards in the Matching Value definition? For instance, if the process type name starts with ‘Quick%’, I want a generic WHERE clause applied.
- Support December 19, 2024 at 11:35 pm
  Yes, the Matching Value field supports the use of wildcard characters . If you use wildcards and the user’s entered parameter value matches multiple defined matching values for the same anchor, Blitz Report applies a rule of precedence. It inserts the SQL text associated with the best match, which is defined as the matching value entry with the longest string length .
- Customer December 21, 2024 at 10:55 am
  We have an existing seeded report, ‘FND Concurrent Requests’, that seems to use this matching value approach for the Phase and Status parameters. Is that a good example to follow?
- Support December 22, 2024 at 7:43 pm
  The seeded report ‘FND Concurrent Requests’ serves as an excellent reference, as it explicitly uses the Matching Value functionality to insert different WHERE clauses for parameters like ‘Phase’ and ‘Status’ . It dynamically selects the most appropriate SQL text to achieve better performance based on the specific parameter value provided by the user at runtime .
- Customer December 23, 2024 at 9:34 am
  If I only provide the matching value for one entry, but the user doesn’t enter anything for the parameter, will the query run without the conditional clause?
- Support December 24, 2024 at 3:55 pm
  If the user does not enter a value for the parameter, none of the associated conditional SQL texts with matching values will be inserted into the extraction SQL . The primary mechanism for dynamic SQL, whether using the `n=n` or `&lexical` anchor, ensures that if a parameter value is left blank, the corresponding reference (or clause) is removed before SQL execution . This maintains query efficiency by preventing the execution of unnecessary conditional logic.
Author
Replies

Viewing 8 reply threads

You must be logged in to reply to this post.