4

I'm familiar with Excel and SQL, but new to Cognos. I'm doing a conditional sum on [Total Margin] for each [Item Code]. This result should show on each row for the each item. I've tried 2 approaches in Cognos and a proof of concept in Excel. See below for sample data from a single [Item Code].

data screenshot

Total Item Margin A (Cognos)

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

The problem here is that the TOTAL result is incorrect, and just fails to display on the 2nd row.

Total Item Margin B (Cognos)

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

Here the TOTAL result is correct on most rows, but different on the 2nd row.

Total Item Margin C (Excel)

=SUMIFS([Total Margin],[Item Code],'10001430',[free of charge flag],FALSE)

So I can get the result I want using an excel SUMIFS formula. What Cognos query do I need to write to get the same result directly from Cognos?

Toto
  • 89,455
  • 62
  • 89
  • 125
Fractional
  • 153
  • 1
  • 2
  • 10

1 Answers1

6

try 

total(
  case
  when [free of charge flag] = 'FALSE'
  then [Total Margin]
  else null
  end
for [Item Code])
Alexey Baturin
  • 1,173
  • 1
  • 7
  • 11
  • That worked, thank you! Working out the query syntax of a new system can be frustrating sometimes. Seems a nested CASE WHEN was required here. – Fractional Nov 06 '14 at 13:47
  • The tool tips in Cognos 10 are next to useless sometimes when you can never see examples of how {expression} can really be utilized. This was a great example. – Rick Henderson Mar 02 '17 at 16:16