How do I create a conditional filter in report?

Sometimes it is desirable to have a conditional filter, where the data is filtered one way when one condition exists, and another way when another condition exists. Using an IF/THEN or CASE/WHEN statement as a detail filter is not supported. This document describes how to accomplish the same thing using supported Boolean expressions.

Steps

Sometimes a report query needs a detail filter that uses one filter condition in one case, and a different filter condition in another case. Report developers may be temped to use a filter expression that relies on an IF/THEN or CASE/WHEN statement to flip between different filter conditions, however this should not be done.

Documentation states that "Detail filters are Boolean expressions that apply one or more conditions and evaluate to either true or false." Filter expressions that are if/then or case/when statements are not Boolean expressions that evaluate to true or false, they are conditional statements that evaluate to a Boolean expression. This is not supported. Sometimes they work, sometimes they cause a report to fail with an error, and sometimes they cause other issues. Since this type of detail filter expression is not supported, IBM does not test the results of using them and development would not see and problems associated with using them as a defect.

It is possible however to write such a filter as a Boolean expression. For example, this IF/THEN expression:

(?p_CurrentYearOrPrevYear?='CY' AND [Year]=[CurrentYear]) OR (?p_CurrentYearOrPrevYear?<>'CY' AND [Year]=[PrevYear])