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
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:
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.
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...