Getting ready

Create a simple list report with Order Method | Order Method and Sales Fact | Sales Quantity as columns. Group by order method to get one row per method and set the aggregation for quantity to TOTAL.

How to do it...

How it works...

The IF ELSE construct works fine when it is used in data expression. However, when we use it in a filter, Cognos often doesn't like it. It is strange because the filter is parsed and validated fine in the expression window and IF ELSE is a valid construct.

The workaround for this problem is to use the pair of AND..OR as shown in this recipe. The IF condition and corresponding action item are joined with AND clause. The ELSE part is taken care by OR operations with the reverse condition (in our example, Order Method <> 'Sales Visit').

There's more...

You need not use both AND and OR clauses all the time. The filtering in this example can also be achieved by this expression:

([Sales (query)].[Retailer site].[Retailer name] = ?SalesVisitRetailer?)

or

([Order method]<>'Sales visit')

Depending on the requirement, you need to use only OR, only AND, or the combination of AND..OR.

Make sure that you cover all the possibilities.