PI90467: CONDITIONAL DATE PROMPT DOES NOT FILTER FILTER RESULTS

APAR status

• Closed as suggestion for future release.

Error description

• No error
  

Local fix

Problem summary

Problem conclusion

Temporary fix

Comments

• With the given report spec, the product is behaving as designed.
  When a parameter in an optional filter is given a NIL value
  (i.e. the user is explicitly not selecting a value for this
  parameter), the optional filter gets dropped. That explains why
  we see unfiltered data with the given report spec.
  
  For more details on NIL parameter values and the dropping of
  optional filters, see this technote here:
  https://www-01.ibm.com/support/docview.wss?uid=swg21970127
  
  For this particular report, there are 2 possible filtering
  scenarios: by date range or by month.
  Therefore, when the user selects to filter by "date range", the
  parameters for the "by month" filter expression (YearP and
  MonthP) will become NIL, resulting in the entire filter being
  dropped. And vice versa when the user selects to filter by
  month.
  
  Given that this report has 2 possible filtering scenarios(i.e.
  by date range or by month), it is best not to put all these
  conditions under one single optional detail filter expression.
  
  For this scenario, there are at least 2 ways to get the desired
  result.
  
  A) Split up the detail filter expression into 2 optional detail
  filter expressions, i.e. one for each filtering scenario.
  
  Optional filter 1 (by date range):
  
      (?DP?='DT' and [Date] in_range ?DateP?)
  
  Optional filter 2 (by month):
  
      (?DP?='MON' and [Year]=?YearP?  and [Month]<= ?MonthP?)
  
  That way, when the user selects to filter by date range, the
  other filter will get dropped due to NIL values for ?YearP? and
  ?MonthP? (and vice versa)
  
  B) Use a CASE WHEN expression within a single detail filter.
  This filter can be required or optional (it will work either
  way).
  
  The product will collapse a CASE WHEN (or IF THEN ELSE)
  statement during query planning, when possible.
  
  For example, with a CASE WHEN expression like this:
  CASE ?p?
  WHEN 'a' THEN [a] = 1
  WHEN 'b' THEN [b] = 2
  ELSE 0=0 END
  
  if the value of parameter ?p? is given, the product will
  collapse/simplify the expression accordingly before sending it
  down to the database. For example, when ?p? is given the value
  'a', the expression become "[a] = 1", and so forth.
  
  Applying this to the current report, we can use the detail filer
  expression:
  
  CASE ?DP?
  WHEN 'DT' THEN ( [Date] in_range ?DateP?)
  WHEN 'MON' THEN ( [Year]=?YearP?  and [Month] <= ?MonthP?)
  ELSE ( 0=0 ) END
  

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

Applicable component levels