Cognos equivalent of Excel Sumif() function without a static variable

1
votes

Here is the following example that I found that does work as a sumif function.

total(
case
when [free of charge flag] = 'FALSE'
then [Total Margin]
else null
end
for [Item Code])

My question is how to replace the 'FALSE' portion and have the expression reflect the [free of charge flag] for that specific row. So as the report builds the total in each row may switch from a total of that item code and the free of charge flag being false to true depending on what is in the specific row.

This fails to run and I believe this may not be possible.

2
cognossumifscognos-10cognos-8

2 Answers

0
votes

You have [Item Code] as the column you are rolling up to but you can roll up to multiple columns.

Try this:

total([Total Margin] for [Item Code],[free of charge flag])

This will total the measure across all combinations of [Item Code] and [free of charge flag]. For a given [Item code] 'TRUE' and 'FALSE' rows will have their own values.

Further, if you don't want to show a total when the value is 'TRUE' then you can wrap this in a CASE statement:

CASE [free of charge flag]
WHEN 'FALSE' THEN total([Total Margin] for [Item Code],[free of charge flag]) 
ELSE NULL
END

All rows with 'FALSE' will show the total for that particular row's [Item Code] and all others will have NULL.

0
votes

I think you can just derive it as a calculated column for each row:

(
case
when [free of charge flag] = 'FALSE'
then [Total Margin]
else null
end
)

And then just total that.