Help with grouping

[jcrouch]

Hi,

Im new to Cognos so please have patience :)

I am working with a sales table that shows Brand, Type and Cost. I brought those 3 into a query.

The Type field holds data like:

Steel
Brass
Copper
Polyvinyl chloride
polyethylene terephthalate
polystyrene
Crystalline
Non-crystalline

I need to group these into Metals, Plastics and Ceramics. End result in a query filtered by a single brand would be:

Brand     TypeGroup     Sales
Vivi         Metals            $12,300
Vivi         Plastics          $3,450
Vivi         Ceramics        $8,500

So id there a way to create an object in the query that could have the values Metals, Plastics, Ceramics; but Metals would be known to equal Steel, Brass and Copper?

I hope I am making sense, and I apologize in advance for not knowing the exact terminology for what I need.

Thanks,

Jeremy

[pimogo]

What you can do is create another column that uses a CASE statement. This works best if your Type field has a limited and unchanging number of items. If thats the case you can proceed as follows:

Code Select Expand


CASE Type
WHEN 'Steel' THEN 'Metal'
WHEN 'Brass' THEN 'Metal'
WHEN 'Polyvinyl chloride' THEN 'Plastics'
ELSE 'Other'
END


Once this field is created, you can Group By this.

Hope that helps.

[jcrouch]

Thanks pimogo,

This looks like it wil work perfectly! I will give this a try tomorrow at work.

I had come up with a workaround, creating a query for each group then union-ing them all back together. But that seemed really cumbersome and a pain to make changes to...