Implementing if-then-else in filters
Business owners want to see the sales quantity by order methods. However, for the Sales Visit type of order method, they want a facility to select the retailer.
Therefore, the report should show quantity by order methods. For the order methods other than Sales Visit, the report should consider all the retailers. For Sales Visit orders, it should filter on the selected retailer.
Getting ready
Create a simple list report with Order method / Order method type and Sales fact / 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...
In this recipe, we need to create a filter that will be used to select the retailer if the Order method is Sales Visit. We will check what will happen if we use the if then else construction inside the filter and how to overcome any problems with the following steps:
- Here we need to apply the retailer filter only if Order method is Sales Visit. So, we start by adding a new detail filter.
- Define the filter as follows:
if ([Order method type]='Sales visit') then ([Sales (query)].[Retailers].[Retailer name] = ?SalesVisitRetailer?).
- Validate the report. You will find multiple error messages.
- Now change the filter definition to:
(([Order method type]='Sales visit') and ([Sales (query)].[Retailers].[Retailer name] = ?SalesVisitRetailer?)) or ([Order method type]<>'Sales visit').
- Validate the report and it should be successful.
- Run the report and test the data.
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 and...or clauses as shown in this recipe. The if condition and corresponding action item are joined with the and clause. The else part is taken care of by the 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)].[Retailers].[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.