I have created a list which almost replicates a pivot table in Excel. I need it to look like the following but can't get it to look this way.
Large or Small Country Profit
Large Russia 1000
Large USA 1100
Large Total 2100
Small 2000
Smal Total 2000
Total 4100
The problem is I don't know how to group all the small countries together so the individual countries are not shown in the country column. Right now, they come out as a full list, just as the large countries do. In an Excel Pivot it would just be a case of using the plus and minus sign to group and ungroup but I don't know what the Cognos solution is.
Any ideas?
It is simply a matter of defining a case against the [country] in a new dataitem:
CASE [COUNTRY]
WHEN 'Russia' then 'Russia'
when 'USA' then 'USA'
else 'Small Country Group'
end
Use the new dataitem instead of [COUNTRY]