Case When function with also the and or conditions in Cognos Analytics 11.1.x

Cognos 11.1x List Report. I am asked to find when revenue account type level 5 exists, then set account values for revenue 5, and expenses 6,7,8 to zero.

It only sets revenue account, 5 to zero. How can I ask it to set expense account 6,7,8 to zero?

I created [SUMTEMPBUDG_SALES_REV_5 ] to calculate the total of revenue. When account 5 exists I calculate it else set it to 0.

Any thoughts about this idea for a case? How would you write the expression?

case
when( ([SUMTEMPBUDG_SALES_REV_5 ] <> (0)) AND ([ACCOUNT_TYPE_LEVEL_1] IN ('50'))  AND 
      ( [ACCOUNT_TYPE_LEVEL_1] IN ('50') OR 
        [ACCOUNT_TYPE_LEVEL_1] IN ('60') OR 
        [ACCOUNT_TYPE_LEVEL_1] IN ('70') OR 
        [ACCOUNT_TYPE_LEVEL_1] IN ('80')
       ) 
     ) THEN (0)
else(coalesce([SUM_TEMPORARY_BUDGET],0))
end

Solution

The answer solution was to use SQL LISTAGG to list the vertical data in the list report as horizontal data, without any delimiters. This answer was provided from many sources too many to list with the same problem as mine.

For a data result set in the report with filters applied for the fiscal year and fiscal period the business rule is that if 50 exists then set all row values with 50,60,70,80 to the value of 0 in a data item.

An example is that each row in the list report had a value 50,60,70,or 80. As long as the data is vertical with one value per row I could not apply the business rule. This effectively removes the values in that transaction per the customer business rule needs.

How did I do it? In the SQL LISTAGG I was able to place the data into a data item [SUM_TEMPORARY_BUDGET_2] with the values in a horizontal string 50607080. And then Join the SQL into the report. Next, I wrote a case statement business rule on the data item: [SUM_TEMPORARY_BUDGET_2] case when( ([ACCOUNT_TYPE_LEVEL_1_LISTAGG]) CONTAINS '50') THEN (0) else(coalesce([SUM_TEMPORARY_BUDGET],0)) end

If you have any questions send me a message. Kindly Cheers!