Cognos report Studio sum one row by year

Question

I am working on a Cognos Report Studio Version 10

                       Year 1        Year 2        Year 3
        Item 1         100           200           300
        Item 2         600           600           600

Item 2 (each year) = Item 1(Year 1 + Year 2 + Year 3)

How can I do this report in Cognos Report Studio? Thanks!

Johnsonium Johnsonium · Accepted Answer · 2015-01-26T21:34:26

Create a data item to calculate the sum of years where item equals 1.

Data Item name: [Item 1 Total]

maximum(
CASE 
WHEN [Item] = 1 THEN [Year 1] + [Year 2] + [Year 3] 
ELSE 0 
END 
for report)

Set the data item's 'Aggregate Function' property to 'Calculated' to tell Cognos that you have supplied your own summary expression.

Now you need to modify the expression for the [Year 1],[Year 2], and [Year 3] data items to use the newly calculated sum.

CASE 
WHEN [Item] = 2 THEN [Item 1 Total] 
ELSE [Year 1] 
END

Use the same expression for Year 2 and Year 3 and just change the data item referenced in the ELSE clause.

Cognos report Studio sum one row by year

1 Answers